September 19, 2017 at 5:39 am
Guys,
We have encountered an issue with an SSIS package that is running via SQL Agent Job. We have made some environment changes and I think part of that has caused the corruption. The details are below but I don't know what part of the upgrade has caused the issue or the best way to fix it.
We had a production environment with
LIVE server has SQL Agent job calling a package stored in REPORTS server MSDB. The package looks at REPORT server and LIVE2 server to extract data to a flat file on the SAN share. REPORT server gets updated with a copy of LIVE database overnight using backup and restore
We created a new UAT server where we have migrated the applications on LIVE to a new version including database schema changes - but not to the tables being accessed by the SSIS package.
We have then decomissioned the LIVE server and renamed UAT to be LIVE and added all the SQL Agent jobs from old live to new LIVE. When the Agent job runs the files are created but each field in the output rows are prefixed and suffixed with "_x003C_none_x003E_"
E.g.
_x003C_none_x003E_1357816/1_x003C_none_x003E_,_x003C_none_x003E_"Drancy Avenue 1-7 Odds; Walsall"_x003C_none_x003E_
instead of desired outcome
1357816/1,"Drancy Avenue 1-7 Odds; Walsall"
If I run the package manually from BIDS it is OK and as far as I know it was OK in the old environment. Can I fix this via the SQL Agent job or do I need to re-load the package to MSDB, or something else.
I know it is to do with the text delimiter but I would rather not mess with the package in production if I can help it.
September 19, 2017 at 5:07 pm
Check the workaround in these articles and see if it helps:
SSIS DTS Package Flatfiles with _x003C_none_x003E_
_x003C_none_x003E_ FlatFile Error in SSIS
Sue
September 22, 2017 at 10:05 am
This was a bug a few years ago that I am sure was fixed with an update to Data Tools (SQL Version not Visual Studio version). I went through it myself, I seem to remember is was a problem swapping between 32 bit and 64 bit machines in 2008. I would have to re-edit the package and enter the delimiter as <none>
September 22, 2017 at 10:29 am
tim.ffitch - Friday, September 22, 2017 10:05 AMThis was a bug a few years ago that I am sure was fixed with an update to Data Tools (SQL Version not Visual Studio version). I went through it myself, I seem to remember is was a problem swapping between 32 bit and 64 bit machines in 2008. I would have to re-edit the package and enter the delimiter as <none>
That is what the links I posted above addresses - setting the TextQualifier to none or a zero length string.
Sue
September 22, 2017 at 7:12 pm
Thanks guys,
I indeed edit the package by hand. i.e. view in code (rather than use the properties) and set the text qualifier to empty string. I just know that it is going to break when the next dev looks at it and the tooling defaults back to <none>
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply