PowerShell: searching in the first 2 levels of the directory

Let's say you need to find a certain folder in the Directory, but you don't want to go deeper than 2 or 3 levels in your search?

Obviously, we will have to use get-childitem

However, -recurse option cannot help, since it has no stopping limit - it will recurse to the bottom of the directory

Here is a solution that I found that's short and simple, just the way I like it:



$search1 = "C:\temp\*"
$search2 = "C:\temp\*\*"
$search3 = "C:\temp\*\*\*"

$folders =  Get-ChildItem  -path $search1, $search2, $search3 | where-object {$_.PSIscontainer -and $_.name.StartsWith("Blue")}

...


Continue SSIS Package execution after failure and ignoring the error

Finally found what I needed to avoid SSIS from failing the package and ignoring the error in the Task:

http://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/

Just one tiny hidden System variable "Propagate" was able to resolve my issue on custom error handling in the SSIS Package.

Indexed Views | Creating and Optimizing Views in SQL Server | InformIT

Indexed Views | Creating and Optimizing Views in SQL Server | InformIT

Looks like not is all that simple with indexed views.
If we are using Standard Edition, then indexed views will be used only when we specifically use SELECT with NOEXPAND hint

Enterprise edition's query optimizer decides himself whether to use data in the indexed view, or in its underlying tables, depending on what is faster for him.

In the Standard edition, however, it doesn't bother and goes to underlying tables right away, unless you add this NOEXPAND hint.

At least this is my understanding of the articles below.

http://technet.microsoft.com/en-us/library/ms181151%28v=sql.105%29.aspx

Improving Performance with SQL Server 2005 Indexed Views

Converting CSV files into XML files using PowerShell

At work I have to  resort sometimes to PowerShell to provide a quick solution to a problem.

For example, I had to do some CSV conversions and manipulations, which seemed to be easier done in PowerShell rather than in SQL Server.

1. I will document here a CSV to XML file conversion, which looked to me short and sweet:

Idea inspired from http://blogs.msdn.com/b/powershell/archive/2007/05/29/using-powershell-to-generate-xml-documents.aspx

#create xml file - export_xml 
$file = "C:\tmp\file.csv"
$fileoutxml = "C:\tmp\file.xml"
$xml=Import-Csv $file -Delimiter ";" -Header "c1","c2","c3" |
 foreach { " <row c1=`"{0}`" c2=`"{1}`" c3=`"{2}`"/>`n" -f
 $_.c1,$_.c2,$_.c3 } 
$xml="<export key_field=`"c1`">`n " + $xml + "</export>" | Out-File $fileoutxml -Encoding UTF8 

2. and a replace with Regular Expressions:
here, I am first replacing ; by | and trimming up the data at the same time
and then changing the swapping the order of the two last columns. it is important that $2$1 is surrounded by single quotes - script stops working if we use double quotes for some reason.

(Get-Content $file ) |
Foreach-Object {$_ -replace "(\s*;\s*)", -replace "(?<=^[^|]*\|[^|]*)(\|[^|]*)(\|[^|]*$)", '$2$1'; } |
Set-Content $fileout -Encoding Unicode

C# supporting different versions of SQL Server with same application

I had to write a piece of code that would work for both SQL Server 2008 R2 and SQL Server 2014

There were several issues - first of them is how to include two versions of the same assembly (Microsoft.SQLServer.ManagedDTS v.10 and v.12) in the same project.

This was solved with extern alias after following instructions in this article: http://kentb.blogspot.ca/2008/11/visual-studio-referencing-same-assembly.html

Then there was a problem of ILMerge.exe which stopped working with extern alias complaining that "Unresolved assembly reference not allowed". I had to ditch ILMerge and use an alternative method described here - http://blogs.msdn.com/b/microsoft_press/archive/2010/02/03/jeffrey-richter-excerpt-2-from-clr-via-c-third-edition.aspx

Then I had to use reflection to maintain same code for both versions. All compiled and worked well until I tried the application on a server which really had a single version of SQL Server - 2008 R2.

I started getting the very obscure "Object reference not set to instance of an object" exception just before entering the function that used reflection.

 As it turns out you have to separate the code that uses a possibly missing library in a separate function and then all will work as it is supposed to.

Here is the code:


using dtr120 = DTS120::Microsoft.SqlServer.Dts.Runtime; 
using dtr100 = DTS100::Microsoft.SqlServer.Dts.Runtime; 

//DTS120 and DTS100 are extern aliases of two different versions of the library 

//generic method
LoadPackage<T>(string sDTSPatch)
{....} 

this doesn't work:

if (is120) //if 2014
   bFlag = LoadPackage<dtr120.Application>(sDTSPatch);
else if (is100)  //if 2008 R2
   bFlag = LoadPackage<dtr100.Application>(sDTSPatch);

but this works:

protected bool Load120(string sDTSPatch)
{
  return LoadPackage<dtr120.Application>(sDTSPatch);
}

protected bool Load100(string sDTSPatch)
{
  return LoadPackage<dtr100.Application>(sDTSPatch);
}

....

if (is120) //if 2014  
    bFlag = Load120(sDTSPatch);
else if (is100) //if 2008 R2
    bFlag = Load100(sDTSPatch);


Update:
I forgot to mention that you would have to automate refreshing of .dll files into the root project directory. EmbeddedResourse dll files work only if they are in the root of the project directory

I did it by writing a Pre-build event

if $(ConfigurationName) == Release (
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectX\bin\Release\ProjectX.dll $(ProjectDir)ProjectX.dll
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectY\bin\Release\ProjectY.dll $(ProjectDir)ProjectY.dll)

if $(ConfigurationName) == Debug (
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectX\bin\Debug\ProjectX.dll $(ProjectDir)ProjectX.dll
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectY\bin\Debug\ProjectY.dll $(ProjectDir)ProjectY.dll)

Ever wanted to use pretty Calendar Controls of Report Builder 2.0 in reports based on Analysis Services?

How to use SSRS Calendar Controls for Time dimensions in the reports based on SSAS (Analysis Services) in Report Builder 2.0:



1. Start creating the SSRS report in the Report Builder 2.0 with a Wizard.

2. Select a Time dimension and drop it in the filter area, select hierarchy, use Range (Inclusive) and check both parameters checkboxes, add measures and other dimensions to the report. Close the Query Designer.




3. Go to the parameters of the report (they are automatically created) and modify "From" and "To" Time parameters - change their Data type to Date/Time and set Available Values to None





4. Open the DataSet Properties and press fx button to the right of Query





5. Remove "CONSTRAINED" from "STRTOMEMBER" function that is using Date parameters in the MDX query



6. Go to parameters tab of the DataSet Properties window and enter the expression as a parameter value. Expression has to generate a Uniquename of the Time dimension

In my example unique name is [Date].[Date].[Day].&[20111216]

To generate this string you will need following expression:
="[Date].[Date].[Day].&[" + Format(Convert.ToDateTime(Parameters!FromDate.Value), "yyyyMMdd") + "]"



Repeat this for the second parameter.

GO statement breaks SSIS "Execute SQL Task"

I have lost an hour today trying to find a bug in my SSIS package, and it was so small - just two letters - "GO"

I had an Execute SQL Task with an input parameter and I had an SQLStatement with some lengthy logc and a "GO" at the end.

Every time I tried to execute it I got an error message "[Execute SQL Task] Error: Executing the query "
" failed with the following error: "The variable name '@StartFrom' has already been declared. Variable names must be unique within a query batch or stored procedure."

I got desperate trying to find where am I declaring the @StartFrom. I even removed it completely from script and still got this error. Eventually, gloomily poring over my script I saw a "GO" statement and decided that maybe he is the culprit - causing Execute SQL Task to declare the Input Parameter twice, and I was right. That is exactly what happens.

When Execute SQL Task sees GO, it redeclares the variable specified in the Parameter Mapping, and then himself not liking it complains about it being declared twice. Isn't that just ingenious?