SQL Server Performance

SSIS Variables Error

Discussion in 'SQL Server 2005 Integration Services' started by danny123, Aug 4, 2008.

  1. danny123 New Member

    Hi there,
    First step of my SSIS package is supposed to rename the cvs file. For that purpose i created on variable to specify the location of the file to be renamed. Now i am using the script task to perform this action. Below is the code:Imports
    SystemImports
    System.DataImports
    System.MathImports
    Microsoft.SqlServer.Dts.RuntimeImports
    System.IOPublic Class ScriptMain
    Public Sub Main()
    Dim srcFile, dstFile As String
    Dim intlen As IntegerDts.Variables(
    "Path1").Value = TruesrcFile = Dts.Variables(
    "Path1").Value.ToString() & Date.Now.AddDays(-1).ToShortDateString().Replace("/", "") & ".csv"dstFile = Dts.Variables(
    "Path1").Value.ToString() & Date.Now.Date.ToShortDateString().Replace("/", "") & ".csv"File.Copy(srcFile, dstFile, True)
    Dts.TaskResult = Dts.Results.SuccessEnd Sub
    End
    Class
    and i am getting this error.
    "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
    at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
    at ScriptTask_3760433a47db4d6a8540bbb738b4c4a1.ScriptMain.Main()"
    Please advise.
    Thanks!!
  2. danny123 New Member

    Hi There,
    I found my mistake. I was trying to set the value of "Path1" to wrong type.
    Thanks!
  3. atulmar New Member

    This is related to the locking of variable.
    Once you lock the variable that will be available for read or write depending on the lock type you are putting on that.
    VariableDispenser classes can be used for that. Another way is to put the variable name in Script task properties dialog, variable boxes.

Share This Page