Execute SQL Task Parameter Mapping

To pass SSIS variables into an SSIS package is straight forward and very simple. Its just hard to find documentation/tutorials stating explicitly how to do it.

In my simple example, I am attempting to track how long it takes to execute a task. I will infer this from the time the previous task completed.

I created a table to simply track when each task completes.

CREATE TABLE dbo._load_tracker(
id int identity(1,1) NOT NULL PRIMARY KEY
,obj VARCHAR(500)
,startdatesec int
,enddatesec int
,dtstamp datetime default(getdate())
,dtstampUTC  datetime default(getutcdate())
)

GO

The query I intend to execute is:

INSERT INTO [willowcca].[dbo].[_load_tracker]
([obj],[startdatesec],[enddatesec])
VALUES
(‘Completed Insert into Object Name’,?,?)

By Default when you attempt to add Parameters they are created with a default name of NewParameterName.

Simply delete these names and replace each one with the correct ordinal position of each “?” in your SQL Statement.

Variable Name| Direction| Data Type | ParameterName | Parameter Size

User::StartDateSec | Input | Long | NewParameterName | -1
User::EndDateSec | Input | Long | NewParameterName | -1

So in the case above, I change the Parameter Names as follows.

Variable Name| Direction| Data Type | ParameterName | Parameter Size

User::StartDateSec | Input | Long | 0 | -1
User::EndDateSec | Input | Long | 1 | -1

I have found that when executing a Stored procedure you should make the parameter name the exact name and datatype that is being used by the Stored Procedure definition.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s