Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS won't "spread" data into new fields Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2014 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:22 AM
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???
Post #1609792
Posted Tuesday, September 2, 2014 2:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,394, Visits: 6,636
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.
Post #1609812
Posted Wednesday, September 3, 2014 12:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
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.


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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1609881
Posted Wednesday, September 3, 2014 2:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,394, Visits: 6,636
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.


And even more important, the source definition.


Missing the obvious
Post #1609904
Posted Thursday, September 4, 2014 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:22 AM
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?
Post #1610530
Posted Thursday, September 4, 2014 8:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 364, Visits: 1,176
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?
Post #1610543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse