SSIS - FTP TASK - DYNAMIC FILENAME

  • Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with a dynamic local path. I just want the FTP task to send whatever file is in the directory I specify.

    I'm creating a package that extracts data from a table to a pipe-delimited file. I then use an external process (since using Expressions for it was a nightmare) to rename the file so that it'll reflect the previous month (e.g. April2008_report.txt). Then, I have to FTP that file. That's where my problem is. It seems I need to specify the exact filename on the local path of the FTP task, and since the filename will change each month... well, you see my problem.

    Is there a way to tell the FTP task to just send *.txt, or something to that effect?

    Jafar

  • If the file is alone in the directory, you could use a ForEach loop and loop through the files (of which there will be one).

    I am not sure why you would have had to use an external process to rename the file to the current month / current year. If you were doing this with an expression variable and a file system task, your variable could be used in the ftp task.

    The expression for the variable would be:

    [font="Courier New"](MONTH(GETDATE()) == 1 ? "January" : MONTH(GETDATE()) == 2 ? "February" : MONTH(GETDATE()) == 3 ? "March" :

    MONTH(GETDATE()) == 4 ? "April" : MONTH(GETDATE()) == 5 ? "May" : MONTH(GETDATE()) == 6 ? "June" :

    MONTH(GETDATE()) == 7 ? "July" : MONTH(GETDATE()) == 8 ? "August" : MONTH(GETDATE()) == 9 ? "September" :

    MONTH(GETDATE()) == 10 ? "October" : MONTH(GETDATE()) == 11 ? "November" :

    MONTH(GETDATE()) == 12 ? "December" : "InvalidMonth")

    + (DT_WSTR, 4) DATEPART("YYYY",GETDATE())

    + "_report.txt"[/font]

  • You can also use a script task to get your *.txt functionality

    This will require writing a function opens an ftp connection.

    This is ideal if you can't actually predict the filename beyond a reasonable doubt. If you need this, i'll Post asap.

  • Hey Guys,

    Thanks a bunch for the replies.

    I think I'm gonna go with the for each loop solution and just pass the path or filename as a variable to be accessed by the ftp task.

    As for the use or not use of expression. The one you included is sooo cool, I never would've thought of that since i'm really still not familiar with the syntax used in the expression builder. However, I'm not sure how I can use that to display the previous month, not the current one. My report will contain data for the previous month, so it should reflect the same accordingly.

    On a related note, is there a source (site, ebook, whatever), that teaches how to write expressions, it's syntax, etc.?

    Thanks again for you patience 🙂

    Jafar

  • Previous month:

    [font="Courier New"](MONTH((DATEADD("MONTH",-1,GETDATE()))) == 1 ? "January" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 2 ? "February" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 3 ? "March" :

    MONTH((DATEADD("MONTH",-1,GETDATE()))) == 4 ? "April" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 5 ? "May" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 6 ? "June" :

    MONTH((DATEADD("MONTH",-1,GETDATE()))) == 7 ? "July" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 8 ? "August" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 9 ? "September" :

    MONTH((DATEADD("MONTH",-1,GETDATE()))) == 10 ? "October" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 11 ? "November" :

    MONTH((DATEADD("MONTH",-1,GETDATE()))) == 12 ? "December" : "InvalidMonth")

    + (DT_WSTR, 4) DATEPART("YYYY",(DATEADD("MONTH",-1,GETDATE())))

    + "_report.txt"[/font]

    I just replaced GETDATE() with (DATEADD("MONTH",-1,GETDATE()))

    As far as a reference, I have not found a good one yet.

    The documentation on expressions in SSIS is terrible, I tend to Google a lot.

  • BTW - I would go with variables and expressions before using the ForEach loop container. It will be a much cleaner solution.

  • Also, don't be afraid to post the "How do I?" on this site when you need expression help. There are some people that watch these forums that are really good with them.

  • Hi Michael,

    Thanks for all your help! 🙂

    I'm trying your suggestions. I've removed the external process and configued my destination flat file to use a variable which points to the path directory of the file. I then used expressions to create the value (filename) of the connectionstring of my destination connection object. My data flow now works, and it creates the file with name I need. However, I'm still encountering a problem with my FTP task. I'm not sure if I'm doing it right, but I configured the localpath of my FTP task as a variable. I then used the same variable (as the one I used for my destination flat file). Again, I used expression builder to set the localpath property as the variable concatenated with the expression code you gave me. The error this time is:

    Error: Failed to lock variable "c:\report\April2008_report.txt" 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.".

    I tried creating a new variable in case the error is because the other one is still being referenced by the data flow, but I still get the same result (error). Any ideas? I'm sure there's a simple explanation to this, just can't figure it out myself.

    Jafar

  • Not sure on this, but it looks to me like you put the file path into the variable name rather than the variable value. It appears to be looking for a variable with the name of your file path. Does that make sense?:unsure:

  • I think the last post hit it. I think you used expressions on your container and set the variable name to be the file path. I would suggest you use a variable set to evaluate as expression and use the expression to create file file path and name. Then you should not have to use expressions on your container at all, just select the variable in the editor.

  • Hey Guys

    Need some help, I am also using a dynamic SSIS package the Uploads and Downloads Files from an FTP, I have a problem on my FTP TASK, When I run the FTP Task to Download a file from the ftp site and there is no file on the site it fails, but it runs successfully when there is a file

    What I would like to find out, is there some kind of property that I can change, that will allow the FTP Task to run successfully even though that there is no file on the ftp, a property that would work similar to DelayValidation, which means you can pass variables during run time instead of having them to be static

    Regards,

    Wilbur

  • Instead of using Success can you change the arrow to complete, check for a newly downloaded file at the next step, and proceed from there?

  • Hi, I found your posts to be very helpful. Based your posts I've been successful at setting the value of my user variable with an expression that creates the file name. My Data Flow tasks works great, however my FTP task continues to errors out.

    I am using a variable named "LocalPathVar" to define the LocalVariable for the LocalPath. The variable's value is determined by the following expressions:

    "c:\\" + @[User::StrFlatFileName]

    Where the @[User::StrFlatFileName] variable determines the file name and the value in the properties window for the variable displays as "c:\ahamembers20080930111217.txt". However, I keep getting the error message, "The variable "User::LocalPathVar" doesn't contain file path(s)."

    When I add a breakpoint to my FTP Task and execute the package with the debugger turned on, I can see through the Locals window that the LocalPathVar value is set to "c:\\ahamembers20080930105942.txt" instead of the value that variable's property window shows.

    Does anyone have any idea what I'm missing? Thanks

  • The values that show in the properties are normally the values that exist based on the debugging environment and can differ based on how you're actually calling the package. That said you should try to see what the values are at run time.

    For me I check variable by putting a script task right before subsequent tasks (FTP Task in your case) and in that Script Task, I pull the variable in and use the tried and true MsgBox(Variable) name approach.

    'i.e. Put Pull the variable into the script task

    Dim vars As Variables

    Dts.VariableDispenser.LockOneForRead("User::VariableName", vars)

    Dim variable As String = vars("VariableName").Value.ToString()

    vars.Unlock()

    Then

    MsgBox(Variable)

    Hope this sheds some light on the issue

  • Hey James, thanks for the suggestion it was very helpful. I discovered that the expression I was using the determine the flat file name (StrFlatFileName) was being evaluated everytime it was called which caused the variable's value to change between the Data Flow Task and the FTP Task.

    My original expression used "getDate()" to determine date/time stamp for the file name, e.g. "ahamembersyyyymmddhhmmss.txt". So the file name that the FTP Task was looking for was a few seconds off compared to the file name that the Data Flow Task created.

    I fixed this by using the System::StartTime variable instead of the getDate() function.

    I'm glad that I finally got this working. I've spent way to many hours troubleshooting the final step in the SSIS package. Thanks again for the suggestion it made me take a closer look at the file name that was being dynamically created.

Viewing 15 posts - 1 through 15 (of 39 total)

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