Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

dynamic excel columns into dynamic table Expand / Collapse
Posted Monday, April 15, 2013 5:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 8, 2015 3:54 AM
Points: 63, Visits: 344
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
Post #1442262
Posted Sunday, April 21, 2013 12:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, Visits: 243
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 or google it.

SSIS: How to read Excel Meta Data?

Vikash Kumar Singh ||
Post #1444813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse