[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

How to use SSIS to import data file by looping though the files in the folder?

How to use SSIS to import data by looping though the files in the folder?

Suppose we have data folders as follows:

C:\DataImport\FTP\Transport\In
C:\DataImport\FTP\Market\In

The file types can be: .zip, xls, csv and xml.

How do we dynamically configure the data source to achieve the goal?

The destination is always SQL 2008R2.

0
chuang4630
Asked:
chuang4630
2 Solutions
 
Alpesh PatelAssistant ConsultantCommented:
1. Create variable in that dynamically create folder path
2. assign this path for the Foreach loop as source folder.
3. Loop through each file and insert into table.

For connection to file create dynamic connection string using Expression.
0
 
Reza RadCommented:
as file types are different you need to create different sources for them.
I mean -> excel source for xls files
flat file source for csv files
xml source for xml files
also you need to unzip the compressed file with the execute process task first and then read its data.

also you should note the structure of data , is all structure same? I mean number of columns and data type and name of them..?

by the way I think you mean something else;
did you mean that you want to load these physical files into image or varbinary fields into database?
if yes, you just need a data flow task with IMPORT COLUMN TRANSFORM, I wrote a sample here:

http://josef-richberg.squarespace.com/journal/2010/1/6/simple-series-ssis-import-column.html
0
 
chuang4630Author Commented:
Where do you do foreach loop? So far I have just created the File System Task. Any new task needed?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now