0

First of all I am sorry for asking a question like this. I am beginner in SSIS. I just created some samples and I am trying to change one of the tool I developed in .net to SSIS. My scenario is I have a table with custom properties to my documents. These properties are created by the user as per their reqirements. (The general properties will be stored in a seperate table. The current problem is the custom properties.)

For example.

When user add a custom property like region, My service code will add a column in the custom property table. In this way the table will grow. How can I migrate data from excel to this table. In ssis I need to map the columns. Is there any way to create this logic with dynamic number of columns.

Sample data

Name projectNo region phone email

Name projectNo location contactno interests skill1 skill2 skill3

Here Name and projectNo are to be stored in a table. This is a direct logic. But my problem is the table stores the remaining columns is different.

it is like

ObjectId Prop1 prop2 prop3 prop4

Each prop field will be saved in a master table.

PropId PropName datatype length

Looking forward to your reply.

Jyothish Bhaskaran
  • 501
  • 5
  • 22

1 Answers1

0

I see 2 options for you:

  1. Create another sheet in your Excel (maybe not visible): With a macro in VBA, you can transpose your current data into a new table. (This table could have always the same structure)

How do i loop an excel 2010 table by using his name & column reference?

  1. Use BIML to create SSIS packages on the fly (based on your datasource)

https://www.timmitchell.net/post/2015/03/16/iterating-through-excel-worksheets-with-biml/

Arnaud

Community
  • 1
  • 1
Arnaud Gastelblum
  • 312
  • 1
  • 11