April 15, 2013 at 5:37 am
I have excel with dynamic column and i want to load into table creating dynamic columns.
I need to add new columns in the same table
kindly suggest me the approch for the same
source excel -1
=========
Emp_Id No_Frame
source excel -2
=========
Emp_Id No_Frame No_Sales No_Month No_Purchase
like these, the source excel will have more / less columns. based on this, i need to insert into my database table
I willnot be able to use SP
select *
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;Database
as my server doesnot allow to install any provider of MS 🙁
April 21, 2013 at 12:47 pm
What is common between your Excel source and Database Table? Does the column name or sequence of column as you have shown in the example appears as they are in DB table.
I would suggest you to
1. Create a Script Task in SSIS and read the excel columns one by one and create a data table out of it.
2. After that you can compare the columns you have in data table with your Database table and add any new column that does not exist.
3. After that you can build a dynamic INSERT statement and pass on the column values as parameter.
If you can share your database table strucuture it might help to provide some solution. At this point the requirement seems incomplete.
If you do not know how to read Excel columns into SSIS you can refer below link or search on sqlservercentral.com or google it.
SSIS: How to read Excel Meta Data?
Vikash Kumar Singh || www.singhvikash.in
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply