This is something that I found tricky when I first started to work with SSIS packages and something that I regularly see asked on the internet, therefore I figured it was something worth documenting.
Simply put, this is a situation in which you run one SQL task on one server and wish the result of that to be used on another server, therefore you need variables to be passed between SQL Tasks within your SSIS package.
I’ve invented a weird example to demonstrate this, but it still works and that’s all that matters.
Basically I’ve got one server which is “live” and one which is a “monitor”. What’s happening is that the live server has one table which has multiple inserts and deletes run against it and the monitor server exists purely to keep a log of how many records are in the table.
Strange, I know. But it’ll demonstrate the point.
So, first things first, I’ve created connections to my two servers (see my previous post for how to do this as I’ll not be covering it again):
The two queries we’ll be running here are as follows:
On the “live” server:
select count(*) numRows
This produces us with the current rowcount of the table. So we’ll now log that.
Here’s the table we’re logging the data to:
if object_id('salesOrderDetailLog') is not null drop table salesOrderDetailLog
create table salesOrderDetailLog
logTime datetime not null,
numRows int not null
Therefore on the monitor server we want to run:
insert into salesOrderDetailLog
select current_timestamp, @rowCount
As you can see, we need the variable populating. So how do we do this?
Well, we’re going to need a variable.
From the menu at the top, click “SSIS” and then “Variables”:
In the resulting window, click on the “Add Variable” icon:
Name it something sensible, and leave it as an Int32 with a value of 0 and with the Expression blank:
Next, drag two “Execute SQL Task” objects to your Control Flow:
Double click the first of these and set it up to connect to the live server and run the Count(*) code. Also, make a note to change the ResultSet option as we are now returning a single row from the query:
Now, select the “Result Set” tab from the left of the window and tell SSIS that the numRows value will be being assigned to the user variable myRowCount that we created earlier:
Click OK and go back to your Control Flow.
Now double click the other Execute SQL Task and fill it in accordingly however, this time, we want to replace the variable in our code with a ?:
The reason we do this is because SSIS then realises that a variable will be being passed in and replacing the ? in the query.
Switch to the “Parameter Mapping” tab on the left and click Add and fill in accordingly:
What this is saying is that our variable myRowCount is being received into the query, it’s a LONG datatype (annoyingly they don’t use SQL datatypes… helpful), and it’s got a size of -1.
The size is irrelevant if you’re not using a specific varchar and therefore for other datatypes should be left at -1 (which means max).
The reason I’ve named the Parameter 0 is because it’s simply easier that way. Basically SSIS will substitute variables into ? placeholders in the order in which they are listed on this screen and therefore it’s easier to keep track of them if you name them by ordinal.
Now simply connect the two SQL Tasks together with your green line and we’ll run the package:
Now if I go to my “monitor” server and look in the logging table I will see the result as required:
As you can see, this is very simple to do and therefore you can now use it as you please as I’ve found this technique incredibly useful over time.