question

hope avatar image
hope asked

How to create dynamic table based on csv file?

I am trying to create a package to load latest csv file into the sql server and every time it should create a new table based on csv file. 1) First past of recognizing latest csv file is done, using script task. (thanks you guys for awesome post) 2) To create a table based on the latest csv. I have no clue on this one. Currently, I am using data flow task. a)Flat File Source- using variable base on script task the file name is recognized from the source folder. b) OLE DB Destination - how can i create table dynamically? Also datatype for fields should be nvarchar(max)
ssisdynamiccsv
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hope avatar image
hope answered
Hey thanks for the suggestion. I applied a different approach to that. 1) I used a script task to identify the latest csv file and put that file name into a variable based on code provided on the following link: http://stackoverflow.com/questions/8831060/import-most-recent-csv-file-to-sql-server-in-ssis 2) I used a second script task to to create a table for every csv file based on code provided on the following link: http://sqlage.blogspot.com/2011/02/create-table-for-each-flate-file-and.html Now I need to call the variable using connection string to get the latest csv file. how can I call that variable?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hey, I found a solution to it. Using a file management tool which keeps only one file at a time in a folder and using the code on the second link I can create a table.
0 Likes 0 ·
swirl80 avatar image
swirl80 answered
can you not try something like: SELECT * INTO tempdb..temptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=D:\\Work\\temp\\', 'SELECT * FROM test.csv')
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.