Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add new column in SSIS Package at run time


How to add new column in SSIS Package at run time

Author
Message
Sarath Vellampalli
Sarath Vellampalli
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4608 Visits: 464

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
Ben Sullins-437405
Ben Sullins-437405
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 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...
Sarath Vellampalli
Sarath Vellampalli
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4608 Visits: 464

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
stevefromOZ
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: Moderators
Points: 1929 Visits: 3754
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
Sarath Vellampalli
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4608 Visits: 464
Thanks Steve

Kindest Regards,

Sarath Vellampalli
vinothkchandran
vinothkchandran
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search