What email address or phone number would you like to use to sign in to Docs.com?
If you already have an account that you use with Office or other Microsoft services, enter it here.
Or sign in with:
Signing in allows you to download and like content, and it provides the authors analytical data about your interactions with their content.
Embed code for: SSIS Custom Control Flow Components with Visual Studio Community (#SQLSatDenmark 2015, #SQLSat413)
Select a size
SSIS Custom Control Flow Components with Visual Studio Community
Say thank you to our Sponsors :
Stick around for RAFFLE and the AFTER EVENT!
All our volunteers and organisers do not get paid for organizing this event – If you see them, please:
Give them a hug
Shake their hand
Say thank you
Spread the word
Get involved yourself
Don’t forget to thank the sponsors for their support
Thank the speakers for donating their time, energy and expenses
About me – Wolfgang Strasser
Software, Business Intelligence and DWH
Like to load, transform, modify and query data
SSIS & T-SQL
SSIS Users since SQL 2005
SSIS Custom Component developer > 6 years
Who knows or used/uses..
… SSIS Script Task
… SSIS Custom Components
Re-Invent the Wheel !?
Foreach Loop Containers
Control Flow Tasks
SSDT–BI (to develop SSIS Packages)
If you like to test the package deployed (SSIS Catalog)
C# knowledge (preferred)
Or any other .NET language
Admin Rights on Development machine
Be Professional - And Part of Community!
Create a new Custom Component
New Project (Class Library)
MainClass – Inherit from Task Class
Deployment – on DEV Machine
Admin rights needed
Copy to DTS Folder
%PROGRAMFILES%\Microsoft SQL Server\120\DTS\Tasks
Post Build events
Copy & gacutil
Deployment – on PROD machine
GAC – needed
Only needed, if you develop on Server machine
SSIS Properties (Built-in Validation)
Properties & Validation
separate assemblies for Task and UI
Do not need UI @ server
UI-assembly in GAC only
, Description="My First SSIS Task"
, TaskContact="wstrasser, 2015"
, UITypeName="SampleTask.SampleTaskUI, SampleTaskUI
, Version=18.104.22.168, Culture=Neutral , PublicKeyToken=82d3a052b01d4702"
DEMO - Recap
Variables - Designtime
Variables - Access
LockForRead(..) / LockForWrite(..)
Fire (Log) Events
Design Time Debugging
UI, configuration, persistance
Start SSIS Package
Attach To Process
Be Professional - Icon
Control Flow Icon
Be Professional - Assembly Version
If you change AssemblyVersion(“…“)
SSIS Package contains hardcoded reference
AssemblyVersion versus AssemblyFileVersion
Be Professional – Automated Builds
Build for multiple SQL Server Versions
Persist & Load Settings
Recycle Application logic
Reduce Copy&Paste errors
Reduce multiple programming efforts
.. written in one place
.. used in multiple places
Be professional - Be faster!
Cool and fast solution – what do you think? I am personally not quite happy with this approach.
Copy & Paste has some disadvantages.
First of all – almost in every situation, you have to minimally change the copied script in the script task. And hopefully, you change the code right, but – unfortunately - this is not always the case.
Second, just think about a larger SSIS project. Within this, you do not only have one package, you have multiple ones. And you want to use your logging script in all of these packages. Just copy and afterwards paste them.
One week later – your chief DWH architect comes to your office and tells you, that you have to change the syntax [output] of the logging statement. Just a small change in every script.. But painful if you have to do it manually…
And – just think about a second, third or fourth DWH project – there you want to also use the logging statements. Because of time-pressure, you recall the solution you implemented several months ago, but you do NOT copy the script – you …..
REINVENT the wheel…
Is this the right approach?Is this a good approach following Software-Engineering Best Practices?
I don‘t think so…
Let‘s think more about it..
What about trying a different approach?
Recyclce application logic (in our example the logging action)
Reduce errors by Reusing
So – let’s start the development.. What do you need in order to develop your first (and all the following) custom component??
Well – enough said for the moment – let’s start with our development.
I will switch to Visual Studio now..
Just a small switch into Visual Studio
Let‘s have a look how we can build a super, duper sexy User-Interface
Variables – one of the central points of our demo setting we want to use..
SSIS variables can be used (read, written and even created) in custom components.
The SSIS API provides structured ways of exposing the SSIS packages‘ variables to the C# sourcecode of the custom component.
For example, you could list all the variables currently available in the package
Or – you want to provide functionality to the user to create new SSIS variables on-the-fly. Just call the appropriate method (PromptAndCreateVariable) and if everything workes fine – a new SSIS variable was created.
When it comes to variable access (reading and writing values) there are some points you have to recall and follow:
First of all: think about concurrency: Multiple Tasks in an SSIS package can run in parallel at the same time. Therefore the access to variables have to be somehow under control
The class VariableDispenser is the guy who gives you access to a specific set of SSIS variables.
First thing you have to do – just tell the VariableDispenser the variables you want to lock. You can either lock variables for just reading and/or writing.
Afterwards you have to call the method GetVariables …this returns the requested variables. And blocks if one or more of them are currently locked by other tasks.
Afterwards, just use the variables and read or write their values.
Very important: Call the Unlock method if your are finished with working with the currently requested variables. Please be so patient and call this method as soon as possible in order to improve concurrency.
Now let‘s combine the usage of SSIS variables and firing logging events for finishing our demo requirements.
Logging the value of a variable.
And now – Action … Until now we focused on the configuration/development aspect of our custom component.
But the custom component is more – it think the execution of the component is the more interesting thing of all this. And exactly this point is the next topic..
.. The Execute method..
Override this method and put your actions in this method. The parameters of this method provide access to the outside of the SSIS package.
The connections parameter gives you access to the packages connection managers
The variableDispenser – you should already know this class – provides access to SSIS variables
The componentEvents parameters allows you to fire events
And the log parameters gives your component access the event log
And last but not least – the transaction object. If it is not null, the surrounding container is contained in a transaction.
Another thing that you maybe know from other SSIS components are entries in the log history.
With a custom component you can also achieve the same results and fire those log events from your component.
In the current slide I provided a sample of two different logging events.
FireProgress in order to provide some sort of progress information to the SSIS environment
And as an example FireInformation to output some logging information
There are other logging levels you can output – FireWarning and FireError (if you want to output some error messages).
Debugging – I think everybody of you already had to debug some sort of source code / Software artifact you had develop.
For SSIS custom components, there are two different ways of debbuging.
First, Design-Time Debugging – this is the process of debugging the User-Interface. With this approach you can debug your all your aspects of design-time logic:Persistance of component settings, Validation logic, User Interface code etc.
This is the process of debugging the execution logic of your component. Run-Time Debugging requires some preparations in order to work.
You need to set a breakpoint in the SSIS package (onPreExecute for the component).
start the SSIS package
Switch back to the Visual Studio and Debug – Attach to Process
Select the DtsDebugHost.exe and click Attach
Back to SSIS: run…
Do NOT to set breakpoint in C#
Sometimes during debugging, it can occur, that the DtsDebugHost.exe is not correctly exited. If you try to start debugging afterwards you get some weired error message.
Being professional means here, to first have a look at the task manager -> yes, multiple DtsDebugHost instances are running
And then calling Taskkill to kill these instances.
And magic – the debugging works again.
As you maybe know, SSIS packages internally are stored as XML files. And all the properties of standard as well as custom SSIS components are persisted in this XML structure.
The hightlighted rectangle on the slide just shows the property part of a sample custom component.
The mechanism of saving and loading properties can be easily achived by adding the Interface IDTSComponentPersist to your custom component. With this interface you have to implement two methods
What you have to do to save the properties is to
Create a new subnode and – in my example – I add an attribute who‘s value get‘s the value of our integer property
For loading the values – I just check, if the current context is my SampleTaskSettings context and then I read the value of the attribute.
The two methods are called from the SSIS designer/exection engine whenever a SSIS package is loaded OR saved. cks if one or more of them are currently locked by other tasks.
But the custom component is more – it think the execution of the component is the more interesting thing of all this. And exactly this p