question

KavithaChandra-0714 avatar image
0 Votes"
KavithaChandra-0714 asked Monalv-msft edited

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

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,

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KavithaChandra-0714 avatar image
0 Votes"
KavithaChandra-0714 answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @KavithaChandra-0714 ,

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:
71941-testfiles.png
71906-execute1.png
71838-execute2.png
71879-cmandvariables.png
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]+"]"
71820-flc-collection.png
71932-est.png
71908-dft-oledbdestination.png
71886-ffcm-expression-connectionstring.png

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.




testfiles.png (10.4 KiB)
execute1.png (10.3 KiB)
execute2.png (10.8 KiB)
cmandvariables.png (18.8 KiB)
flc-collection.png (15.1 KiB)
est.png (26.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KavithaChandra-0714 ,

I am very glad that my answer solved your problem.

Please mark a valid answer as "accept as answer".

This can be beneficial to other community members reading this thread.

Thanks for your understanding.

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.


0 Votes 0 ·