Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to use a File System Task with a Source/Destination Variable

SSIS Tasks -

How to use a File System Task with a Source/Destination Variable

by  Catadmin  Posted    (Edited  )
Oh, the joys of using the File System Task dynamically. It is a royal pain in the butt. @=)

I'm sure you've seen the wonderful "Error at File Move Task [File System Task]: Variable "myvar" is used as a source or a destination and is empty". Well, if you haven't found a solution for it yet, this post is for you.

I have a flat file named "MyFile.Year.Month.Day.txt", with the Year, Month and Day portions of it changing constantly, that I want to move from a Holding folder to a Processed folder. Sounds easy, except when you take into consideration that every day the file name is different.

I used a Script Task, before my actual data transformation, to locate the name of the file. First, I created three package level variables, TotFileName and TrueFileName and Unprocessed_Loc, as String. Then, I enter into the Value of Unprocessed_Loc the UNC path the unprocessed files will always be in. I.E. \\MyServer\MyFolder\. Make sure you get that last slash.

Drop and drag the Script Task to the Control Flow window. Edit it, giving it a friendly name, and on the script tab, put in TrueFileName,Unprocessed_Loc as the ReadOnly Variables. Enter TotFileName as the ReadWrite Variable. Click "Design Script". Here's the script I used to get the variables filled.


Code: 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()

        Dim TotFileName As String

        MsgBox(CStr(Dts.Variables.Item("TrueFileName").Value))

        TotFileName = CStr(Dts.Variables.Item("Unprocessed_Loc").Value) & LTrim(CStr(Dts.Variables.Item("TrueFileName").Value))

        Dts.Variables.Item("TotFileName").Value = TotFileName

        MsgBox(TotFileName & " is TotFileName")
        MsgBox(Dts.Variables.Item("TotFileName").Value)

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Note that the MsgBox stuff is just my personal verification that everything filled up correctly. You can remove those lines once you're sure the variables have filled correctly. Also, the LTrim is VERY IMPORTANT. Otherwise, your path will resolve with a space right before your FileName.

TrueFileName should populate with only the actual FileName. TotFileName should populate with the entire path. Yes, maybe it's a little overkill, but I wanted to be sure I covered all my bases. @=)

Once the Script Task is done, close it and create your Flat File Connection Manager. You'll have to put the literal path in the box titled "File Name" to get your column widths & delimiters set up. If you don't currently have a file in your "Holding" folder, create a quick fake one with NotePad (including a few sample rows of data) to make sure you get your columns set up correctly. Then hit "OK" to get out of the Edit Window.

Go to the properties of the Flat File Connection Manager, click into the Expressions box and open the popup window with the ellipses. Choose ConnectionString under Property and then click the ellipses in the new Expressions column. Choose "@[User::TotFileName]". Then click "Ok" again.

The above sets up everything you need to do your data transformation. Set up the actual pump process however you need to and do your Start Debugging process until you've worked out all the kinks. It's easier to do it now than to wait until after you've put in your File System Task.

Once all the troubleshooting is done, add your File System Task. When you get into the Edit, make sure IsSourcePathVariable = False and set the SourceConnection to your Flat File Connection. Unfortunately, setting it to another variable doesn't work unless the variable is a constant, never changing, and the value is filled in on the Variables tab. Fortunately, the Destination doesn't require the actual FileName, so assuming your "Processed" folder is always going to be in the same place, you just fill in your fully qualified UNC path in the value slot on the Variables tab.

Choose your method (Move File, Copy File, etc.) and click "Ok".

Debug once more, but it should all come together at this point and smoothly move your dynamically name-changing source file from your Holding folder to your Processed folder when the package runs correctly.

If you have any questions or comments on my process, let me know. I hope this helps out someone and saves them the hours of headaches I went through figuring this out. @=)

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top