Anyone that can help solving the following problem.
I need to load data from an OLDB/AS400 data source. Within the SSIS package I have setup a dataflow task with an OLDB/IBM DB2 data connection – and a SQL query with specific date periods (period range) as limit for the relevant records that need to be loaded from the AS400 source system. Only problem is that the SQL query within the dataflow (source) task need to be manually adjusted every time when a new load (or the loading of historic period data) is required. Also need to mention that I use a number of different SSIS packages where the period range need to be changed manually every time when data for a different/new period need to be loaded.
I was thinking of solving this problem by using/referring to an additional Period table within the SQL 2005 database (also the destination data connection) to lookup the relevant/next period range that need to be loaded. This can be done by adjusting the various SSIS packages (i.e. all the SQL source task scripts) to not only link to the relevant AS400 source data tables – but to also refer to / “lookup” the relevant period range values to be used from the SQL period table. The problem with this will be that SSIS do not allow for a dataflow task to refer to various data connections within a single (the same) dataflow. Does anyone know of an alternative way to approach this – or a means around this problem?
My SQL command as Data Access mode – within the Dataflow/Source Editor – reads as follow: SELECT ( …. various columns from the OLDB/AS400 data source ….) FROM …. WHERE ((AS400SourceData.Date>=SQLServerPeriod.BeginDate) AND (AS400SourceData.Date<=SQLServerPeriod.EndDate))
Maybe the use of “Global Variables”/parameter values or a similar method to lookup/and refer to values as input to SQL text used in SSIS as data access mode? This will really help to eventually automate the complete loading process. Any help will be much appreciated.
Regards, SQLBeginner587
