Using Variables in Script Task in SSIS

Question

I am using a SSIS package with an Execute SQL Task connected to Script Task. The values from Execute SQL Task are stored in a variable and in the Script Task I use the following code to give XML file.

Public Sub Main()
    If My.Computer.FileSystem.FileExists("C:\SAMPLE.xml") Then
        Dts.TaskResult = ScriptResults.Failure
    Else
        My.Computer.FileSystem.WriteAllText("C:\SAMPLE.xml", Dts.Variables(0).Value.ToString, False)
        Dts.TaskResult = ScriptResults.Success
        ''
    End If
End Sub

I don't want to hardcode the path to XML. So I created two new variables FileName and FilePath with package as scope. How do I edit my VB.Net code to actually use these two variables.I tried this but did not work:

    Sub Main()
    If My.Computer.FileSystem.FileExists(Dts.Variables("FileDest").Value.ToString())        Else
        My.Computer.FileSystem.WriteAllText(Dts.Variables("FileDest").Value.ToString(), Dts.Variables(0).Value.ToString, False)Dts.TaskResult = ScriptResults.Success
   ''
    End If
End Sub

Ideally I want to use two variables one for name and one for path but when I tried with a single variable which combines both, it didn't work.

Edmund Schweppe Edmund Schweppe · Accepted Answer · 2012-11-15T23:41:54

I suspect that part of your problem is using a numeric index into the Variables collection. Once you added the FileDest variable, there's no guarantee that whatever variable was being used to store the Execute SQL Task results is still the zeroeth one in the collection.

Assuming the variable with the Execute SQL Task results is named XmlQueryResults, the following should do the trick:

Public Sub Main()
    Dim filePath As String = Dts.Variables("FileDest").Value
    Dim xmlToWrite As String = Dts.Variables("XmlQueryResults").Value
    If My.Computer.FileSystem.FileExists(filePath) Then
        Dts.TaskResult = ScriptResults.Failure
    Else
        My.Computer.FileSystem.WriteAllText(filePath, xmlToWrite, False)
        Dts.TaskResult = ScriptResults.Success
    End If
End Sub

(Don't forget to add the FileDest variable to the ReadOnlyVariables in the Script Task Editor script tab.)

Using Variables in Script Task in SSIS

1 Answers