Saturday, 22 June 2013

Yesterday I finally started using BIML in one of my projects. Very handy to create dozens of those simple but annoying packages in a fraction of the time you would normally need when creating them manually. No mistakes when creating your thirtieth staging package and all according to the naming conventions.

But two things bothered me and I think more people find them annoying:

Solution
You van overcome this by changing the XML formatting options in Visual Studio. Go to the Tools menu and choose options. Then go to the Text Editor options, XML and formatting. There you must change auto format on paste from clipboard. Now try again pasting some BIML code. See this for more details.
﻿

disable on paste from clipboard

2) Losing format & intellisense
When you mix the XML from BIML Script with C# code, the XML code obviously gets corrupt, because the brackets are not opening and closing one after the other:
<ExecutePackage Name="EPT - <#=row["name"]#>">

And then, when you re-open the BIML Script, you have lost formatting and even worse you have lost intellisense.
﻿
﻿

Formatting and intellisense gone!

Solution
You can overcome this by right clicking the BIML Script and select Open With... Then choose the XML (Text) Editor. Now it opens with formatting and intellisense. Setting it as the default editor didn't work.

Open in XML (Text) Editor

An alternative is using the online editor at http://www.bimlscript.com/Develop. This editor doesn't have the formatting issues and the <# and #> are formatted properly.

Friday, 14 June 2013

Case
I want to create my own custom SSIS task with a GUI. How do you do that?

Solution
For this example I will create a very basic task which you can extend for your own needs. It checks whether a file exists. The task is for SSIS 2008 and 2012 and I will use Visual Studio 2010 to create the it. Programming language is C#. Use this page to translate the code to VB.Net if you prefer that language.
﻿

My first SSIS task

1) Create Visual Studio project
For my task I used two C# Class Library projects. One for the GUI/editor and one for the code. For SSIS 2008 I will use .Net framework 3.5 and for SSIS 2012 I will use .Net framework 4.0

Two projects for my Task

2) Create key for strongname
You need to strongname your DLL's so that SSIS can use them. More about that in this Codeguru article: Giving a .NET Assembly a Strong Name. Open the Visual Studio 2010 Command Prompt (in Windows start menu). Browse to your project folder and execute the following command to create a key file: sn.exe -k myTask.snk
﻿

Microsoft (R) .NET Framework Strong Name Utility

3) Add key to project
The key file should be added to both projects.

Add key to projects

And after adding them, you need to sign the projects. Go to the properties of the projects and then to the Signing page. There you can sign the assembly with your newly generated key. Do this for both projects.

For SSIS 2008 they can be found in the program files folder. Something like:C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
And for SSIS 2012 they are located in the GAC. Something like:C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll

Add references

5) Build Events
To use the task dll's in SSIS you need to copy them to the GAC and to the task folder of SSIS. With the Build Events you can do that automatically when you build the visual studio project. Go to the properties of your projects and then to the Build Events. Add the following command to the Post-Build events.

6) Icons
I have added the same icon file to both projects. In the properties of the icon file you have to set Build Action to "Embedded Resource". In the UI project you can use the icon in your Windows Form. This will show when you edit the task.

Icon for editor

In the other project you can use this file to give your task a custom icon instead of the default. This will show in the SSIS Toolbox and in the Control Flow. Code is explained later on.

Icon for SSIS Toolbox and Control Flow

7) Gui project code
To keep everything clear and easy to explain I created a simple task. In the GUI you can choose a file connection manager or a string variable. The task will use the value in runtime to check if the filepath exists. See the code comments for the explanation.

8) Get PublicKeyToken
For the other project you need the PublicKeyToken of the GUI assembly. So first build the GUI project and then, via the same command prompt of step 2, execute the following command in the BIN folder of your GUI project: sn.exe -T SSISJoost.myTaskUI.dll

Copy the number generated. You need it in the next project.

9) The code for the actual work
This is the code from the project that does the actual work during runtime. See the comments for the explanation.

10) The Result
After building / deploying the solution, you need to close/reopen BIDS because the GAC is cached on startup. Now you can use your new task. For SSIS 2008 you need to right click the toolbox and click Choose Items. Then go to the Control Flow items and select your new task. For SSIS 2012 this is done automatically.