question

SQLBeginner587 1 avatar image
SQLBeginner587 1 asked

Using a lookup table "date value" as variable in SSIS package

Will appreciate if anyone can help me to do the following:

I use various SSIS packages to load transaction data for specific periods from time to time.

The data that form part of the load is defined by a SQL query based on a date range (i.e. TransDate>='04/01/2010' and TransDate<='04/30/2010' OR by a specific period indicator (i.e. Period='201004').

Problem is: I need to change the periods in the SSIS package (i.e. Dataflow task; SQL query window) manually every time a load is required; e.g.:

SELECT     TransDate, DocType, Description, Amount ... etc.
FROM         SourceTable
WHERE     (TransDate>='04/01/2010') and (TransDate<='04/30/2010')

A lookup "Period" table is available in the database:

Period StartDate EndDate Status
-------------------------------
201003  03/01/2010  03/31/2010 Done;
201004  04/01/2010  04/30/2010 Load

(I can update the value of the Status column when a load is finalized)

Question is:

1- How can I map the relevant values from the Period table (i.e. where Status='Load') to a variable and SQL command within SSIS?

The new SQL query in my SSIS dataflow - should be something as follow:

SELECT     TransDate, DocType, Description, Amount ... etc.
FROM         SourceTable
WHERE     (TransDate>=VariableStartDate) and (TransDate<=VariableEndDate)

(Values for Start- and EndDate variables will be set to values where Status='Load')

2- How do you map a SSIS user variable to a SQL script return value; e.g. a datevalue from the Period table for EndDate where Status='Load'?

3- How do you refer to the SSIS user variable in a SQL query (dataflow task; SQL command) within a package and can this be setup as a global variable?

Appreciate any help.

SQLBeginner587

ssisvariablelookups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Todd McDermid avatar image
Todd McDermid answered

Not hard at all - hope this gets you on the right foot.

Use an Execute SQL Task, with a query much like:

SELECT TOP 1 Period, StartDate, EndDate 
FROM Period 
WHERE Status = 'Load' 
ORDER BY StartDate

Set the ResultSet type to SingleRow. On the Result Set tab, set/add three variables to retrieve the three values from your statement. Keep in mind that the Result Names should be 0 (zero), 1 (one), and 2 (two) if you're using an OLE DB connection, and the data types of the variables should (probably) be Int32, DateTime, and DateTime.

Second part - in your Data Flow, assuming you're using an OLE DB Source, you can use a variable as the source for your statement. You'll now have to use property expressions on the variable to make it dynamic. Create a string variable, go to its properties (F4). Set the EvaluateAsExpression to true, then edit the expression, making it something like:

"SELECT TransDate, DocType, Description, Amount ... etc. "
+ "FROM SourceTable "
+ "WHERE(TransDate >= " 
  + (DT_WSTR, 4)YEAR(@[User::StartDate]) 
  + "-" + (DT_WSTR, 2)MONTH(@[User::StartDate]) 
  + "-" + (DT_WSTR, 2)DAY(@[User::StartDate]) + ") "
  + "and (TransDate <= " 
  + (DT_WSTR, 4)YEAR(@[User::EndDate]) 
  + "-" + (DT_WSTR, 2)MONTH(@[User::EndDate]) 
  + "-" + (DT_WSTR, 2)DAY(@[User::EndDate]) + ")"
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.