Creating a Staging Table from CSV file directly

Question

We are having a CSV file we need to create a table Dynamically using that CSV file using SSIS and transfer the data to that Table.

Please provide samples to create a dynamic table using SSIS,

Answer 1

How frequent is the process of data loading? Why do you want to create a dynamic destination?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 2

You issue a CREATE TABLE statement with an Execute SQL Task. You can create the SQL statement dynamically using a string variable.

Something like this:

"CREATE TABLE " + @[User::myFileName] + " ... column list ;"

Use the same variable prefixed with the schema in the OLE DB Destination to configure your dataflow.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 3

We need to create a table with out giving any queries .

We just want to pass the input csv using which the table should be created dynamically.

Answer 4

How do you create tables w/o DDL SQL? You need to tell in what DB what kind of table columns to create, right?

You have no OTHER options if you use SQL Server as the target.

I also suspect the input file is each time different, right?

You better off creating the package dynamically based on your findings about the file.

You need to

1) Cycle through the file columns;

2) Create the proper DDL SQL and have the table created

3) Insert the data using a dynamically created package

I must say SSIS may be not the best option here as BULK INSERT can do that in fewer hops.

---

Arthur My Blog

Answer 5

When you say create a table without any queries do you mean no query in the SSIS package. In which case you can stick the ddld scripts in a stored procedure and invoke it from within SSIS. If you are saying you don't want to use DDL at all, I would myself love to learn that.

---

http://btsbee.wordpress.com/

Answer 6

We need to create a table with out giving any queries .

We just want to pass the input csv using which the table should be created dynamically.

I'm sorry, but a crystal ball is not included in this version of SQL Server.
As Arthur en btsbee already mentioned, you need to create the table somehow.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 7

We need to create a table with out giving any queries .

We just want to pass the input csv using which the table should be created dynamically.

You may have execute such type of command from SQL Task over your database... [You can replace couple of things with user::variables] 

Now, things like how to create such command and verifying the existence of table, is on you.

SELECT * INTO User::Table_Nm
FROM OpenDataSource( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + 
FilePath +
 "\";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';")
 

Read this before implementing:

http://msdn.microsoft.com/en-us/library/ms179856(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms188029.aspx

Here is one more article, it talks about Excel import but connection properties mentioned in above Code Snippet should make it work with CSV as well.

http://support.microsoft.com/kb/321686

---

Thanks Prasoon