March 15, 2005 at 7:56 am
Hi,
I have a text file being downloaded from the mainframe during a certain timeframe and within that spreadsheet there are double quotes and commas. Those double quotes are preventing me from automatically load this into the table. So I am trying to replace doublequotes in a text file with no value or null. Here is my DTS script but I am getting an expected ')' error message:
Function Main()
DTSDestination("Col009") = Replace(DTSSource(""CHCK""), """", "")
DTSDestination("Col008") = Replace(DTSSource(""ASTT""), """", "")
DTSDestination("Col007") = Replace(DTSSource(""OTRT""), """", "")
DTSDestination("Col006") = Replace(DTSSource(""HRLY""), """", "")
DTSDestination("Col005") = Replace(DTSSource(""WRK#""), """", "")
DTSDestination("Col004") = Replace(DTSSource(""KEY""), """", "")
DTSDestination("Col003") = Replace(DTSSource(""FRST""), """", "")
DTSDestination("Col002") = Replace(DTSSource(""LAST""), """", "")
DTSDestination("Col001") = Replace(DTSSource(""EEID""), """", "")
Main = DTSTransformStat_OK
End Function
Thanks
March 15, 2005 at 8:28 am
Try getting rid of the double quotes inside the DTSSource(). So, the code would be:
Function Main()
DTSDestination("Col009") = Replace(DTSSource("CHCK"), """", "")
DTSDestination("Col008") = Replace(DTSSource("ASTT"), """", "")
DTSDestination("Col007") = Replace(DTSSource("OTRT"), """", "")
DTSDestination("Col006") = Replace(DTSSource("HRLY"), """", "")
DTSDestination("Col005") = Replace(DTSSource("WRK#"), """", "")
DTSDestination("Col004") = Replace(DTSSource("KEY"), """", "")
DTSDestination("Col003") = Replace(DTSSource("FRST"), """", "")
DTSDestination("Col002") = Replace(DTSSource("LAST"), """", "")
DTSDestination("Col001") = Replace(DTSSource("EEID"), """", "")
Main = DTSTransformStat_OK
End Function
March 15, 2005 at 8:35 am
Unfortunately, the double quotes are coming from the flat file. There are certain columns (character fields) that are coming across from the mainframe with those double quotes. So when DTS sees those, it also puts double quotes around the values. I was hoping that the replace function would catch those and eliminate them before the load.
Example:
"EEID","LAST","FRST","KEY","WRK#",
"HRLY","OTRT","ASTT","CHCK"
March 15, 2005 at 9:11 am
OK, I understand now. The column names actually have quotes around them.
OK, there are 2 options:
1. The simplest is to put an extra double quote around the column name (for a total of 3 on either side of the field name). For example, DTSSource("""EEID"""). That worked for me.
2. Another option would be to change your source connection in the package. When you define the connection to the file, you can skip the first row (which has the field names). Then, you can just use the Col001, Col002, etc. instead of the actual column headers.
March 15, 2005 at 9:46 am
Yes, that worked.
Thank you very much for your help...
Raj
March 15, 2005 at 10:03 am
You're very welcome. My pleasure. You're the first person I was actually able to help on this site so far (or at least confirmed). And hopefully not the last! :> I'm discovering that this site is pretty fantastic.
March 16, 2005 at 9:54 am
Hi
If you want to use the original headers you can make an .ini-file in notepad. Just write these lines:
[Source]
ColumnNames=EEID,LAST,FRST,KEY,WRK#,HRLY,OTRT,ASTT,CHCK
NumberOfColumns=9
Save the file as source.ini (for example...)
Use the Dynamic Properties Task and add the 2 values in the .ini-file to your sourcefile. Be sure to run the Dynamic Properties Task before you start the transformation.
//Pelle
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply