How to Set Variables in an SSIS Package?

Question

I need to use two variables in my SSIS package, @extract_beg_date and @extract_end_date.

How do I set them to behave a certain way? For example, in a stored procedure, it would look like this:

SET @extract_beg_date = CASE WHEN DATEPART(weekday,GETDATE()) = '2'
                           THEN CAST(CONVERT(VARCHAR(10),GETDATE()-2,101) AS DATETIME)
                           ELSE CAST(CONVERT(VARCHAR(10),GETDATE()-1,101) AS DATETIME)
                        END

SET @extract_end_date = CAST(CONVERT(VARCHAR(10),GETDATE(),101) AS DATETIME)

How can I set the variables in the SSIS package without having to use a stored procedure? I want to be able to use it in the SQL Command text like so:

SELECT *
FROM dbo.test_tbl
WHERE [TimeStamp] >= @extract_beg_date and [TimeStamp] < @extract_end_date

Answer 1

I would do this as follows:

1. On the process flow, add an Execute SQL Task.

2. Open the Execute SQL Task and configure the ResultSet property to Single row, and configure the SQLStatement property to:

   SELECT CASE WHEN DATEPART(weekday,GETDATE()) = '2' THEN CAST(CONVERT(VARCHAR(10),GETDATE()-2,101) AS DATETIME) ELSE CAST(CONVERT(VARCHAR(10),GETDATE()-1,101) AS DATETIME) END AS extract_beg_date, CAST(CONVERT(VARCHAR(10),GETDATE(),101) AS DATETIME) AS extract_end_date

3. Still in the Execute SQL Task, go to the Result Set view and Add two result outputs. Set the Result names to 0 and 1 respectively and create 2 variables for extract_beg_date and extract_end_date.

4. During execution time, the Execute SQL task will take the output of your SELECT query and save each value into the two SSIS variables you created. These variables can be used in subsequent steps to parameterize another query.

Answer 2

The Variables pane can be opened by going to the control flow and selecting it from the SSIS menu in the menu bar. This is where you can create variables and set their types.

You can set the variables using a script task in the control flow, or a script component in a data flow. To do this, add the variable to the list of ReadWriteVariables in the script editor and access it as follows:

Control Flow:

Dts.Variables["VariableName"].Value = "hello";

Data Flow:

Variables.VariableName = "hello";

Finally there's putting the variable into a SQL statement. I'm assuming you're using an OLEDB connection at the start of a data flow to do this. In the OLE DB Source Editor, change the Data access mode to SQL command, and put question marks as placeholders for the variables:

SELECT * FROM dbo.test_tbl WHERE [TimeStamp] >= ? and [TimeStamp] < ?

Then click the Parameters button and select your variables in the drop down lists, to assign them to the parameters.

Be careful with the scope of your variables. If the variables are scoped to another data flow task you won't be able to see them when you do your select.