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