April 20, 2012 at 7:41 am
Hi All
Just a quick one and its probably simple, but I think I have a case of the "its Friday afternoon and its home time in 20 minutes" (just been one of them weeks).
We have just received a text file from SalesForce which contains a list of Schools in the US, putting in the text qualifer of " into the flat file source removes the " character from both ends of all the columns bar 1, so I went in to do a derived column with the replace function trying to do REPLACE(NAME, ""","") but as " is the text qualifer in SSIS the expression errored so I reverted to a T-SQL step instead once the data is imported into the DB REPLACE(NAME,'"','') which does the job for the merge statement to then fire and update whatevers nessesary.
The question is how do you do """ in SSIS to replace a set of speach marks should they appear in a string?
Thanks
April 20, 2012 at 9:53 am
The literal of double-quote in an SSIS expression is \" (backslash double-quote) as double-quote is reserved. Is that what you're after?
E.g. REPLACE([expression],"\"',"")
But why don't you just explicitly set the flat file source to have double quote as a text qualifier in the flat file connection? Maybe I'm missing something...
April 20, 2012 at 10:07 am
So ... what's so special about that one column?
You're right about Friday afternoon ... same sentiment here.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 23, 2012 at 1:53 am
Howard, the flat file source does have " as the text qualifier its just that one column which it wont remove the " from. Also thanks for the expression much appreciated
What I think is special about this column is that there is multiple , inbetween " so I am thinking that SSIS is not able to read the full text string within the file as the , within the text string is pushing data out a column or two. Think I will have to pass it back to the data provider (internal team) to do some data cleansing as there are a number of data issues on other columns with the export.
April 23, 2012 at 2:05 am
Can you get them to change the column delimiter to a pipe (|) as part of that? It might just solve these issues.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 23, 2012 at 2:07 am
Its a possibility but its extracted from SalesForce using ApexDataLoader and I have yet to find a way to change it from comma delimited to something else, but will keep digging.
We are responsible to get the data out of SalesForce, but its SalesForce who is responsible for the data quality which they have confirmed is a bit lacking and needs checking.
April 23, 2012 at 2:15 am
anthony.green (4/23/2012)
Its a possibility but its extracted from SalesForce using ApexDataLoader and I have yet to find a way to change it from comma delimited to something else, but will keep digging.We are responsible to get the data out of SalesForce, but its SalesForce who is responsible for the data quality which they have confirmed is a bit lacking and needs checking.
Ah, OK. You might like to vote for a change to ApexDataLoader here.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 23, 2012 at 2:22 am
Thanks Phil, I will add a comment once I have done my morning checks.
June 6, 2014 at 8:02 am
this post was a little old ... but this is a solution I came up with worked
Replace('ABC"EF',CHAR(34),'D')
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply