Home > SSIS Best Practices > SSIS #99 – Pass a date time parameter to the Execute SQL Task

SSIS #99 – Pass a date time parameter to the Execute SQL Task

Early this year, I had a post on SSIS – Pass a variable to a OLE DB Source in a Data Flow. Being able to make your ETL process dynamic by using dynamic variables will make you appreciate SSIS more, and enjoy working with it more.

In this post, I’ll show you one example from my work where my Execute SQL task is expecting a date time parameter in a parameterized SQL statement.

Challenge

I have a variable varStartDate, which is always the first of the reporting month. I want to get a list of 2 moths prior to the reporting month.

In SQL, it’s something like this:

SELECT DATEADD(MONTH, -1, ‘2011-08-01’) AS MNTH_ID
UNION
SELECT DATEADD(MONTH, -2, ‘2011-08-01’) AS MNTH_ID

This will give me:

2011-07-01
2011-06-01

Once I have these two dates, I will pass them into a Foreach Loop Container to get data from a DB2 database.

The entire sequence of my control flow looks like this:

image

For the purpose of this post, I’ll focus on the second control flow task, where I create the MNTH_ID, based on the value from a variable, varStartDate, which is dynamically set in the first control flow task.

Step 1 – create a variable varStartDate

Of cause you need to create the variable varStartDate first. Since it’s date time field, I picked DateTime as Data Type.

image

The value of varStartDate is dynamically set in the first task. I have blogged before on how to use ”Full result set” or “Single row” to send dynamic values to variables.

SSIS – “Full result set” to a set variable of Object

SSIS – ETL Audit Table with Variables Passed to SQL Task

I’ll skip this step in this post.

image

Step 2 – Use an appropriate parameter marker in the SQL query (OLE DB <—> ?)

This step is done in this Execute SQL task.

image

First, write a query in SQLStatement;

SELECT DATEADD(MONTH, -1, ?) AS MNTH_ID
UNION
SELECT DATEADD(MONTH, -2, ?) AS MNTH_ID

Instead of hard-coding the date, I used a question mark in the query. This question mark tells SSIS that we are expecting a variable.

Note: you might ask, “why did you use question mark?”. A technet article from Microsoft can answer your question.

How to: Map Query Parameters to Variables in an Execute SQL Task

According to this article, if I am using the OLE DB Connectio0n Type, the correct parameter marker is a question mark.

image

image

Step 3 – Map the variable varStartDate to appropriate parameter name

  • Click Parameter Mapping
  • To add a parameter mapping, click Add.
  • Choose DATE as Data Type ( do not choose DBTIMESTAMP, DBDATE as Data Type if that’s what you are thinking. Choosing DATE will allow SSIS to map the date time value to the correct value to SQL Server.)
  • Provide a name in the Parameter Name box. I chose 0 and 1 as the parameter names.
    You might ask, “how did you know 0 and 1 are the correct parameter names to use?”

image

Again, according to the above Microsoft technet article, 0, 1, 2, 3, … are the correct parameter names to use if I am using the OLE DB Connection Type.

image

That’s it!

Categories: SSIS Best Practices
  1. August 27, 2011 at 7:19 am

    Good article.

    when talking about the SSIS, I recently met an issue.
    In my package, I would dump some table data from SQL server to Access. So first, I create a access file as a template without data but structure. when the packages run, as my design, i would copy this template access to the output folder as an access destination. It works well in design phase. However, when you deployed, it turns out an issue. Before running any component , the SSIS package will do the pre-validation, then it reports it can not connect the access file(my destination) because it does not go into the output folder as my design– I copy the template file into output folder as the first step in ETL.

    I have some workarounds of this. But I am curious, for this typical case, how you solve it elegantly?

    Like

  2. Sherry
    September 1, 2011 at 9:58 pm

    Sorry about the late reply…if you can automate the entire job, I don’t see any issue with your workaround. – Sherry

    Like

  1. No trackbacks yet.

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

%d bloggers like this: