3

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lena Zheng
  • 41
  • 1
  • 1
  • 4
  • possible duplicate of [How to pass variable as a parameter in Execute SQL Task SSIS?](http://stackoverflow.com/questions/7610491/how-to-pass-variable-as-a-parameter-in-execute-sql-task-ssis) – Tab Alleman Dec 26 '14 at 14:55

2 Answers2

2

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

Execute SQL Task

  1. 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.

Result set

  1. 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.
Bert Wagner
  • 851
  • 1
  • 11
  • 23
  • When you created the two variables, did you make it a string type or datetime type? – Lena Zheng Dec 26 '14 at 22:36
  • I am trying to set variable values from the "set values" tab to execute an SSIS package. Is there a way to see the available variables from here. – Paul McCarthy Apr 27 '17 at 11:37
0

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.

James Rosser
  • 404
  • 2
  • 6