Why am I getting object reference not set error on script task connector?

2
votes

I have an SSIS package (SQL Server 2005) that loops through a bunch of flat files in a folder. I need to wait until the source application has finished writing the file before I can open it in my flat file import task.

I have a For Each loop container and within it a script task to execute before the Data Flow Task.

When I try to create the success connector between the Script Task and the Data Flow Task I get this error:

Could not create connector. Object reference not set to an instance of an object.

I get that something is being set to nothing, but I can't see it. I have DelayValidation set to true on both the Script Task and the Data Flow Task. What else am I missing?

I'm a C# guy so maybe I'm missing something obvious in the VB. Here's the script I poached from the interwebs:

Public Sub Main()
    Dim strFileName As String = CType(Dts.Variables("FileName").Value, String)
    Dim objFS As System.IO.FileStream
    Dim bolFinished As Boolean = False

    Do
        Try
            objFS = System.IO.File.Open(strFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.None)
            bolFinished = True
            objFS.Close()
        Catch ex As Exception
            System.Threading.Thread.Sleep(1000)
        End Try
    Loop

    If bolFinished Then
        Dts.TaskResult = Dts.Results.Success
    Else
        Dts.TaskResult = Dts.Results.Failure
    End If
End Sub
2
sql-server-2005ssis

2 Answers

3
votes

Milen k is more than right. It looks like you have an infinite loop which is opening a file several times until it breaks down.

You could change your code with the below suggested code. This will help you to get out of the infinite loop.

Your current code:

Do
    Try
        objFS = System.IO.File.Open(strFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.None)
        bolFinished = True
        objFS.Close()
    Catch ex As Exception
        System.Threading.Thread.Sleep(1000)
    End Try
Loop

Suggested code:

Do While(true)
    Try
        objFS = System.IO.File.Open(strFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.None)
        bolFinished = True
        objFS.Close()
        Exit Do
    Catch ex As Exception
        System.Threading.Thread.Sleep(1000)
    End Try
Loop
1
votes

Make sure that you have created a Flat File Source for your Data Flow task. If you do not have an existing one, create a temporary one which act as a place-holder for the file paths you feed it through the For Each loop.

From what I understand, you should be passing the path to each file that you will be importing to your Flat File Connection. This can easily be done by adding the variable generated in your For Each loop as an expression in the Expression property of your Flat File Connection.

UPDATE:

You need to set a condition in your Do ... Loop. For example: Loop While Not bolFinished. Look at this document for more information.