How to add new column in SSIS Package at run time

Question

How to add new column in SSIS Package at run time

Viewing 14 posts - 16 through 28 (of 28 total)

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

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

hxkresl (12/4/2011)
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

Did you map the new column in the OLE DB Destination?

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 2

Hi Jeff. Well, I thought my OLE DB output was overwriting destination table each time I ran the package but I was wrong. When I truncated the destination table and reran the pkg I found the import populated as needed.

Ridiculous that it took me so long to try truncate... Thanks for standing by.

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

hxkresl (12/5/2011)
Hi Jeff. Well, I thought my OLE DB output was overwriting destination table each time I ran the package but I was wrong. When I truncated the destination table and reran the pkg I found the import populated as needed.
Ridiculous that it took me so long to try truncate... Thanks for standing by.

Then it is I who have learned. 🙂 Thanks for the very considerate feedback, Helen.

--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)

nike_tly Valued Member Points: 59 More actions · Answer 4

Either you can clean up the data or in the Destination uncheck the checkbox "Check constrainte"

Thanks,

Nikesh

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

nike_tly (12/5/2011)
Either you can clean up the data or in the Destination uncheck the checkbox "Check constrainte"
Thanks,
Nikesh

I'm not following. Which question are you trying to answer?

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

Charmer SSChampion Points: 14903 More actions · Answer 6

hxkresl (12/4/2011)
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?

whenever you get this kinda error it must be a duplicate records (primary key violation) or column does not allow null values...it means you are going to insert null values where you are not supposed to do that...well is that FiscalYearNumber column defined not null or primary key? check this out and if it is like what i said....put a conditional split not to allow null values...before destination or check for duplicate values.........

Thanks,
Charmer

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

Charmer (12/6/2011)
hxkresl (12/4/2011)
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?
whenever you get this kinda error it must be a duplicate records (primary key violation) or column does not allow null values...it means you are going to insert null values where you are not supposed to do that...well is that FiscalYearNumber column defined not null or primary key? check this out and if it is like what i said....put a conditional split not to allow null values...before destination or check for duplicate values.........

Good answer. That's why it was used to solve the problem about 6 posts back. 😉

--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)

Charmer SSChampion Points: 14903 More actions · Answer 8

Jeff Moden (12/6/2011)
Charmer (12/6/2011)
hxkresl (12/4/2011)
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?
whenever you get this kinda error it must be a duplicate records (primary key violation) or column does not allow null values...it means you are going to insert null values where you are not supposed to do that...well is that FiscalYearNumber column defined not null or primary key? check this out and if it is like what i said....put a conditional split not to allow null values...before destination or check for duplicate values.........
Good answer. That's why it was used to solve the problem about 6 posts back. 😉

Sorry i was not seeing your post.....

Thanks,
Charmer

KoldCoffee SSCoach Points: 18971 More actions · Answer 9

But Guess What, Charmer? I have another problem and I bet you can help me 🙂

Dataflow tasks are as follows:

OLE DB source -> OLE DB destination

The source is a SQL staging table, the destination is the SQL target table. The datatypes for the columns I want to bring from staging to destination are already matching, so should not require data converstion, YET, I am getting a datatype error.

[OLE DB Destination [119]] Error: There was an error with input column "POAmountLC" (146) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".

Unbelievable.

Datatype of POAmountLC in the SQL tables is Decimal (15,2), and in SSIS I have tried using DT_numeric and DT_decimal, the latter of which is compatible with Decimal (15,2).

Can you help?

Charmer SSChampion Points: 14903 More actions · Answer 10

hxkresl (12/7/2011)
But Guess What, Charmer? I have another problem and I bet you can help me 🙂
Dataflow tasks are as follows:
OLE DB source -> OLE DB destination
The source is a SQL staging table, the destination is the SQL target table. The datatypes for the columns I want to bring from staging to destination are already matching, so should not require data converstion, YET, I am getting a datatype error.
[OLE DB Destination [119]] Error: There was an error with input column "POAmountLC" (146) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".
Unbelievable.
Datatype of POAmountLC in the SQL tables is Decimal (15,2), and in SSIS I have tried using DT_numeric and DT_decimal, the latter of which is compatible with Decimal (15,2).
Can you help?

is there any null values going into that column...?

Thanks,
Charmer

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

hxkresl (12/7/2011)
But Guess What, Charmer? I have another problem and I bet you can help me 🙂
Dataflow tasks are as follows:
OLE DB source -> OLE DB destination
The source is a SQL staging table, the destination is the SQL target table. The datatypes for the columns I want to bring from staging to destination are already matching, so should not require data converstion, YET, I am getting a datatype error.
[OLE DB Destination [119]] Error: There was an error with input column "POAmountLC" (146) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".
Unbelievable.
Datatype of POAmountLC in the SQL tables is Decimal (15,2), and in SSIS I have tried using DT_numeric and DT_decimal, the latter of which is compatible with Decimal (15,2).
Can you help?

What constraints do you have on that column in the destination database? A unique index?

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 12

Ah yes, Charmer. It was simple and you are correct.

CHA CHA CHA. It WAS a constraint. Those darn Nulls get me every time.

I hope I have finally learned. Thank you ALL.

Charmer SSChampion Points: 14903 More actions · Answer 13

hxkresl (12/7/2011)
Ah yes, Charmer. It was simple and you are correct.
CHA CHA CHA. It WAS a constraint. Those darn Nulls get me every time.
I hope I have finally learned. Thank you ALL.

it happens...we don't give attention to silly things when you have learned a lot....:-)

Thanks,
Charmer

KoldCoffee SSCoach Points: 18971 More actions · Answer 14

I have one more problem....but I don't want to double post. However it is at this link.

http://www.sqlservercentral.com/Forums/Topic1218249-148-1.aspx?Update=1

:rolleyes: