Hello, I have a corrupt Flat File and I need to write a SSIS Script Component to Replace LF to CR LF in my flat file. Any idea how to do it? ThanksKajoo

do you want to do in whole file data? if yes, it is better to use Script Task in data flow to do this, you can use StreamReader and StreamWriter .net class to read the file and replace LF with CRLF and then save it back.http://www.rad.pasfu.com

Hello Reza, This would be my first time using the Script component. Can you please guide me on how to use it...ThanksKajoo

This would be my first time using the Script component. Can you please guide me on how to use it...Thanks this is sample c# code for reading and writing data from file: http://www.codekeep.net/snippets/801e1ebb-db64-492b-a543-dfabf0b176c6.aspx you should do same, but only with Replace method as you need, in a SCRIPT TASK's main() methodhttp://www.rad.pasfu.com

Reza, Thanks again! I am using SSIS 2005...I don't think I can use C#...2005 works with VB script..Kajoo

this link shows how to work with streamreader and streamwriter in VB.NET: http://msdn.microsoft.com/en-us/library/aa903247%28VS.71%29.aspxhttp://www.rad.pasfu.com

Thank you Reza! I assume I will be using a similar script as such to read the text file line by line...I am really ignorant to VB scripting since I have not used it before. Now I unerstand that I have to use a Replace to change the Row Delimiters...____ flat files may have other issues, I wanted to make the script rowbust to resolve other problems as it processes them...for example, removing blank lines and spaces. Can you please assist me on where I need to alter the script: Sub ReadTextLinesFromFile() Dim file As New System.IO.StreamReader("c:\test.txt") Dim oneLine As String oneLine = file.ReadLine() Replace ? CR with CR LF End While file.Close() End Sub --Also, I have found another script that replaces all the double CrLf by a single CrLfDim length As Long..and I wanted to incorporate them together so it would resolve other problems if they exist. Dim length As Long While length <> fileText.Length length = fileText.Length fileText = fileText.Replace(Environment.NewLine & Environment.NewLine, Environment.NewLine) End While Kajoo

this is the whole code you need in main() method: Public Sub Main() Dim file As New System.IO.StreamReader("c:\test.txt") Dim data As String data = file.ReadToEnd().Replace(vbCr, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter("c:\test.txt", False) writer.Write(data) writer.Flush() writer.Close() Dts.TaskResult = ScriptResults.Success End Sub http://www.rad.pasfu.com

the code above replace CR with CRLF if you want to replace LF with CRLF, use this code: Public Sub Main() Dim file As New System.IO.StreamReader("c:\test.txt") Dim data As String data = file.ReadToEnd().Replace(vbLf, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter("c:\test.txt", False) writer.Write(data) writer.Flush() writer.Close() Dts.TaskResult = ScriptResults.Success End Sub http://www.rad.pasfu.com

Thank you Reza! Now is it possible to incorporate both into one script? Thanks~Kajoo

this is all-in-one code : Public Sub Main() Dim pk As New Package Dim file As New System.IO.StreamReader("c:\test.txt") Dim data As String data = file.ReadToEnd() data = data.Replace(vbCrLf, vbLf) data = data.Replace(vbCr, vbLf) data = data.Replace(vbLf, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter("c:\test.txt", False) writer.Write(data) writer.Flush() writer.Close() Dts.TaskResult = ScriptResults.Success End Sub http://www.rad.pasfu.com

The script works when I run it alone...however, I am not able to set it up in a Foreach Loop container. Is it possible to use the script componet in a Foreach Loop. Because I am doing so and I keep getting an error. I have a declared a variable called str_SourcePath...and in the script expression I have used it as a readonlyvariable as such @[User::str_SourcePath] but when I run it the variable cannot be found...this is the error that I get: Error: Failed to lock variable "O:\Files\Processed Files\" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". Does that have to be set int the script? Public Sub Main() Dim pk As New Package Dim file As New System.IO.StreamReader("str_SourcePath") Dim data As String data = file.ReadToEnd() data = data.Replace(vbCrLf, vbLf) data = data.Replace(vbCr, vbLf) data = data.Replace(vbLf, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter("str_SourcePath", False) writer.Write(data) writer.Flush() writer.Close() Dts.TaskResult = Dts.Results.Success End Sub Thanks Kajoo

you should set @[User::str_SourcePath] as ReadonlyVariables in script task. and also use it in script like this: Dts.Variables("User::str_SourcePath").Value.ToString()http://www.rad.pasfu.com

I'm sorry for my ingnorance! I have the variable @[User::str_SourcePath] set up and I tried to alter the script and run it but I keep getting an error. I assume that it is because of the way I have altered the script...please verify if I am doing it correctly Public Sub Main() Dim pk As New Package Dim file As New System.IO.StreamReader("@[User::str_SourcePath]") Dim data As String data = file.ReadToEnd() data = data.Replace(vbCrLf, vbLf) data = data.Replace(vbCr, vbLf) data = data.Replace(vbLf, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter("@[User::str_SourcePath]", False) writer.Write(data) writer.Flush() writer.Close() Dts.Variables("@[User::str_SourcePath]").Value.ToString() Dts.TaskResult = Dts.Results.Success End Sub Kajoo

your script is not correct. you didn't used variable with correct syntax. this one is correct: Public Sub Main() Dim pk As New Package Dim file As New System.IO.StreamReader(Dts.Variables("@[User::str_SourcePath]").Value.ToString()) Dim data As String data = file.ReadToEnd() data = data.Replace(vbCrLf, vbLf) data = data.Replace(vbCr, vbLf) data = data.Replace(vbLf, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter(Dts.Variables("@[User::str_SourcePath]").Value.ToString(), False) writer.Write(data) writer.Flush() writer.Close() Dts.TaskResult = Dts.Results.Success End Sub also as your error mentioned, your variable value is "O:\ATMFiles\Processed Files\" , you will got an error with this value again, because you should have a FILE path in this variable not directory path. if you have a directory you should loop through files in directory first.http://www.rad.pasfu.com

Thank you again Reza! Another question. Now, is it possible to add another feature to also check to see every column has the correct amount of Delimiters. The code above takes care of the problem with the row carriage returns...however, I have two other files that have a different issue. The package fails at a certain row each time. I appears that the column level delimiters may have some issues. Is it possible to fix the column delimiters with VB as well? ThanksKajoo

you can check number of column delimiters in each row by .NET code. do you mean this? and note that you can not fix these rows, just you can find them by validating number of column delimiters. then only action you can do for them is that you can remove them from this file and add to another file for example BADROWS.csv file. but note that you can do this with error output in flat file source too. I think error output setting is simpler and more reliable. just set error output and save results in another flat file destination. http://www.rad.pasfu.com

Yeah! I set one up and set all the column in the Flat File Source Editor to redirct on erro...but it dosen't send/redirect any of the bad rows to the Flat File Source Error Ouput...Thanks! Kajoo

Yeah! I set one up and set all the column in the Flat File Source Editor to redirct on erro...but it dosen't send/redirect any of the bad rows to the Flat File Source Error Ouput...Thanks! Kajoo you mean that you receive error ? and package will fail? if yes, you have no way except validating it in script task. let me know if you need more details on scripthttp://www.rad.pasfu.com

Correct! So how would go to create a script task for this problem....again, I am very very new to VB so I know almost zip! Thanks!Kajoo

this is whole code for validating rowdelimiters and column delimiters together: Public Sub Main() 'validate RowDelimiter Dim file As New System.IO.StreamReader(Dts.Variables("@[User::str_SourcePath]").Value.ToString()) Dim data As String data = file.ReadToEnd() data = data.Replace(vbCrLf, vbLf) data = data.Replace(vbCr, vbLf) data = data.Replace(vbLf, vbCrLf) file.Close() Dim writer As New System.IO.StreamWriter(Dts.Variables("@[User::str_SourcePath]").Value.ToString(), False) writer.Write(data) writer.Flush() writer.Close() 'validate ColumnDelimiter Dim delimiterCharacter As String = "," Dim numberOfColumns As Integer = 10 file = New System.IO.StreamReader(Dts.Variables("@[User::str_SourcePath]").Value.ToString()) Dim ValidRowsWriter As New System.IO.StreamWriter("c:\test\validRows.txt", False) Dim InvalidRowsWriter As New System.IO.StreamWriter("c:\test\InvalidRows.txt", False) While (file.EndOfStream) data = file.ReadLine() Dim rowArray As String() = data.Split(Convert.ToChar(delimiterCharacter)) If (rowArray.Length = 10) Then 'valid rows ValidRowsWriter.WriteLine(data) Else 'invalid rows InvalidRowsWriter.WriteLine(InvalidRowsWriter) End If End While file.Close() ValidRowsWriter.Flush() ValidRowsWriter.Close() InvalidRowsWriter.Flush() InvalidRowsWriter.Close() Dts.TaskResult = Dts.Results.Success End Sub Note that I inserted good rows to a file named ValidRows.txt . and bad rows to a file named InvalidRows.txt . you can change these file addresses with values from variables. just note that you should use ValidRows.txt in your data flow.http://www.rad.pasfu.com

Thank you again Reza! I tried the script and ran it...it doesn't error out but the two files that it creats (validRows.txt and InvalidRows.txt) are not working...meaning there two files were empty! does that means it didn't find an issue related to that? Kajoo

at least one of these files should have rows inside after running code above! what is size of your file? could you mail one of them for me?http://www.rad.pasfu.com