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

[ 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!

Answer 2

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 3

Here's the OLE DB Apporach:

Create some parameters for your package. If you make them Required then they are, well, required and your package won't run without some values. The field in the Value column will be the default and used for debugging.

Then create a variable and add an expression:

The Expressions editor uses a washed out version of C#(if you were curious)

Also Make sure to add spaces in your string in between variables Its hard to see but AND has a space on either side like so:

" AND "

I almost forgot. The values for yor parameters have to be in 'singleQuotes'. This can be done in one of two ways: You can either include the quotes when you give a value to the string or you can add some quotes in the expression(what I usually do).

Adding them in the expression would look something like this:

"some stuff " + "'" + @[variable] + "'"

Click evaluate expression to see if your variable is holding the correct string.

The last step is to add an OLE DB Source, set up the connection(if you don't have one yet) and add the query from variable.

Answer 4

Ok here is how i do it.

On your control flow create an Execute SQL task. Set up the DB connection. In the SQLStatment do something like.

select getdate() -1 startDate , getdate() endDate

Set ResultSet on the genral tab to Single Row. Go to the Result Set tab click add Give a Result name something like StartDate then add a new Variable.

Name: startDate  (must be the same name as the alius in the select)
Namespace: user 
type:  DateTime
Value:  02-09-2013

Now when that runs it should fill in a user variable with the current date and yesterdays date. Do the same thing form EndDate

Create your second Execute SQL task Link it to the first. Add this SQL statment. ? will be your variables

select stuff
WHERE CONVERT(varchar, call_date, 112) BETWEEN ? AND ?

Go to Paramater mapping. Add one

Variable Name should be something like User::StartDate 
Direction Input
Datetype: Nvarchar
Parameter name: 0 
Parameter size: 0

add the second one

Variable Name should be something like User::EndDate 
Direction Input
Datetype: Nvarchar
Parameter name: 1
Parameter size: 0

When you add a third one just add Parameter name 2...

Run it.