Bitesize SSIS: Passing SSIS variables into script components

I hate SSIS. It seems to me that it is full of certain nuances and unless you are regularly developing SSIS packages, they are easy to forget or it is easy to miss specific important steps. I first started using SSIS back in 2005 when it was directly introduced to replace DTS, but even today I am constantly going around in circles whenever I have to return to write certain functionality.Therefore I have decided to put together a “Bitesize” series of posts that encapsulate simple operations in order to help not just you, but more importantly remind me! Hopefully this will save me time in the long run…

Script Task components are incredibly useful if you (like me) have spent any time in a development background. My language of choice is C#, but Script Task components also support Visual Basic (for the purposes of my explanation I will assume C# is your preferred language too). Sooner or later you will require the ability to pass in dynamic values into the Script Task so that you may consume or manipulate them through code.

There are essentially three operations required in order to use SSIS variables in your scripts, they are:

  • Define your (SSIS) variable/s
  • Declare (SSIS) variable/s to Script Task as ReadOnlyVariables or ReadWriteVariables
  • Assign (SSIS) variables/s to Script variables (or visa-versa)

Define your SSIS variable/s

Your SSIS variables can be scoped as necessary and assuming that the Script Task has visibility, the scope is irrelevant for their consumption. Ensure that you create them using the correct data type so that you do not have to perform type checks in the script to allow simple casts to the correct required type.

To view SSIS variables, from the menu simply click SSIS/ Variables to display the Variables pane. It can be useful to click the Variable Grid Optionsvargridbutton on the Variable pane and select Show variables of all scopes.


Now you have the Variables pane visible, simply click on the Add variable buttonAdd Variableto define your new variable/s, their data type and their scopes. It is usually advisable to assign a data value regardless of whether this will be overwritten or not simply because doing so, allows you to evaluate variable expressions and have useful output returned at design time.

Tip: If you do not create the SSIS variables up front, it is sometimes possible to create them directly from the container or task editor. For instance, when in the Foreach Loop Editor we can create new package (or scoped) variables when attempting to define a Variable Mapping. We shall be discussing Variable Mappings further in a future post.

Declare SSIS variables to Script Task

This is one of those operations which might not be obvious to anyone returning back to SSIS after any length of time but SSIS variables are not available to Script Tasks unless you explicitly declare them to the Script Task (yes I know they are declared to the package, but the script task needs to know that you want to use them and how).

If you don’t and attempt to use them you will see something like this…


DTS Script Task has encountered an exception in user code:
Project name: ST_a814ca7dfd6244b3a42a81260bb1d4a8
Exception has been thrown by the target of an invocation.
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Think of this step as really declaring the variables for interop. It is important that you decide how the SSIS variable/s is/are going to be consumed by the script code, so you must define them as ReadOnlyVariables or ReadWriteVariables. There is a misconception that placing your SSIS variables in the read only list would prevent write backs in code, but this is not exactly the case and the reason to use them is more driven by the necessity to serialise access to them more than anything else. For more information about those two list types you should read Todd McDermids excellent post titled Use ReadOnlyVariables and ReadWriteVariables properties in Scripts and follow his best practice advice when choosing between them. You can always change which list you use afterwards if absolutely necessary.


Launch the Script Task Editor by selecting the Script Task properties and clicking the ellipses button next to either of those options just mentioned. Select all the variables that you wish to make available to the Script task and click OK to close the Select Variables dialog.

Assign SSIS variable/s to Script variables (or visa-versa)

So far, all we have really done is defined the SSIS variables and put in place the plumbing to allow their consumption in code. As mentioned, the way you are allowed to consume them will depend upon how you have declared them to the script task.

Launch the Visual Studio Tools for Applications script editor by (you guessed it) clicking Edit Script… while still in the Script Task properties dialog.

Scroll to the public Main method and you will see:

// TODO: Add your code here

Obviously this is your place to input your code block, and more importantly, the place where you will consume your SSIS variables!

In order to assign your SSIS variable to a script variable:

var myvar = (int)Dts.Variables["User::MyVar"].Value;

In the example above we are using inferred typing and explicitly casting our SSIS variable (and therefore script variable type) to Integer. If you have made a mistake with the SSIS variable type or cast to the wrong script variable type, this might result in a runtime error or unexpected results.

In order to assign your script variable value back to a SSIS variable the operation is pretty much reversed as you would expect:

Dts.Variables["User::MyVar"].Value = myvar;

Notice that in the example above the assumption is that the SSIS variable datatype is compatible with the script variable type.

Once you have finished writing your code block you may save your code and close the Script Editor. All that is left is to click the OK button to close the Script Task Editor and run your package!

To be honest, that is all there really is to using SSIS variables within a script task.  I hope this has been a useful post for you, but at very least I won’t keep forgetting!

extendingssisWant more Bitesize SSIS tips? Then keep an eye open for the other posts in the series and if you cannot wait until then, you might want to take a look at my good friend Régis Baccaro’s new book Extending SSIS with .NET Scripting which is out now through Apress. And for those who are curious, No he didn’t give me a free copy!

Posted in SQLServerPedia Syndication, SSIS | Tagged , | 1 Comment

7 Days of PASS – Day 7

confI wake at 3 a.m. to find my plan of preparing my sessions into the late evening in tatters having completely crashed and burned after the huge meal the night before. My main session is scheduled to be delivered at 8 a.m so that means I have approximately four hours to try and nail it. First I get showered at dressed, and then start running through my slide deck and correct error after error. Once I am happy with the deck, I read through my notes and study each slide until I am happy that I understand each one. I run through the demos and complete main session preparation before returning back to the lightning talk. After working four solid hours I head to the Convention Centre so that I can grab a quick bite to eat and setup in my room with enough lead time before the session starts.

While nibbling on a tiny piece of breakfast (I don’t like eating before a session) I am joined at the table soon after by Chris Yates (who I met yesterday) and we chat for a couple of minutes before saying our goodbyes and me heading onto my session. I am hoping that unlike last year, my room will be small and intimate but upon arriving I see that it is quite the opposite. Thankfully there are smatterings of attendees already, so I can tell that the turn-out is going to be ok (or at least much better than in 2014).

I setup my session and switch to duplicate screen mode in order that my demos will be visible to the audience, and then get my slide deck up. Virtual Machines are all started and working fine, so the only hurdle now is to compose myself, calm my mind and ensure that I get clarity on the next three minutes of intro I am going to start with. For me, a good presentation beginning is usually key to a good ending, and should I be clumsy at the start then it will be an uphill battle from that point on.

So I make sure I welcome the audience, give them a very quick set of reasons to stay AND reasons to leave, and try not to waste any more time on all this than necessary. Often I tend to worry about these opening minutes more than anything else in the presentation and have been guilty at times of going too slow across the first five slides. I know we have a lot to get through today so I attempt to avoid waffling, avoid talking about myself and get straight to the point. Things are moving fairly well now, and I am happy that it is all going to plan – that is until I arrive at the first demo.

Beam me up Scotty! Warn-out after session but pleased we have more questions

Beam me up Scotty! Warn-out after session but pleased we have more questions

Upon entering the first demo, I find that screen duplication is for some reason is not now set correctly (despite doing so at the start) and my attempts to flick the screen into duplicate (Using Win key + P) is not working. Not only have I lost video to my laptop, but the main projector screens are going nuts and look like a crashed Linux boot screen. During this time I try to stay completely calm, apologise to the audience and attempt to make the change again -to no avail. I look for the room assistant but cannot see her, so I let out a little joking yelp for help :). I’m not sure where she was hiding, but suddenly she appears from behind the screen and attempts to help fix the problem. Despite us both trying every trick in the book, we both quickly realise that this problem is not going to get fixed any time soon. We have managed to get video displaying on the screen ahead of me (the screen on the floor) and also on the main projector screens (but still no output on my laptop). Even though I have only lost a few minutes, I decide that enough is enough and grab my wireless mouse and jump down from the stage, near the screen on the floor and attempt to run through the first demo. This is far harder that it might sound, and after successfully navigating through the first section of my demo -with help from the audience to click the right buttons (my eyesight is struggling with the resolution), I decide to talk through the remaining sections and explain what we would expect to happen and why.

Now back on track (and still without a screen on my laptop) I am forced to present from the floor screen as my reference, despite no longer having my PowerPoint presenter notes available, I continue with the session as best I can, and it runs as  smoothly as I could hope for. By the end of the session and despite some technical difficulties, I am happy with the session and audience interaction has been quite good. What was particularly pleasing was a bunch of people coming to talk to me at the end (always a good sign), and one chap thanked me for ploughing through the technical problems. I was also pleased to close the session almost dead on-time.

Lightnings-are-a-frightening! Me getting my NOLOCK mojo on

Lightnings-are-a-frightening! Me getting my NOLOCK mojo on

I only have the lightning talk left to present now, so head back to my hotel room for a quick run through my small deck of slides, notes and demo. I only have time enough to do this once and so I am heading back to the Conference Center to find the room I am about to present in. I was first up to present, with Rob Volk needing to present right after me (due to his session related gags to mine). My session went fairly well considering its gestation period and birth-date, but there is definitely scope for improvement and a couple of snags to be ironed out. One big take away from this Summit (and the last few SQLSaturdays I’ve presented at) is that it is time to upgrade my Laptop AND Surface to help with transition and Video connectivity issues -so this will be high on my list when I get back. Congratulations goes to Rob, Tim, Andreas and Murillo for their excellent Lightning talks and it was a pleasure to be part of this session with them.

Misbehavin' in Uwe's Session

Misbehavin’ in Uwe’s Session

Straight after the Lightning Talk the “Swarm pack” headed straight to the Tap House for lunch. Sadly our waitress gave us perhaps the worst service I have ever seen and it took 30 minutes even to be served a beer! After finishing the meal we headed into Uwe Ricken’s (web|twitter) session Change Data Capture (CDC) Case Study and Checklist and he proceeded to deliver an excellent session. I know Uwe really well, having got to know him from other events and also as a speaker to my own. Not only is he one of the nicest guys you can meet, but as a fellow MCM, I respect this guy immensely and his depth of knowledge on SQL Server is obvious upon first speaking to him. Uwe did something in his session that Germans are not supposed to be able to do… HUMOUR!

Editors note: I am beginning to think that the stereotype of Germans not having a sense of humour is grossly exaggerated -especially after getting to know Oliver, Tillman, Kostja, Gabby and Uwe very well. But perhaps they are the exception to the rule :)

His presentation was riddled with one-liners and high-jinx and I really enjoyed it. Having recently been heavily involved in a CDC proof or concept project, I was probably very prepared for this session. The only thing I think was not covered by the session was the use of CDC in HADR solutions, but to be honest that would probably have required a half day session to deliver rather than 75 minutes.

Not long after Uwe’s session we headed over to the final of Speaker Idol of 2015. The first session I saw, the speaker was understandably very nervous but did a good job -like all the other candidates. Sadly the winner of the competition was the presenter of the one and only session I missed (typical!), but I was told it was well deserved.

As the end of the last official day in Seattle drew to a close, I and the usual suspects headed to the Local 365 Cafe and things went well at first (service was good) -up until the entire PASS conference team turned up to their pre-booked tables. Not sure of the odds of us going to the same restaurant as their end of Summit get-together, but we did. And upon their arrival our service almost disappeared! Once we finally did eat, the food was ok but very expensive. One point of amusement came when Jens received his bill and found that he now had $100 less than he did before it arrived!!!

Didn't enjoy this cocktail at all and perhaps this was the thing that made me feel ill

Not the nicest drink I’ve ever chosen

The only real sour point of the evening came at the end when I parted company with everyone. I started to feel really unwell all of a sudden and was now getting really really bad stomach pains and felt sick. The only thing on my mind was getting back to my hotel room as fast as possible which grew harder with each step. Ironically, just before I reached sanctuary, I bumped into Wendy Pastrick (web|twitter) and a group of others who stopped me to say hi and ask me what I was doing tonight. I almost had to push past in my mad rush to get to my room before I collapsed, and sent my private apologies to her later when I was feeling a little better -so sorry again Wendy!

Thankfully I suspect my pains and illness was related more to tiredness and overindulgences during the week but by morning was nearly 100% recovered. Overall this was a fantastic Summit, and I am very grateful that I was allowed to play a small part of it and hope you have enjoyed my 7 Days Of PASS series.

Well done PASS!

See also in this series:
7 Days of PASS – Day 6
7 Days of PASS – Day 5
7 Days of PASS – Day 4
7 Days of PASS – Day 3
7 Days of PASS – Day 2
7 Days of PASS – Day 1

Posted in Community, SQLServerPedia Syndication | Tagged | 9 Comments

7 Days of PASS – Day 6

nikocrazyIt’s my 2nd and final day on the Blogger Table and also the 2nd day of the Summit proper. This time I am seated at the opposite end of the table to the day before and soon after sitting down I am greeted by Chris Yates (web|twitter) who turns out to be a really nice guy and a pleasure to sit with. This time the keynote focuses more on the logistics of PASS growth and their finances more than anything else. After an all too long discussion on those points we finally get to the ever brilliant Dr David DeWitt and Dr Rimma Nehme who talk about IoT. I’ll be honest and say that I didn’t find much (personal) value in this discussion but that is probably because I am more interested in technical nitty gritty rather than more generalised discussion, but there is no doubting those two are not only brilliant individuals but are an incredible team together. Sad then that the real stand out announcement of the entire Summit Keynote sessions for me was their shock news that they would not be working together after this Summit due to David retiring and Rimma moving onto other things. The result was a couple of tears in my eyes and a standing ovation from all those present. I’ve been watching these guys present (especially David) since my very first Summit, and this feels like an end of an era to all concerned.

I hang around in the Conference Center and head to the coffee shop in order to seriously try to nail my lightning talk in between waiting for the Women in Technology Luncheon. Yes folks, don’t try this at home, because sessions should be completed way before heading off to major conferences and if you don’t, you will only end up hating yourself for it. This last couple of years has provided me with some understandable excuses for the situation I find myself in (I’ll speak about that in the future), but whilst I hate being in this position, I cannot change what is done and try to focus my energies on getting where I want to be. I had conceptually been planning on working in The Matrix film plot into my NOLOCK Chronicles lightning talk for weeks and months (even having bought a DVD for the stills), but so far had not managed to match the content. My obstacle was mainly because I kept wanting to have the entire story mapped to my content, but when I realised this was clearly ridiculous for a lightning talk, I decided to find the start point and end points and the rest fell into place.

My deck is 70% complete within a few hours, just in time for the Women in Technology luncheon and I make my way towards the Conference hall and some food.

Written entirely in BASIC, Football Manager inspired me to get involved with I.T.

Written entirely in BASIC, Football Manager inspired me to get involved with I.T.

The guest of today’s W.I.T luncheon is Angie Chang (twitter|web) who is involved with an initiative called HackBright, which aims to help Women enter into (and succeed with) IT careers. Her resume is quite impressive at such a young age, but something is slightly niggling me a little bit from the discussion and I’m not yet sure what it is. Upon the first round of questioning from the audience not a single person comes to the MIC which was a little unusual and resulted in me thinking that perhaps I should. While I am summoning courage to do so, a very small queue of people starts to line up while discussion recommences. Denise McInerney (twitter) is a great interviewer and I suspect she would do well with her own day time chat show.

During another break to questions I finally understand what was niggling me. I think many of these p.I.T (people in I.T) initiatives focus too much on addressing problems rather than getting at the root causes, and I think HackBright potentially falls into that camp. I feel that there are some social conditioning reasons why ladies are moving less and less towards Technical professions and rather than focus just on encourage older girls to start coding, perhaps we should also be looking at encouraging young girls to take their first steps with I.T. I ask Angie if she believes that the gaming industry is partially responsible for not providing enough games targeted towards girls and young women. I also made the point that I first started playing computer games at a very young age which made me then want to code them! I believe if people have a strong enough desire to do anything, then barriers will not be strong enough to prevent the charge.

I’m not sure I managed to get my point across as well as I’d hoped, for Angie’s reply was that there are games out in the market place (using Minecraft as the example), to which I replied that this example was the one and only game that my daughter was interested in. She seemed a little confused as to exactly the point of my question so I tried to explain that after noticing a drop off in female speakers at my own I.T Conference, I had asked one what exactly she thought might be the reason. She offered me the “not enough young girls playing computer games” discussion. I don’t think Angie really bought into this idea and responded that there are different reasons why that happens.

Editors note: Upon doing my own research after the W.I.T meeting, I was very surprised to find a huge amount of material regarding Women and the gaming industry, so I’d like to see more discussion in this area.

Despite being unable to get my point across, I enjoyed listening to Angie, and the younger perspective was a refreshing change to Women in Tech discussions. Not long after the luncheon is over I hurried back to my hotel room to continue working on my sessions for 3 hours or so. Things are moving along and I am determined I will get to where I want to be.

jensThere is only so much working from a hotel room that my brain can put up with before turning to Jello, so at around 4.30 P.M I head back to the Conference Center to see what is going down. I arrange to have dinner with Jens Vestergaard (web|twitter) and fellow “Swarm Pack” stalwart Kenneth Nielsen (web|twitter) but before I have chance to meet them I get a DM from SQLPASS twitter account telling me I’m late for the meeting….

I quickly realise that I had been invited to a Blogger Q&A with the PASS Board Of Directors, and I only really understand the importance of the meeting when I actually walk through the door to find a very small number of people (5) in attendance with the Board. I’m instantly humbled that I was invited to this discussion and that my opinion is sought and apologise for my tardiness. I will blog about this meeting in due course, but all I would like to say for now is that I was very grateful for being invited to participate in this meeting and happy that I could play a part in it.

Straight after the meeting I grab Kenneth and Jens and we head over to the Dragonfish Cafe (another place I have been wanting to visit for years) and proceed to consume inordinate amounts of Noodles and Sushi until our stomachs are ready to burst.

My intention is to work on my sessions for several hours after returning back to my room, but the reality is that as soon as I take off my shoes and lie on the bed, I instantly fall asleep (at 8 P.M.) and nothing bar an earthquake will wake me.

See also in this series:
7 Days of PASS – Day 7
7 Days of PASS – Day 5
7 Days of PASS – Day 4
7 Days of PASS – Day 3
7 Days of PASS – Day 2
7 Days of PASS – Day 1

Posted in Community, SQLServerPedia Syndication | Tagged | 8 Comments