January 20, 2010 at 10:55 am
I am new to SSIS, so please bear with me here.
I have to import data from a number different formatted textfiles into different tables within our SQL database.
The file names and locations are located in a table within our database. These names and locations are subject to periodic change, so I do not want to use the Foreach File Enumerator for maintenance reasons.
I have decided to use the Foreach Item Enumerator, and for each file I have stored the simple SQL statement as a string which, when executed, gives me the file name and path.
My problem is I cannot figure out how to take the file name and path and use it to dynamically connect to the text file and do the data extraction.
Each item in the list also contains the name of the destination table as well as an id number.
I had planned to iterate through the list and for each item:
(1) Get the text file info (name and path)
(2) Connect to the text file. (My Problem, so far)
(3) Extract the data, and based on the id map it to the right table
(4) Loop until there are no more items in the list.
Any help would be much appreciated.
Thanks
January 20, 2010 at 12:00 pm
You'll need the following tasks:
1. ExecuteSQL task - this is the first task in your control flow. This should query your table and return the results into a variable of type Object for use with the For Each Loop container.
2. For Each Loop Container - loop through ADO recordset and assign column attributes into package varaibles.
3. Data Flow Task - this contains your Flat File data source that needs to bet set up dynamically. Do this by editing the Connection Manager's properties. Set up an expression that uses the package variables populated in step 2 to set the Connection String property.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply