SSIS Script Task StreamWriter error

kcmoore
kcmoore used Ask the Experts™
on
So I am looping through a file. I created, what I think is a dynamic variable for File Names.
(I'm going to a location for the list of files, but only processing the first one in the list. Then at the end I move the file so it won't be processed again when the For Each Loop contain runs the script task again)
I have debug on, and it fails when executing the : " Dim objStreamWriter As New StreamWriter(strUpdatedFile)" line.

A. How can I throw a catch error or something that will give me a descriptive error message? (if can be done)
B. Or is it something else that I am not seeing?
Public Class ScriptMain
    Public Sub Main()
        Try
            'GET SSIS Variables
            Dim strFolder As String = "\\OriginalFolder\"
            Dim strUpdatedFolder As String = "\\UpdatedFolder\"
            Dim strMoveFolder As String = strFolder + "Imported\"
            Dim files() As String = System.IO.Directory.GetFiles(strFolder, "*.*")
            Dim strOriginalFile As String = files.GetValue(0).ToString   'returns the value of first file name list
            Dim strUpdatedFile As String = Replace(strOriginalFile, "\\OriginalFolder\", strUpdatedFolder)
            Dim fCreateTime As Date = File.GetCreationTime(files(0))
 
            'strOriginalFile = Replace(strOriginalFile, "\\OriginalFolder\", "")
            strUpdatedFile = Replace(strOriginalFile, ".DAT", "_") + fCreateTime.ToString + ".txt"
 
 
            'INITIALIZE Objects and Variables
            Dim objStreamReader As New StreamReader(strOriginalFile)
            Dim objStreamWriter As New StreamWriter(strUpdatedFile)
            Dim OriginalRow As String = ""
            Dim UpdatedRow As String = ""
 
 
            'Replaces carrot delimeter with a pipe delimeter and saves as a new file
            Do While Not objStreamReader.EndOfStream
                OriginalRow = objStreamReader.ReadLine
                UpdatedRow = Replace(OriginalRow, "^", "|") ' UPDATE HERE!
                objStreamWriter.WriteLine(UpdatedRow)
            Loop
 
 
            File.Move(strOriginalFile, strMoveFolder)
 
            'CLOSE objects and variables
            objStreamReader.Close()
            objStreamReader.Dispose()
            objStreamWriter.Flush()
            objStreamWriter.Close()
            objStreamWriter.Dispose()
 
            'GENERATE an Information Event
            Dts.TaskResult = Dts.Results.Success
            'Dts.Events.FireInformation(10000, "Script", "Texfile updated.", "", 0, False)
 
        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
            Dts.Events.FireError(20000, "Script", "Error description.", "", 0)
        End Try
    End Sub
End Class

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
add a msgbox strUpdatedFile
before the error line to confirm if you are going to the correct path + filename

helped?
Regards,
Pedro
www.pedrocgd.blogspot.com

Author

Commented:
Definitely helped ensure the file path and name are correct.
But I just don't get why it is erroring out on that step...?

Author

Commented:
It bombs while executing the Dim objStreamWriter

            'INITIALIZE Objects and Variables
            Dim objStreamReader As New StreamReader(strOriginalFile)
            Dim objStreamWriter As New StreamWriter(strUpdatedFile)
            Dim OriginalRow As String = ""
            Dim UpdatedRow As String = ""
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
you have thje import system.IO ?

Author

Commented:
I'm completely new to VB....
Other than what you see in the original code posted, How would I find out?

Commented:
check this example:

Check if you have the
Imports System.IO

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO ' ADDED
 
Public Class ScriptMain
    Public Sub Main()
        Try
            'Open a file for write
            Dim filename As String = Dts.Variables("uvDestFile").Value.ToString
            Dim intRows As Integer = CInt(Dts.Variables("uvRows").Value.ToString)
 
            'Get a StreamReader class that can be used to read the file
            Dim objStreamWriter As StreamWriter
            objStreamWriter = File.AppendText(filename)
 
            'Append the the end of the string the rows
            objStreamWriter.WriteLine("FTR | ROW COUNT: " & intRows)
 
            'Close the stream
            objStreamWriter.Close()
 
            'GENERATE AN Information Event
            Dts.TaskResult = Dts.Results.Success
            Dts.Events.FireInformation(10000, "Script", "Footer added to the file!", "", 0, False)
 
        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
            Dts.Events.FireError(20000, "Script", "Error description", "", 0)
        End Try
    End Sub
End Class

Open in new window

Author

Commented:
I have the same Imports you listed.
I was able to make it work if I removed the file name change
            strUpdatedFile = Replace(strOriginalFile, ".DAT", "_") + fCreateTime.ToString + ".txt"
But the file was blank and not named correctly. I have no issue renaming the file after it is copied over.

So the original way, doesn't write just errors. And the aforementioned way writes, but blank...

I've been stuck on this since yesterday. : (
 

Commented:
send me the package and a sample file to check it in detail!
regards,
Pedro

Author

Commented:
The enitire SSIS package?

Author

Commented:
Ok. I changed the extensions from .dtsx and .dat to .txt so it would allow me to attach them.
R-TE012193-XX-170-095133.txt
LabCorp-ETL.txt

Commented:
if you can it would be better

Author

Commented:
Is it possible the the length of the strUpdatedFile is too long for the StreamWriter?

Commented:
I already made an example similar to your issue...
donwload and see if the package I made here works:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24135629.html

Also, try replace the filesystem location of your txt file to a more simple path with a drive letter like c:\

Give feedback!
pedro

Author

Commented:
I read that string yesterday. Very well done.

I looked at the package and it just does a reader and not a writer. Maybe me being a noob is not allowing me to see what you're trying to tell me.
I'll try a shorter file path and see what happens.

Also, when I commented out the line that changes ".DAT" to _ + CreateTime + ".txt",
that is when the script task runs through properly, with one exception, the destination file is blank... Here is the latest script piece...

 'GET SSIS Variables
            Dim strFolder As String = "\\HSPGPWS\Labcorp\"
            Dim strUpdatedFolder As String = "\\SVR-07\Clients_Data\CLIENTS\PSS_WORLD_MEDICAL_INC\DATA\LabCorp\Pre_Raw\"
            Dim strMoveFolder As String = strFolder + "Imported\"
            Dim files() As String = System.IO.Directory.GetFiles(strFolder, "*.*")
            Dim strOriginalFile As String = files.GetValue(0).ToString   'returns the value of first file name list
            Dim strUpdatedFile As String = Replace(strOriginalFile, "\\HSPGPWS\Labcorp\", strUpdatedFolder)
            Dim fCreateTime As Date = File.GetCreationTime(files(0))

            MsgBox(strOriginalFile)
            MsgBox(strUpdatedFile)
            'strUpdatedFile = Replace(strUpdatedFile, ".DAT", "_") + fCreateTime.ToString + ".txt"
            'MsgBox(strUpdatedFile)

            'INITIALIZE Objects and Variables
            Dim objStreamReader As New StreamReader(strOriginalFile)
            Dim objStreamWriter As New StreamWriter(strUpdatedFile)
            Dim OriginalRow As String = ""
            Dim UpdatedRow As String = ""
            Dim FinalUpdatedRow As String = ""


            'Replaces carrot delimeter with a pipe delimeter and saves as a new file
            Do While Not objStreamReader.EndOfStream
                OriginalRow = objStreamReader.ReadLine
                UpdatedRow = Replace(OriginalRow, "^", "|") ' UPDATE HERE!
                FinalUpdatedRow = Replace(UpdatedRow, "||", "|NULL|") ' UPDATE HERE!
                objStreamWriter.WriteLine(FinalUpdatedRow)
            Loop

            'Move the file processed to the Imported folder so it won't be processed again
            File.Move(strOriginalFile, strMoveFolder)

            'CLOSE objects and variables
            objStreamReader.Close()
            objStreamReader.Dispose()
            objStreamWriter.Flush()
            objStreamWriter.Close()
            objStreamWriter.Dispose()

Commented:
The example has a reader and a writer... see attached image:
SSIS-ScriptCode.JPG

Author

Commented:
Ahh...didn't open that one, just the package.
That is what i used to "create" what I have so far.
Than I manipulated and added some things to make the file name dynamic.
I'm working on it for a few more today..will let you know if these things have helped.

Commented:
Send me an email because I'll not be here near tonight!
See my profile!
regards,
Pedro

Author

Commented:
Pedro,

I got everything working with exception of one thing, the File.Move statements at the end. Here is the latest...
Can wait until Monday. I'm just happy I was able to get it to do what I needed.
If you can please provide any thoughts on the file move failing, I would greatly appreciate it.


--------------------------------------------------------------------
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO ' ADDED

Public Class ScriptMain
    Public Sub Main()
        Try
            'GET SSIS Variables
            Dim strFolder As String = "\\HSPGPWS\Labcorp\"
            Dim strUpdatedFolder As String = "\\HSPGPWS\Labcorp\Pre_Raw\"
            Dim strMoveOFolder As String = "\\HSPGPWS\Labcorp\Imported\"
            Dim strMoveUFolder As String = "\\SVR-07\Clients_Data\CLIENTS\PSS_WORLD_MEDICAL_INC\DATA\LabCorp\Pre_Raw\"
            Dim files() As String = System.IO.Directory.GetFiles(strFolder, "*.*")
            Dim strOriginalFile As String = files.GetValue(0).ToString   'returns the value of first file name list
            Dim strUpdatedFile As String = Replace(strOriginalFile, "\\HSPGPWS\Labcorp\", strUpdatedFolder)
            Dim fCreateTime As Date = File.GetCreationTime(files(0))


            'INITIALIZE Objects and Variables
            Dim objStreamReader As New StreamReader(strOriginalFile)
            Dim objStreamWriter As New StreamWriter(strUpdatedFile)
            Dim OriginalRow As String = ""
            Dim UpdatedRow As String = ""
            Dim FinalUpdatedRow As String = ""


            'Replaces carrot delimeter with a pipe delimeter and saves as a new file
            Do While Not objStreamReader.EndOfStream
                OriginalRow = objStreamReader.ReadLine
                UpdatedRow = Replace(OriginalRow, "^", "|") ' UPDATE HERE!
                FinalUpdatedRow = Replace(UpdatedRow, "||", "|NULL|") ' UPDATE HERE!
                objStreamWriter.WriteLine(FinalUpdatedRow)
            Loop


            'CLOSE objects and variables
            objStreamReader.Close()
            objStreamReader.Dispose()
            objStreamWriter.Flush()
            objStreamWriter.Close()
            objStreamWriter.Dispose()

            'MsgBox(strFolder)
            'MsgBox(strUpdatedFolder)
            'MsgBox(strMoveOFolder)
            'MsgBox(strMoveUFolder)
            'MsgBox(strOriginalFile)
            'MsgBox(strUpdatedFile)

            strUpdatedFile = Replace(strUpdatedFile, ".DAT", "_") + fCreateTime.ToString + ".txt"
            'MsgBox(strUpdatedFile)


            'Move the file processed to the Imported folder so it won't be processed again
            'File.Move(strOriginalFile, strMoveOFolder)
            'File.Move(strUpdatedFile, strMoveUFolder)

            'GENERATE an Information Event
            Dts.TaskResult = Dts.Results.Success
            'Dts.Events.FireInformation(10000, "Script", "Texfile updated.", "", 0, False)

        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
            Dts.Events.FireError(20000, "Script", "Error description.", "", 0)
        End Try
    End Sub
End Class
--------------------------------------------------------------------

Commented:
I made a mistake... in spite of setting th equestion as resolved setted as close without answer...
Check it and correct please!

Commented:
Sorry for the late answer... I'm on vacations!
The user made a mistake... I gave the answer but maybe he made a mistake that in spite set the question answered with 500 points set it with no points rewarded.
Let me know if you have some doubts.


And kcmoore, could give you some feedback!?
Best regards to all!
pedro

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial