We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.
Thank you very much,
Regards,
Srinivas Raveendra
I am also facing the same problem.|||The simplest way I can see that is to create a package per every file-table destination pair. Each package could have a ForEach loop to process all files that have the same structure.|||The way we have accomplished this task is to have a table that holds the names of all the files and the names of the tables each file is loaded into.
The SSIS package reads all the rows from this table and runs a foreach loop container for each record; The looping process firstly truncates the staging table and then bulk inserts based on the variable values for each iteration of the loop container. the syntax for the Bulk Insert would look something like this:
EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv''')
If the files you are importing are not exactly the same format as the tables you are importing them into. ie the columns are in a different order or there are more/less columns in the file than in the table, you will need to have format files set up and the syntax with the format file would look something like this:
EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv'' WITH (FORMATFILE=''' + @.strFormatDir + @.strFormat + '.fmt'')')
As a note we have the variables @.strFileDir and @.strFormatDir set at the package level, and dynamically controlled through a package configuration.
For more information on Bulk Insert see Books Online: http://msdn2.microsoft.com/en-us/library/ms188365.aspx
Hopefully this helps.
sql
No comments:
Post a Comment