Automatically create column in destination table based on source column in SSIS or in SQL

Question

I have requirement to create automatically columns in sql server table using ssis or sql.(you can suggest)

Scenario is like this,

In ODBC Source table I have 10 columns. Using SSIS transformations I am importing 10 columns data into my destination SQL server table.

On second day I have 12 columns in source.....(Here is the problem) Now I need 12 columns in destination also and number of columns always increment everyday.

How can I handle this using any tool or code ....Please suggest.

Thanks in advance ...

and on the third day you have 13 columns? SSIS doesn't play well with this situation. It needs consistent metadata. (i.e. column count and data types). Can you explain the nature of this data? Is there an upper limit to the number of columns that will dynamically appear? — Nick.Mc
– Nick.Mc, Commented May 2, 2016 at 7:20
No there is no limit ....because its source...but it not more than 10 columns everyday. — Jack
– Jack, Commented May 2, 2016 at 7:31
SSIS is definitely unsuitable for this. If you are comfortable with C#, write a C# console app. This kind of 'add a column to a table every table' seems like a bad design. You should normally be adding a row not a column. — Nick.Mc
– Nick.Mc, Commented May 2, 2016 at 12:48

Community · Accepted Answer · 2017-05-23 12:23:56Z

0

You can create BIML and do automation using C# to generate ssis package.

More details are available at below link

Automatically generate SSIS package from BIML script

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered May 2, 2016 at 10:11

sandeep rawat

4,9671 gold badge22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SebTHU · Accepted Answer · 2016-05-03 08:46:07Z

0

@Nick.McDermaid already said it, but adding columns dynamically to a table is a really bad design decision. For one thing, it'll cause no end of problems when you try to work with the data in the table. How will you know when a blank column means "unknown", means "no data in this load", or "this column didn't exist in the load"?

answered May 3, 2016 at 8:46

SebTHU

1,4032 gold badges12 silver badges25 bronze badges

1 Comment

Jack Over a year ago

there is no issue with design, the requirement is like this, for the same i am looking for dynamic column create solution ..and the the table is staging table

Collectives™ on Stack Overflow

Automatically create column in destination table based on source column in SSIS or in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related