June 29, 2009 at 5:59 am
Hi all,
I am trying to export multiple CSV files into SQL Database using SQL Server Integration Services and I wanted to know how it would be possible to do so. I have never used SSIS in the past so I would be very grateful if I could get some help.
Thank you, in advance.
June 29, 2009 at 10:04 am
So you have CSV files that were generated from some source that you want to import into a SQL Server instance using SSIS. Are all of the CSV files going to get imported to the same destination tables in SQL Server?
This sound like a pretty simple task for the For Each containter. You can configure the For Each container to loop through files in a directory. Place your data flow task inside of the For Each container and configure your source adapter to read the CSV files by using an expression to point the connection manager to the CSV file names that you read in with the For Each loop.
I'm not sure of where you are with experience level with SSIS so you'll need to let me know if this makes sense and what kind of progress you can make on your own w/o more input. Go ahead and try to piece this together and let me know what you need help with.
June 29, 2009 at 10:24 am
Yes, I need all the help that I can get at the moment as I have been searching on the net for such a long time, I would be very grateful.
All the files are all going to the same destination (there are 13 CSV files in total) where I have created a table in sql.
I don't know if it is necessary to add the following: Data Conversion, Derived Column and Lookup?
At the end of this I would like to have an button where all the CSV files would be imported into DB, because at the moment I have to import them manually which such a long time and I have to do this at least once a week which takes up my time.
Thank you for all your help
June 29, 2009 at 10:54 am
OK, here's what you'll need to do.
1. Set up a For Each Loop Container in your control flow.
-- Collection tab settings
- Enumerator: Foreach File Enumerator
- Folder: your folder where your CSV files live.
- Files: *.csv
- Retrieve File name: Fully qualified.
-- Variable Mappings - set up a variable called FileName and assign it the index of 0.
2. Drag a data flow task inside of your For Each Loop container. In your data flow you'll need:
-- Flat file source
- Connection Manager: select 'New' and go through the setup to point the connection manager to one of your csv files. We'll change this in a little bit to make it dynamic, but just point it to any of your csv files for now.
- Columns tab - set up the columns properties from your source csv file.
-- OLE DB Destination: set this up to point to your SQL Server database's destination table. Set up the column mappings to map your input columns to the destination table's columns.
3. Make your connection string dynamic.
Right-click on your flat file connection manager and select Properties. In the properties window, look for Expressions and click on the elipses (...) button. Select the ConnectionString Property and click the elipses to open up the expression builder. Put your filename variable (@[User::FileName]) into the exression window. This will allow for each iteration of your For Each loop to tell the connection manager which file it is to open up as the source file. When set up correctly, your for each loop will loop through the directory and open up and import each file into your destination table. I would recommend setting your destination table to a test table and once your process is working, change the destination connection to point to your production table.
Your data types must match between the csv file and sql server destination so you may need a data conversion task between your source and destination. You'll most likely need to add a data conversion for each string type column in your csv.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply