SSIS packege to load CSV file in new sql table every time it runs.

Question

Hello, I am new to SSIS.I need to create a SSIS package where CSV is the source file and SQL table is the destination. Users place CSV file once in a week, for every run (on a weekly basis) it needs to create a new dynamic SQL table in DB.Table name will date extension along with the file name. Can any one help me with the steps to achieve this? Thanks in advance,

Answer 1

Hi @Monalv-MSFT ,

CSV file has the same structure with the same number of cols and same data types, Thanks for the clear explanation with screen shots . It works for me.

Thanks again!

Answer 2

Hi @Kavitha Chandra ,

May I know if the CSV files have same structions( same columns and same datatypes)?

If yes, we can use Foreach Loop Container to foreach the CSV files, use Execute SQL Task to create new sql tables and use Data Flow Task to load data from CSV files to sql tables.

We should execute the Foreach Loop Container and the Execute SQL Task firstly.
And then execute the Foreach Loop Container and the Data Flow Task after disabling Execute SQL Task.

Please refer to the following pictures:




Variable SqlQuery-Expression:
"CREATE TABLE "+ @[User::TableName] +" (
[Column 0] varchar(50),
[Column 1] varchar(50),
[Column 2] varchar(50),
[Column 3] varchar(50),
[Column 4] varchar(50),
[Column 5] varchar(50)
)"
Variable TableName Expression:
"["+TOKEN(TOKEN( @[User::FileName] ,"\", 7 ), ".", 1 )+"_"+ (DT_WSTR, 50) @[User::Date]+"]"

The following links will be helpful:
1.Creating dynamically generated CSV files containing SQL Server data
2.Import Text and CSV Files into SQL Server Database with SSIS Script Task

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.