December 19, 2011 at 9:32 am
Hi,
Please excuse me, but I don’t normally work with SSIS, I see my question has been asked before but I haven’t found any good answers.. Yet.
How do you take a folder of CSV’s (different columns) and dynamically create the tables in SQL 2008(without creating the tables first).
I have worked out parts of this, I have created a Forereach loop which opens each CSV file in turn and created Script component for the destination.
So then obviously the table need’s to exist first .. this is where I am struggling.
I Now need the CSV definition which I guess I can get once the Flat File source is open .
But how do I do this?
If I had that I presume I could create a SQL statement to create the table ..
But what would the best way to do this?
Many thanks
December 19, 2011 at 9:56 am
Create a work table that has only one VARCHAR(MAX) column.
Use the bulk import task to load your entire rows into this table.
Write a simple script that will break the header into individual pieces, create a table with these column names (if it does not violate SQL syntax - note that you can add columns one by one) and load the data from the remaining rows.
Being in your shoes, I would deal just with VARCHARs and leave any attempts to convert data into other formats to the next step, which, I guess, will be a transform into data mart.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply