How to Pass parameter in ADO.NET Source SSIS

Question

Hello I need some help This will be my first SSIS package and I am learning as I go. So far this is what I have.

I created a Control Flow. Then I created three ADO.Net connections, twofor source and one for destination. Then I created data flow tasks it copies data from a table in one database into a corresponding table in another database on the same server. data flow task has an 2 ADO NET Source and ADO NET Destination. The destination simply maps the fields together.

Okay, so far so good. This is my problem. Some of the source queries have date criteria. An example would be:

SELECT --Code Here  WHERE CONVERT(varchar, call_date, 112) BETWEEN '6/1/2013' AND  '7/1/2013' 

I want to replace these hard-coded dates with variables. Something like:

WHERE CONVERT(varchar, call_date, 112) BETWEEN STARTDATE AND ENDATE 

I've read several posts and tried to do what is being described, but it's not sinking in. So please use my example to tell me how to do this. It would be nice if I could have the package prompt me for the Date when I run it, but I'd be very happy just to learn how to pass a variable into the query.

This is the only solution I know because I just a beginner here in SSIS package I hope someone can help me

Answer 1

Since none of the answers here actually answer the question (pay attention to the ADO.NET source, not OLE DB!), here's the real answer.

In SSIS you can't parametrize ADO.NET source. You have to use a workaround.

Luckily, there are few workarounds. One would be creating Script Component that acts like source and code it. However, one can't always easily convert the existing resource into script, especially when he lacks ADO.NET programming knowledge.

There is another workaround, and that would be creating the SQL Query before the ADO.NET Source takes action. However, when you open ADO.NET source, you will notice that Data access mode doesn't allow variable input. So, how do you proceed?

You want to dynamically set the SQL expression of the ADO.NET source, so you have to tell your data flow task to configure the SSIS ADO.NET source component by using Expression.

To make the long story short (or not-quite-so-short :), do this:

• in your package, enter your data flow task with source/destination components
• click anywhere on the background, to have Task properties shown in Property panel
• in Property panel find Expressions property, that can configure various data source/destination properties, and open it using ellipsis button (...)
• under Property, select SQL Command property of your source (e.g. [ADO.NET source].[SqlCommand]) to add one row
• click ellipsis button for the row to open Expression Builder
• build your dynamic query in the Expression Builder

The last step could be somewhat cumbersome for date/datetime parameter. However, here's the example, for your convenience:

"SELECT * FROM YOUR_SOURCE_TABLE WHERE your_date_column = '" +    (DT_WSTR,4)YEAR(@[User::VAR_CONTAINING_DATE]) + "-" +   (DT_WSTR,2)MONTH(@[User::VAR_CONTAINING_DATE]) + "-" +   (DT_WSTR,2)DAY(@[User::VAR_CONTAINING_DATE]) + "'" 

HTH

Answer 2

[ ADO.NET AS A SOURCE TYPE SOLUTION ]

Step 1 Create variables for each parameter you would like to use.

Step 2 Select the task on the Control Flow tab which executes the query

Step 3 Go to the properties of this task to select the Expressions by clicking on the "..." button

Step 4 Select the command property and click on the "..." button

Step 5 Now you can construct your query here including the variables you defined in Step 1. Save when done. Enjoy!