SQL Server Performance

SSIS Script Task to check for File Existance

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Jul 3, 2006.

  1. SQL_Guess New Member

    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
  2. SQL_Guess New Member

    .. 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
  3. ranjitjain New Member

    have you declared dts variables as read only or read and write variables.
  4. SQL_Guess New Member

    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
  5. ranjitjain New Member

    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
  6. SQL_Guess New Member

    <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

Share This Page