Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to add new column in SSIS Package at run time Expand / Collapse
Author
Message
Posted Monday, May 7, 2007 5:45 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:30 PM
Points: 4,486, Visits: 451

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
Post #363612
Posted Monday, May 7, 2007 10:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267
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...
Post #363738
Posted Monday, May 7, 2007 10:26 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:30 PM
Points: 4,486, Visits: 451

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
Post #363886
Posted Monday, May 7, 2007 11:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 11:39 AM
Points: 1,814, Visits: 3,454
Have you been through either the samples for SSIS or Books OnLine (BOL) (see here for a 'how-to' for this exact topic)


Steve.
Post #363894
Posted Tuesday, May 8, 2007 12:01 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:30 PM
Points: 4,486, Visits: 451
Thanks Steve

Kindest Regards,

Sarath Vellampalli
Post #363897
Posted Tuesday, March 29, 2011 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 4:39 AM
Points: 1, Visits: 8
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.
Post #1085381
Posted Wednesday, March 30, 2011 2:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 13,271, Visits: 10,149
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).




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1086030
Posted Saturday, December 3, 2011 11:45 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:56 AM
Points: 605, Visits: 1,410
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?


it helps to talk it out
Post #1215939
Posted Sunday, December 4, 2011 3:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 36,753, Visits: 31,209
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1216016
Posted Sunday, December 4, 2011 3:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:56 AM
Points: 605, Visits: 1,410
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?


it helps to talk it out
Post #1216021
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse