Dynamic file name for FTP task.

  • Hey I am also working on same task like this In SSIS ftp task...

    Here I created a variable for getting dynamic file name path for

    "LOCAL PATH"..

    @[User::LDirectoy] = "E:\LocalFilefolder\"

    @[User::LZipFilePrefix] = "KK_"

    @[User::LZipFileEndExtension] = ".rar"

    @[User::LDirectoy]+ @[User::LZipFilePrefix] + (DT_STR, 4, 1252) DAY( DATEADD("dd", -1, GETDATE() ) )+"-"+RIGHT("0"+(DT_STR, 4, 1252) MONTH( GETDATE() ),2)+"-"+(DT_STR, 4, 1252) YEAR( GETDATE() )+ @[User::LZipFileEndExtension]

    op: for this expression.." E:LocalFilefolder/KK_26-09-2012.rar"

    while debugging i am getting

    Error: Failed to lock variable "E:\LocalFilefolder\KK_26-09-2012.rar" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    Any help will be apreciated.......

  • Is the variable in a scope that can be used by your task?

  • These variables are created at package scope...

    I hope this might correct as of your concern..

    other than this anything i can do please let me know.

  • What if you set the variable to Evalute as expession?

    Other wise, is the file being brought over from the ftp? if so, rename it once its your side?

    Ian Cockcroft
    MCITP BI Specialist

  • If you are using ssis 2008, you can set up variables read/write in a vb script task.

    Easy to modify, just set-up the variables you will be reading and writing from and modify them in the script.

    Should be easy to figure out...

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

    <System.CLSCompliantAttribute(False)> _

    Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    ' 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()

    'Assume success

    Dts.TaskResult = ScriptResults.Success

    Dim FileName As String

    Dim Path As String

    Dim FTP_User As String

    Path = CType(ReadVariable("Data_Location"), String)

    FTP_User = CType(ReadVariable("FTP_UserName"), String)

    ''FileName = "/" & FTP_User & "/" & Replace(CType(ReadVariable("FileName"), String), Path, "")

    FileName = "/ftp_root/" & FTP_User & "/"

    WriteVariable("Admin", FileName)

    End Sub

    Private Function ReadVariable(ByVal varName As String) As Object

    Dim result As Object

    Try

    Dim vars As Variables

    Dts.VariableDispenser.LockForRead(varName)

    Dts.VariableDispenser.GetVariables(vars)

    Try

    result = vars(varName).Value

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    Return result

    End Function

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

    Try

    Dim vars As Variables

    Dts.VariableDispenser.LockForWrite(varName)

    Dts.VariableDispenser.GetVariables(vars)

    Try

    vars(varName).Value = varValue

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    End Sub

    End Class

  • Sorry had to add my two cents about using expressions with Date included in the file name.

    This is a bad idea if you will be running the package around midnight and the date change will screw up the file name, especially if you have steps later on like archiving or deleting using that expression.

    Found this in a package a few weeks ago, I hate expressions as they are very cumbersome to maintain.

    (Note I did not write the package, work in a shop where there are SSIS packages written by numerous SQL developers, past and present)

    I suppose they have uses but not in my world.

  • churlbut (10/10/2012)


    Sorry had to add my two cents about using expressions with Date included in the file name.

    This is a bad idea if you will be running the package around midnight and the date change will screw up the file name, especially if you have steps later on like archiving or deleting using that expression.

    That is easily dealt with

    get the date at the start of the process, store it in a variable and always use the stored value. Do not get it multiple times throughout the process.

    That way you do not get any issues with dateparts changing whilst you are building up the string.

    i.e. use

    @DATETOWORKWITH=GETDATE()

    do something with @DATETOWORKWITH

    do something else with @DATETOWORKWITH

    do yet another thing with @DATETOWORKWITH

    and NOT

    do something with GETDATE()

    do somethingelse with GETDATE()

    do yetanother thing with GETDATE()

  • Thank you ........

    I will give a shot to my flow using this script

    hope it works...........:-):-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply