Archive for the ‘Parameters’ Category

Passing Variables to and from an SSIS task


So, in the spirit of building up a knowledge base, I have constructed an example to demonstrate passing variables into and out of anExecute SQL Task and Script Task

The two tasks do fundamentally the same thing, which is to try and date-stamp a file.  The final variable value “FullPath” could then be easily used by a File System Task to copy/move or delete a file perhaps.

I suppose most SQL Server developers would be more comfortable knocking up this fairly trivial code in SQL, but the difficulty is in passing and catching the input variables in the task.   This example demonstrates the problem.

I have set up a package with three String variables called

  • FileName, which has a data type of String and an initial value of “Import.txt”
  • FolderName, which has a data type of String and an initial value of “c:\”
  • FullPath, which has a data type of String and no initial value

… and an ‘Execute SQL Task and a Script’  Task.

The package is called, rather imaginatively, “Package3”. The scope of the variables is at the package level.  One thing to note when you set up variables (choose SSIS-VARIABLES from the top menu) is to make sure you have clicked on the package and not a task when you create or add a variable.  If you create a variable while being clicked on a task (therefore with task scope) then the variable will disappear from the list when you click up to the package.  Sorry to be an old dog but I initially found this a bit confusing.

The simplest way to inspect your variables is to set a break-point on the task (right click on the task and choose EDIT BREAKPOINTS) for the OnPostExecute event of the task.  This will then allow you to  inspect the value of the variable after the task has completed its execution.  The red dots on the tasks indicate that there are already breakpoints set up on the task.

Doing it the ‘Execute SQL Task’ way

In the ‘Execute SQL Task Editor’ in the ‘Parameter Mapping’ section, (double-click on the task and choose Parameter mapping), I have set it up so that the two variables i.e. User::FolderName and User::FileName are added to the dialogue box.  Each has a Direction of “Input” which seems logical and a data type of VARCHAR.  The parameter names that I have used are just the ordinal positions of 0 and 1, which is what the context help suggests.  In other words, the value of User::FolderName is the first input parameter and User::FileName is the second input parameter.  The parameter lengths are 50. In other words, we are dealing with a varchar(50) parameter.  The initial default values for these, when you set them up, are -1 which tells me nothing I am afraid.

For the Result Set options on the Execute SQL Task, I have put in the aliased name of the output column from my query, which, giving my poetic instincts full rein, I have called FullPathFromQuery, and I want to pass this to my variable User::FullPath.

‘So what about the query?’ you may ask.  Well, if we go back to the General option on the Execute SQL Task Editor, you can see the code and I will list it again here

DECLARE @YesterdaysDate varchar(8)

DECLARE @Filename varchar(50)

DECLARE @Folder varchar(50)

DECLARE @Etc varchar(50)

SET @Folder = ?

SET @Filename = ?

–SET @Etc = ?

SELECT @YesterdaysDate = CONVERT(varchar(8),DATEADD(dd,-1,getdate()),112)

SELECT @Folder + SUBSTRING(@Filename,1,CHARINDEX(‘.txt’,@Filename)-1) + ‘_’ + @YesterdaysDate + ‘.txt’ AS FullPathFromQuery

For such trivial code you would not want to set up a stored procedure I suspect, so the option of passing parameters to a stored procedure is not really there.

The only way to pick up these input variable values is to use question marks “?” in the order that they are passed.  This query as it stands will clearly not parse in query analyser window so you can only really test your code by running it in the task: This is not ideal.

You must also set the ResultSet option to be “Single row”.

If you run this to the break point one can see that the variable User::FullPath has been assigned the value  C:\\Import_200870805.txt ( I ran this on 6th Aug  2008) so the code must be working.


Parameters Don’t Always Work in your Execute SQL Task

Chances are, if you’ve used SSIS in any capacity, you’ve used an Execute SQL Task.  I would say that about 95% of all the packages I write include at least one.  And if you’re a fan of making your packages dynamic, chances are you’ve also probably used the Parameter Mapping tab in the task.  Well most of the time this is fairly straight forward and easy to use…  For example, you can simply place a question mark in your SQL statement on the general tab and then select the package variable you want applied to the question mark in the Parameter Mapping tab.  However, there are a few instances where it’s not quite that simple…  Here’s an example.

If you have an Execute SQL Task that is truncating a table, and you’re using a variable to tell the task what table to truncate, you might set up your General tab to look something like this (yes, you could also just select the “Variable” option for the SQLSourceType and create a variable with the value “TRUNCATE TABLE <table>”.  However, for this example, let’s say that we want to reuse a variable used elsewhere that only has the table name):

General Tab

And your Parameter Mapping tab would look something like this:

Parameter Tab

Buuut, unfortunately if you do this you will also see an ERROR message that looks something like this:

Parameter Error

What you can do in this situation is put the variable inside another variable inside your query and execute the SQL variable.  Like this:

Parameter Fix