I recently had to create an SSIS package that used variables to pass data between procs, and I thought it would make a good topic for a blog post. There are many scenarios as to the how and why to use variables in SSIS, but we’re going to keep it pretty simple. Let’s assume we have some need to retrieve data from Proc A, pass it to Proc B, and store the results in Table C. First, let’s set up our environment:
Use AdventureWorks; Go Create Procedure dbo.LastOrderGet_sp As Set NoCount On; Begin Select Max(SalesOrderID) As 'LastSalesOrderID' From AdventureWorks.Sales.SalesOrderHeader With (NoLock) Set NoCount Off; Return 0; End Go Create Procedure dbo.ProcessLastOrder_sp @LastOrderID int As Set NoCount On; Begin Select SalesOrderDetailID , ProductID , OrderQty , LineTotal From AdventureWorks.Sales.SalesOrderDetail With (NoLock) Where SalesOrderID = @LastOrderID; Set NoCount Off; Return 0; End Go Create Table dbo.testStage ( SalesOrderDetailID int , ProductId int , OrderQty smallint , LineTotal numeric ); Go |
Now for the fun stuff!
(Please note, I’m assuming some basic understanding of SSIS, so I’m skipping the “how to create a project”, etc. stuff and just going to the pertinent parts).
Inside BIDS (Business Intelligence Development Studio), create a new SSIS project and call it what you will. If your Variable window is not already open, open it now by going to View –> Other Windows –> Variables.
Open Variables Window
Now let’s create a variable. To do this, click on the little icon in the upper left-hand corner of the Variables window. Name the variable LastSalesOrderID.
Create a variable
After you create the variable, you should now see it in the Variables window. Make sure the scope of the variable is the name of your project, which is “Blog” in my case (for obvious reasons); this means the variable is defined at the package scope. Once you’ve confirmed that the variable exists, create an Execute SQL task.
(Variables in SSIS, like in other programming languages, can have different scopes. For instance, a package scope means the variable can be accessed anywhere within the package, but a variable with a Data Flow scope can only be accessed within the specified Data Flow task.)
Create Execute SQL Task
Double-click on your Execute SQL Task and configure with the following values:
- Set “Result Set” to Single Row.
- Set your Connection to your appropriate data source.
- Set your SQL Statement to: Execute AdventureWorks.dbo.LastOrderGet_sp;
Set up your Execute SQL Task
Now click on “Result Set” and click on “Add.” You’ll want to put the name of the column that’s returned by the proc in the “Result Name” column; in our case, that’ll be LastSalesOrderID. Click on the Variable Name column and scroll down until you find the appropriate one (User::LastSalesOrderID).
Mapping the results to a variable
Go ahead and add a Data Flow task to the designer surface. We don’t need to use a Data Flow task here — for example, we could use another Execute SQL task instead — but this will help demonstrate one way to use variables.
Add Data Flow Task
Double-click on the Data Flow task and add an OLE DB Source, then double-click on it to open up the properties. Enter the following text in the “SQL Command text” window:
Execute AdventureWorks.dbo.ProcessLastOrder_sp ?
The question mark (?) tells SSIS to expect a parameter.
Edit OLE DB Source
Now click on the Parameters button on the left. This is where we map our variable to our parameter. For the “Parameters” value, enter @LastOrderID (the parameter the stored procedure is expecting). In the “Variables” column, click on the drop-down and navigate to the User::LastSalesOrderID variable.
Map Variables to Parameters
Finally, set up an OLE DB Destination, and configure the OLE DB Source to load into the testStage table.
Configure OLE DB Destination
At this point, you should be able to successfully execute your package. Upon successful execution, the testStage table will return the following results:
Select * From testStage; SalesOrderDetailID ProductId OrderQty LineTotal ------------------ ----------- -------- ------------------ 121315 878 1 21 121316 879 1 159 121317 712 1 8 |
That’s all for now. Hopefully this gives you an idea of how easy and useful it is to work with variables in SSIS.
Great post! This is a very handy technique to keep in your toolbox. I think i’ll be using it a lot! – Tim
If the resultset returns more than one row in the first step does it take the first record, or error out?
@Mark It should proceed without error using the first value returned.
Pingback: SqlServerKudos
Pingback: Weekly Link Post 108 « Rhonda Tipton’s WebLog
Good explanation…. nice.. michelle
Pingback: #PASSAwesomeness : SQL Fool
Pingback: PASS Log Reader Award Winners | Brent Ozar - SQL Server DBA
Pingback: PASS Log Reader Award Winners | The SQL UPDATE Statement
Pingback: 31 Days of SSIS – One Variable to Rule Them All (5/31) | Strate SQL
This article and screen shots is very helpful. Thank you for provding the details in detailed manner. You are super.
very effective!
Super…Super…Super, i reallly appreciate you. Many thanks.
Hi I’m new to SSIS and searched your blog for anything on loading a user var (with xml data) into an sql table as an XML data type. I have a web sevice task that returns an xml file and stores in a user var, I want to load the results directly into sql server as an XML data type.
Thanks.
Great man…write other articles too like Execute Script Task
Very useful
+ 1 to all of the above comments… Very good post.
Very useful blog. I have been searching for something like this for days. Good presentation
Very usefull blog. This is short and upto point.
Can u present some more examples for ‘Uses of variable in different senario’?
noob
yes,i am also new to SSIS but little bit confused about how to use variables in IS finally i got the ans,its very usefull thanks alot Michelle….
It’s the way I was looking for…
Thank you very much for this clear application of variables in SSIS.
Very easy to follow and I enjoy doing this practice in my learning process,
Thank you.
Great Post. Very clear.
Pingback: Getting Started with Variables in SSIS « Faisal Hafiz
http://sqlserverbrains.blogspot.in/
thanks alot….
It is very useful for beginners like me. Very easy to understand. Thanks allot.
very simple and clear explanation
Be aware that the variable names can be case sensitive!
To clarify – the name of the parameter defined in the database must match the name of the parameter that is mapped to the variable in the ole db source of the data flow task.
Tony
no entiendo tu post. de que se trata eee ?