Click here to monitor SSC
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
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6715 Visits: 17701
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
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: 16447 Visits: 13202
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6715 Visits: 17701
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
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 7431
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