SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS won't "spread" data into new fields


SSIS won't "spread" data into new fields

Author
Message
ppritts
ppritts
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 5
I had an SSIS Package that took data from a flat file and moved it into a SQL Server table. It was working OK, but I had to add two new fields to the data. I added the fields to the data output program on the computer that generates the flat file, but had forgotten to add the fields to the heading, so the last field got populated with three fields worth of data. I was using comma delineated fields, with the last field being delineated by <CR>-<LF>, so my last field had data similar to "TRUE, EA, I". So, I went back and added the two new fields to the header and to the database table and deleted and re-created my SSIS package.

In my package, in the Flat File Source, if I edit it and click the Preview button, everything looks OK, and the new data is in the new columns. But, if I edit the OLE DB Destination and Preview it, the new fields are still back in my old last column, and the new columns are NULLs! I've tried deleting and recreating all the components of the package, but can't find what I'm doing wrong. Can anyone point me in the right direction???
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39367 Visits: 19437
ppritts (9/2/2014)
I had an SSIS Package that took data from a flat file and moved it into a SQL Server table. It was working OK, but I had to add two new fields to the data. I added the fields to the data output program on the computer that generates the flat file, but had forgotten to add the fields to the heading, so the last field got populated with three fields worth of data. I was using comma delineated fields, with the last field being delineated by <CR>-<LF>, so my last field had data similar to "TRUE, EA, I". So, I went back and added the two new fields to the header and to the database table and deleted and re-created my SSIS package.

In my package, in the Flat File Source, if I edit it and click the Preview button, everything looks OK, and the new data is in the new columns. But, if I edit the OLE DB Destination and Preview it, the new fields are still back in my old last column, and the new columns are NULLs! I've tried deleting and recreating all the components of the package, but can't find what I'm doing wrong. Can anyone point me in the right direction???


Quick thought, if the source structure is changed, the whole data pipeline (data flow path) has to be updated, including the destination definition.
Cool
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60441 Visits: 13297
Eirikur Eiriksson (9/2/2014)

Quick thought, if the source structure is changed, the whole data pipeline (data flow path) has to be updated, including the destination definition.
Cool


And even more important, the source definition.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39367 Visits: 19437
Koen Verbeeck (9/3/2014)
Eirikur Eiriksson (9/2/2014)

Quick thought, if the source structure is changed, the whole data pipeline (data flow path) has to be updated, including the destination definition.
Cool


And even more important, the source definition.


Missing the obviousw00t
Cool
ppritts
ppritts
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 5
Thanks for the replies, but I DID recreate the source & destinations, including the Connection Managers. Then, when it was still not working, I deleted the whole package and started over, but with the same results!

Is there something I have to do in SQL Server after adding fields to the destination table to jog it into realizing that what used to come into the last field should get split into three fields?
ZZartin
ZZartin
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5984 Visits: 10172
This might be a stupid question but are you using text qualifiers in your input file? If so is the input file formatted properly for those last few fields?
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