March 29, 2006 at 1:46 pm
FYI: This question pertains to SSIS 2005 only, not SQL!
the incoming txt has some extra carriage returns. How can I tell the flat file Source component to just ignore or trim those....because the component fails when it comes across them.
Example, the txt file has:
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
----get rid of this blank line or else my component fails! ----
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
asdfsf,dfdfd,dfdfd,dfdf,dfdfd,fdfd
March 29, 2006 at 4:11 pm
Check this url out. Might help.
March 29, 2006 at 4:13 pm
Thanks, I've read that one before. Unfortunately my flat file source component won't even get that far if there are line returns...it fails. If I take the line returns out, it runs fine. So I need to somehow take those extra CRs out before somehow.
March 30, 2006 at 7:14 am
Here is some vbScript that I use to remove blank lines from a file. Just save this into a file with a vbs extension. I wrote everything with functions to make it more portable. Replace ("C:\MyFileWithBlanks.TXT")and ("C:\MyFileWithoutBlanks.TXT") with whatever.
option explicit
Dim sFileText
dim RetVal
sFileText = ReadTextFile("C:\MyFileWithBlanks.TXT")
if sFileText <> "" then
sFileText = RemoveBlankLinesFromText(sFileText)
if sFileText <> "" then
RetVal = WriteTextFile("C:\MyFileWithoutBlanks.TXT", sFileText, 2)
end if
end if
wscript.quit
function ReadTextFile(byval sFileName)
'By Rick Carisse
'This function reads a text file and returns the contents
'Returns "" if there is any error
on error resume next
Const ForReading = 1
dim objFso
dim objFile
Dim sFileText 'as string
dim bContinue
bContinue = true
sFileText = ""
Set objFso = CreateObject("Scripting.FileSystemObject")
if err.number = 0 then
if objFso.FileExists(sFileName) then
Set objFile = objFSO.OpenTextFile (sFileName, ForReading)
if err.number = 0 then
sFileText = objFile.ReadAll
objFile.Close
if err.number = 62 then
err.clear
else
if err.number <> 0 then
bContinue = false
end if
end if
if bContinue = true then
sFileText = trim(sFileText)
end if
end if
end if
end if
Set objFile = nothing
set objFso = nothing
err.clear
ReadTextFile = sFileText
end function
function RemoveBlankLinesFromText(byval sTextString)
'By Rick Carisse
'This function removes any blank lines from a string
'Most useful when reading all of a text file that may contain blank lines
on error resume next
dim iLenNewLine
iLenNewLine = len(vbNewLine)
if sTextString <> "" then
'remove any blank lines
do until instr(1, sTextString, vbNewLine & vbNewline, 1) = 0
sTextString = replace(sTextString, vbNewLine & vbNewline, vbNewline, 1, -1, 1)
loop
if left(sTextString, iLenNewLine) = vbNewLine then
sTextString = replace(sTextString, vbNewLine, "", 1, 1, 1)
end if
if right(sTextString, iLenNewLine) = vbNewLine then
sTextString = left(sTextString, len(sTextString)-iLenNewLine)
end if
end if
RemoveBlankLinesFromText = sTextString
end function
Function WriteTextFile(byval sFullFileName, byval sWriteString, byval iWriteType)
'By Rick Carisse
'Writes to a text file returns 0 if successful
'Creates the file if it doesn't already exist
'Valid iWriteType values are:
'Const ForWriting = 2, ForAppending = 8
on error resume next
Dim objFso
Dim objFile
Dim RetVal
RetVal = 0
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFile = objFso.OpenTextFile (sFullFileName, iWriteType, true)
RetVal = err.number
if RetVal = 0 then
objFile.write (sWriteString)
objFile.close
RetVal = err.number
end if
set objFile = nothing
set objFso = nothing
err.clear
WriteTextFile = RetVal
end function
Rick
March 30, 2006 at 7:20 am
Thanks Rick. I wonder if th en I can fire your script off with a Script component in my SSIS project somehow.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply