This short tutorial will explain just a few of the ways you can use variables in your SQL Server Integration Services packages. From time to time you may want to create a variable in your package that you can either change easily or can update automatically. Lets say that every week you want to run a report that is built on some information from that week. You don’t want to have to go into the package every week and change the date do you? What if most of the time you want to run that report and only show the information for USA some one up stairs wants it for Denmark. Do you want to have to go into each of the tasks and change the hard coded USA? Probably not.
Requirements
- read Lesson 1 if you don’t already know how to create an integration services project.
- SSIS is only available in the “Standard” and “Enterprise” editions.
- If you don’t have your own dummy database with some data in it. You can download and Install Adventure works Database. I will be using Adventure works in all of my examples. Once you have downloaded the Database. Open SQL Server Management Studio -> right click Databases -> Attach -> Select the file that you downloaded.
Dynamic Date
Our plan is not to have to update our dates every time we run our query.
Getting a Dynamic Date
Drag an Execute SQL task onto your control flow canvas, double click on it to open the Execute SQL Task Editor. The first thing you need to do is add a Sql Statment -> connection : <DBConnection> If you dont know how to create a new DB connection manager you can read Lesson 2: first package the destination in that tutorial shows you how to create a database connection manager.
next you need to add a SQL Statment.
select getdate() -7 startDate , getdate() endDate
Something like that should work. Remember the alias names you have given the two columns you will need that soon. Yes spelling and Case are important.
Result set needs to be set to Single Row
Here is what your general tab should look like.
That done lets move on to the result set tab. On the left click “Result Set” Twards the bottom click “Add”
Result name needs to be the same name as the alias you made in your select. This is case sensitive. write StartDate Click Variable name. In the dropdown click <New Variable..>
The add variable window pops up. You will need to add a Name remember same name as the alias. Name space is User. Value type is DateTime and give it a value. It doesn’t matter what it will be over written it just needs a default for some reason.
Make sure you add both.
Your Execute SQL Task Editor -> Result Set window should now look like this. All we have done is mapped the results of the SQL statement the results will now be placed into our package variables.
Top Tip: You can see all the variables available in a package by right clicking the canvas and selecting variables.
Try and execute the package it wont do much but it should run if it doesn’t something is wrong you need to go back and double check your spelling and Case on all the variables.
Using the Variable
Drag another Execute SQL Task onto your control flow canvas. Using the arrow attack the first Execute SQL Task to the New one. Double click on the new one.
Add the Database connection. Then make the SQL Statement something like this.
select * from ErrorLog where errortime < ? and errortime > ?
The ? tells SSIS that a variable will be placed here.
Go to Parameter Mapping on the left. We need to map our ?’s
Top Tip: Order is critical here. The first ? will be replaced with the first Variable you add here. Mix them up and things will get interesting.
Click “Add” to add a new one. You can make the columns bigger if you cant read stuff.
Variable Name: is a drop down user defined variables start with User:: So the first one you need to select is User::StartDate
Direction: Input
Datatype : Nvarchar
Parameter Name: 0
Parameter Size: 0
Now lets do End date
Variable Name: User::StartEnd
Direction: Input
Datatype : Nvarchar
Parameter Name: 1
Parameter Size: 1
This is what it should look like when you are done. Just click OK and run your Package.
Static Variables
Right click the Control flow canvas -> variables . You have a list of all the variables available to your package. You also add a new variable directly here and give it a static value. Example: User::Country = USA You can then use this variable in the parameter mapping exactly like you did in the example above.
Expressions
The first example was a little silly using the database to get the current date and time. The reason i used that example is that it shows you how to get data out of the database into a variable and pass it on to another task. A better way of doing it would be to create a static Variable but create an expression that uses Getdate(). Create a new variable set the type to String all the way on the right you should see a button with three “…” click on it.
This opens the expression builder window. With the expression builder you can build all kinds of strange and magical values. You could even create a dynamic SQL statement that you could use in an OLE DB source. All you would have to do is change the Data Access mode to “SQL Command from Variable”.
In the expression window of your expression builder add.
(DT_WSTR, 30)(DT_DBDATE) DATEADD("Day",7, GETUTCDATE() )
You need to read this from Left to right.
- GETUTCDATE() gets the current date.
- DATEADD Adds “Day”s 7 to GetDATEUTC()
- (DT_DBDATE) Casts the Datetime to a Date removing the time stamp on the end
- (DT_WSTR, 30) Casts the hole thing as a string.
Click the Evaluate Expression button now. You should see a date now in the “Evaluated Value” box. Easy enough.
Conclusion
I hope this short tutorial helped you understand just a few of the ways you can use variables in your SQL Server Integration Services packages.