September 2, 2014 at 2:24 pm
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???
September 2, 2014 at 2:57 pm
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.
😎
September 3, 2014 at 12:20 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2014 at 2:15 am
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:w00t:
😎
September 4, 2014 at 7:56 am
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?
September 4, 2014 at 8:19 am
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?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply