SSIS Script Task to check for File Existance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SSIS Script Task to check for File Existance

Hi all,
Let me apologize in advance for what I know is a very basic question, but so far I’m not being terribly successful trying to find sample code to learn from. I’m trying to write (my first!) .Net Script in SSIS to check for the existance of a file (since that seems to be the only way to do it). I’ve created the Script Task, with the following settings:
PrecomplieScriptIntoBinaryCode = False (it complained at one point in time (possibly on validation))
EntryPoint = ScriptMain
ReadOnly Variables = ProductBuyListFileLocation, ProductBuyListFileName
ReadWrite Variable = FileFound Now the ReadOnly variables have the 2 components necessary for a file location, i.e.:
ProductBuyListFileLocation = ‘D:TestingProductInformationEnhancements’
ProductBuyListFileName = ‘buyList.xls’ So, I’m now trying to check the existance with the following .Net Script code:
——-code——–
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain ‘ The execution engine calls this method when the task executes.
‘ To access the object model, use the Dts object. Connections, variables, events,
‘ and logging features are available as static members of the Dts class.
‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
‘
‘ To open Code and Text Editor Help, press F1.
‘ To open Object Browser, press Ctrl+Alt+J. Public Sub Main()
‘
‘ Add your code here
‘
Dim FileLocation As String
Dim DTSVariables As Variables If System.IO.File.Exists((CStr(DTSVariables("ProductBuyListFileLocation").Value) + (CStr(DTSVariables("ProductBuyListFileLocation").Value)))) Then
DTSVariables("FileFound").Value = 1
Else
DTSVariables("FileFound").Value = 0
End If
Dts.TaskResult = Dts.Results.Success
End Sub End Class
——-code——– if fails with this message, which I’m not finding too helpful:
at ScriptTask_6c9d28f1bd3045739c4237bd588e3835.ScriptMain.Main() in dts://Scripts/ScriptTask_6c9d28f1bd3045739c4237bd588e3835/ScriptMain:line 28 Is it complaining about not being able to find/address my ReadOnly variable? If so, what am I doing wrong? TIA Panic, Chaos, Disorder … my work here is done –unknown
.. made some progress. I’m debugging, and when stepping through the .NEt script, I get to the end of the script, at End Main, and then .Net launches a ‘Find Source: _Startup’ window, seeking the location of a file:
ScriptTask_6c9d28f1bd3045739c4237bd588e3835_Startup I think that’s the route back to the SSIS package? Anyway, I continue debugging, return to Package, but clearly I haven’t set the variable, even though the line of code:
Dts.Variables("FileFound").Value = 0
was executed in the stepping through. I set the precedence for the script task to evaluate @FileFound ==0, and set the variable = 1 by default, so if the script correctrly runs, it SHOULD update the value to 0, and allow the precedence constraint to be met, and continue, but it does not – it finishes the script task successfully, and completes package. Am I being even more blonde here? Doesn’t Dts.Variables("FileFound").Value = 0 assign the value 0 to the variable? the latest code is:
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain ‘ The execution engine calls this method when the task executes.
‘ To access the object model, use the Dts object. Connections, variables, events,
‘ and logging features are available as static members of the Dts class.
‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
‘
‘ To open Code and Text Editor Help, press F1.
‘ To open Object Browser, press Ctrl+Alt+J. Public Sub Main()
‘
‘ Add your code here
‘
Dim FileLocation As String
‘Dim DTSVariables As Variables If System.IO.File.Exists((CStr(Dts.Variables("ProductBuyListFileLocation").Value) + (CStr(Dts.Variables("ProductBuyListFileName").Value)))) Then
Dts.Variables("FileFound").Value = 0
Else
Dts.Variables("FileFound").Value = 1
End If
Dts.TaskResult = Dts.Results.Success
End Sub End Class Panic, Chaos, Disorder … my work here is done –unknown
have you declared dts variables as read only or read and write variables.
I’ve created the Script Task, with the following settings:
PrecompileScriptIntoBinaryCode = False (it complained at one point in time (possibly on validation))
EntryPoint = ScriptMain
ReadOnly Variables = ProductBuyListFileLocation, ProductBuyListFileName
ReadWrite Variable = FileFound Panic, Chaos, Disorder … my work here is done –unknown
I think it will be better if you refer to some sample packages eg: SynAdvWorksPartitions.dtsx
these samples can be found in samples directory.
The code looks correct so it seems you must be still missing something which may be using variables after setting values to those dts variable
<br />I think I’ve found the problem. Seems I had 2 variables named the same name, with different scopes – one was package, the other was script. I suspect I was setting the script variable, but checking the package variable. Since deleting the rogue script-scope variable, it seems to be working (in 1 run, at least). I’m making some changes, and I’ll be creating a few more scripts tasks that also need tro report on existance, and we’ll see how it goes <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Panic, Chaos, Disorder … my work here is done –unknown
]]>