March 4, 2009 at 3:35 pm
Hi,
I am new in SSIS and wonder how to load data from multiple same by structure excel files, adding the date of each load. For example, file_1 with A,B,C columns loaded into table will become table_1 (extract_date, A,B,C).
The straight-forward solution will be to use a simplest custom Data Dictionary - put extract dates and excel filenames into a table in SQL Server. The question, therefore, goes: can SSIS variables get the values from this table in a loop someway?
March 4, 2009 at 4:49 pm
You can use an execute sql task that return the results of a sql query.
Store the results in a variable of type Object.
Use a for each loop and iterate the object variable and map the columns to variables in the loop.
hope that helps
March 4, 2009 at 5:36 pm
Just to clarify:
For each Excel file, you want to
a) Populate one line of an ImportLog table (LogID, Filename, LoadDate)
b) Populate a physical table with the Excel source data, plus LogID so that you can always identify where the data came from?
Sounds like a ForEach loop to me too ....
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply