February 14, 2008 at 7:30 am
I got an email from one of our developers this morning regarding the below. Any ideas?
SLABS_FM_EXRACT is an ssis package that used to be a dts package on 2000.
The vendor who receives the output of the SLABS_FM_EXTRACT encountered a problem importing some of the output files. It seems that when a string, which is enclosed with double quotes, itself contains double quotes, then an error occurs. Let me give an extract of line 21 of AM88P.OUTPUT.SITE.NEW:
"LAND & BLG. S-12435 IS LEASED TO "GREATER NEW ORLEANS ASSOC FOR RETARDED CITIZENS" FOR A SHELTERED WORKSHOP. BESE STILL OWNS."
When the import program encounters the double quote right before 'GREATER', it assumes that is the end of the string, and so is expecting a comma to delimit this field from the next one. Instead, it sees a 'G' and blows up.
This wasn't a problem in DTS, because DTS automatically escaped double quotes found in a string, like so:
"LAND & BLG. S-12435 IS LEASED TO ""GREATER NEW ORLEANS ASSOC FOR RETARDED CITIZENS"" FOR A SHELTERED WORKSHOP. BESE STILL OWNS."
So is there any setting in SQL Server 2005 that directs an SSIS package to escape certain characters? That would be the easiest way to address this.
February 14, 2008 at 7:50 am
We had the exact same issue when converting our dts packages. Had to add a derived column in the data flow task that replaced a single quote with two quotes. Here's the syntax for converting column BrandName:
Derived Column Name:
OutBrandName
Expression:
REPLACE(BrandName,"\"","\"\"")
Hope that helps.
February 14, 2008 at 11:55 am
I'm playing middle man here. I sent him your response and got this back. any ideas?
thanks
The Derived Column Transformation Editor doesn't seem to allow for different column names in 'Derived Column Name', Derived Column', and 'Expression', so I don't know how your respondent was able to do it. Perhaps I don't know enough about how all this works to get it done right, but the online help isn't much help.
February 14, 2008 at 12:50 pm
In the data flow tab of our package we have 3 tasks.
A Data flow source (OLE DB Source that executes a stored proc), a data flow transformation (derived column) and a data flow destination (flat file). When you open the derived column transformation do you see the column names returned by your data flow source (top left corner)? Drag the column name down into the expression column and use the Replace function posted earlier. You should be able to type anything in the derived column name column. Keep in mind that you'll have to change the data flow destination mapping to use the new derived column name and not the original column name.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply