Regd some import issues using ssis

Question

Regd some import issues using ssis

Chinna

SSCertifiable

Points: 5408
More actions
November 2, 2008 at 5:42 pm

#128230

I have created a ssi package which pulls data from oracle production to sql server everyday....suddenly the job failed yeserday i came to know they made changes in the schema in production .....they added a new column to the table ....but to my knowledge it should not fail right if they added a new column in source and they dont have any mapping in destination it will not import that column it should ignore that column and should import rest of the columns...but it did not ..it failed can i know is there any option which i need to set in that way .....plz any suggestions would be helpful.....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 6 posts - 1 through 6 (of 6 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Have you tried selecting explicitly the columns you want imported? I'm guessing your source is set with : SELECT * FROM ...

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Chinna SSCertifiable Points: 5408 More actions · Answer 2

okay even thought i have select * from tbl1 .....it should not fail right ...correct me if i am wrong....

for example ...

i have atable tbl1

step1

col1,col2,col3,col4---oracle source

query select * from Tbl1

step2

col1,col2,col3,col4---sql destination and do the mappings from source to destination.......

it works fine ....

now when they changes to the tbl1 schema in oracle source they add new column...

col1,col2,col3,col4 and col5

here col5 is the newly added column in source...

here i write as select * from tbl1 --so 5 columns in source

destionation ...

here the schema is same so i have 4 columns only ..so it will map only 4 columns ..

but it should import 4 columns ignoring the 5th column but my job fails is there any specific reason ,...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

I don't know what to tell you. This is how SSIS works. This behavior doesn't look right, but there might be a good reason for it. Why don't you post bug report at MS connect and see what they will respond:

http://connect.microsoft.com/SQLServer/Feedback

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

Explicit selection of only those columns which are required is surely the way to go here? Select * from xxx will often throw things out when the underlying xxx schema changes - and what's the point of moving data around that is not required?

That does not answer your question exactly - I imagine that there is some metadata in your source component that has the four columns explicitly named that needs to be modified to accommodate the new one ... even though it is not going to be used.

Phil

Chinna SSCertifiable Points: 5408 More actions · Answer 5

Hey phil....its not like we are not using the 5th column ....we shall change the schema in the destination with 5th column ..but before that it at all the production people change it in source then the job should not fail that is what i need...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman