How to add new column in SSIS Package at run time

Question

How to add new column in SSIS Package at run time

Sarath Vellampalli

SSCertifiable

Points: 5822
More actions
May 7, 2007 at 5:45 am

#178276

Hi All,
I am new to SSIS, I created one new package and i want to add one new column in the destination table at package runtime.
How can do this
Anybody know this please tell me the solution.
Kindest Regards,
Sarath Vellampalli

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

In the 'data flow' editor add the 'Derived Column' component between your source and destination. There you can add new columns as well as perform some clean up operations on the source columns. I typically use this to add the time the rows are being inserted and perform any kind of cleanup on the source data.

Hope this Helps!

--

Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Sarath Vellampalli SSCertifiable Points: 5822 More actions · Answer 2

Hi Ben,

thanks for your help on SSIS, but how can use that task, can u tell me the procedure of 'Derived Column Task' for adding new column to destination table.

while i am trying to add new column to destination it shows some error like expression property was empty.

please send the steps of this task.

Kindest Regards,

Sarath Vellampalli

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Have you been through either the samples for SSIS or Books OnLine (BOL) (see here for a 'how-to' for this exact topic)

Steve.

Sarath Vellampalli SSCertifiable Points: 5822 More actions · Answer 4

Sarath Vellampalli

SSCertifiable

Points: 5822

May 8, 2007 at 12:01 am

#704832

Thanks Steve

Kindest Regards,

Sarath Vellampalli

vinothkchandran Valued Member Points: 53 More actions · Answer 5

Sarath Vellampalli (5/7/2007)
Hi All,
I am new to SSIS, I created one newpackage and i want to add one new column in the destination table at package runtime.
How can do this
Anybody know this please tell me the solution.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Sarath Vellampalli (5/7/2007)
I created one newpackage and i want to add one new column in the destination table at package runtime.

If you want to add a column at runtime in the schema of the destination table, you need to issue an ALTER TABLE ADD ... command in an Execute SQL Task. Your dataflow will however crash like it has never crashed before, as SSIS has strongly-typed metadata (aka you cannot change it at runtime).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

KoldCoffee SSCoach Points: 18971 More actions · Answer 7

In my case I added a fiscalyearcolumn to the destination table, for which there is no corrolary in the source spreadsheet, but the value will always be '2012' for each imported row.

Since your situation was close to mine, would you please share with me the name of the data flow object you use to generate the values that must end up in the destination table, during run-time?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Sarath Vellampalli (5/7/2007)
i want to add one new column in the destination table at package runtime.

Even in the world of ETL, never mind that of an RDBMS, this is normally a very bad idea. What type of column are you adding and what is the business reason behind adding it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 9

OK, thank you very much for replying!!!

so, I've changed my approach. I've manually added the column in source file and on destination sql table as smallint.

My flow is:

Excel Source -> Derived Column -> Data Conversion -> OLE DB Destination and my error is now only at the last data flow task.

1. Excel Source maps FiscalYearNumber

2. Derived Column does conversion so that for a given empty column it will supply the value "2012" FiscalYearNumber==”0”? “2012”: [FiscalYearNumber]

3. Data Conversion converts data type for this column to two-byte signed integer.

4. OLE Destination goes red with the following error:

[OLE DB Destination [684]] Error: There was an error with input column "Copy of FiscalYearNumber" (1170) on input "OLE DB Destination Input" (697). The column status returned was: "The value violated the integrity constraints for the column.".

Will you please give me some idea why?

KoldCoffee SSCoach Points: 18971 More actions · Answer 10

when I look at the input properties for fiscalyearnumber column it shows expected datatype DT-I2 which is what I am supposed to use when SQL table expects smallint.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

hxkresl (12/4/2011)
The column status returned was: "The value violated the integrity constraints for the column.".
Will you please give me some idea why?

Any nulls in the data? Does the table have a NOT NULL constraint on that column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 12

That is an excellent question. When I do the following for the conversion data flow, the import works!

ISNULL([FiscalYearNumber])? "2012" : [FiscalYearNumber]

Thank you.

KoldCoffee SSCoach Points: 18971 More actions · Answer 13

Jeff,

would you consider looking at one more issue? I just added another column to the excel source so that it would map to an existing varchar (15) column on the SQL destination called BusinessOwner. The package *this time* executes without error but nothing populates the destination table.

Flow is: Excel Source->Derived Column->Data Conversion->OLE DB Connection.

Inside the derived column data flow object I added the conditional statement

ISNULL(BusinessOwner) ? "myalias" : BusinessOwner.

with the expectation that it will put my alias in the destination column for every row that is empty.

In data converstion data flow object I specify string [DT_STR] as data type to match the varchar(15).

Like I said, no errors running the pkg but nothing populates the BusOwner column on the destination table.

Thanks so much. Helen

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Gosh, Helen... my apologies. I've reached the limit of my SSIS knowledge (which is quite small, actually) and don't know the answer to your latest problem. Hopefully, someone else will chime in soon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)