CODEWITHSUNDEEP
sql-servercsvssis
MKasper
MKasper

Reputation: 31

Use SSIS to import multiple .csv files that each have unique columns

I keep running into issues creating a SSIS project that does the following: inspects folder for .csv files -> for each csv file -> insert into [db].[each .csv files' name]

each csv and corresponding table in the database have their own unique columns

i've tried the foreach loop found in many write ups but the issue comes down to the flat file connection. it seems to expect each csv file has the same columns as the file before it and errors out when not presented with this column names.

anyone aware of a work around for this?

Upvotes: 0

Views: 4396

Answers (4)

kiran kumar
kiran kumar

Reputation: 51

Try to keep a mapping table with below columns FileLocation FileName TableName

Add all the details in the table. Create user variables for all the columns names & one for result set. Read the data from table using Execute SQL task & keep it in single result set variable.

In For each loop container variable mappings map all the columns to user variables.

Create two Connection Managers one for Excel & other for csv file. Pass CSV file connection string as @[User::FileLocation]+@[User::FileName]

Inside for each loop conatiner use bulk insert & assign the source & destination connections as well as table name as User::TableName parameter.

if you need any details please post i will try to help you if it is useful.

Upvotes: 0

Dance-Henry
Dance-Henry

Reputation: 953

I got 2 options for you here.

1) Scrip component, to dynamically create table structures in sql server.

2) With for each loop container, use EXECUTE SQL TASK with OPENROWSET clause.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

You could look into BiML Script, which dynamically creates and executes a package, based on available meta data.

Upvotes: 0

Matt
Matt

Reputation: 14381

Every flat file format would have to have it's own connection because the connection is what tells SSIS how to interpret the data set contained within the file. If it didn't exist it would be the same as telling SQL server you want data out of a database but not specifying a table or its columns.

I guess the thing you have to consider is how are you going to tell a data flow task what column in a source component is going to map to a destination component? Will it always be the same column name? Without a Connection Manager there is no way to map the columns unless you do it dynamically.

There are still a few ways you can do what you want and you just need to search around because I know there are answers on this subject.

  • You could create a Script Task and do the import in .Net
  • You could create a SQL Script Task and use BULK INSERT or OPENROWSET into a temporary stagging table and then use dynamic sql to map and import the final table.

Upvotes: 1

Related Questions