I'm trying to load data from multiple excel files with multiple sheets into different sq server tables. Currently i created a SSIS package with Foreach Loop Container tasks that can load the data from multiple files into 1 table, I have multiple excel files with 17 sheets with different data on it. I need to load it to 17 different tables as each sheet contains different columns. Please help me with any suggestions.
2 Answers
You want to load 17 different Excel files into 17 different tables? (as in different fields, different data types) then you need 17 datafloes. Forget the For Next loop.
If the Excel files were very similar and they were going into one table it might be worth using a For Each with some script.
Comments
This query uses bulk insert to store the file in a #temptable
and then inserts the contents from this temp table into the table you want in the database, however the file being imported is .csv. you can just save your excel file as csv, before doing this.
CREATE TABLE #temptable (col1,col2,col3)
BULK INSERT #temptable from 'C:\yourfilelocation\yourfile.csv'
WITH
(
FIRSTROW = 2,
fieldterminator = ',',
rowterminator = '0x0A'
) `
INSERT INTO yourTableInDataBase (col1,col2,col3)
SELECT (col1,col2,col3)
FROM #temptable
To automate this, you can put above query inside a stored procedure and call the stored procedure using batch.Edit this code and put this inside textfile and save as cmd
set MYDB= yourDBname
set MYUSER=youruser
set MYPASSWORD=yourpassword
set MYSERVER=yourservername
sqlcmd -S %MYSERVER% -d %MYDB% -U %MYUSER% -P %MYPASSWORD% -h -1 -s "," -W -Q "exec yourstoredprocedure"
