locked
Copying Files using Dynamic Variables and Expressions RRS feed

  • Question

  • I am trying to copy specific files from one server to another, zip the file then FTP the file.

    My first question is that will I be able to copy using the wild card?  For example, my database table has Book_Type, Book, Page,NumOfPages,FilePath,PartialFileName.
    O, 01234, 0001,3,\\ServerName\Book_Type\Book,O.01234.0001*.tif               
    The server actually has 3 files for me to copy O.01234.0001.0001.tif, O.01234.0001.0002.tif and O.01234.0001.0001.tif.  The NumOfPages tells me how many files will need to be copied.  Or I can just grab all using the wildcard. My largest book a little over 400 pages.

    So far I have the following: 

    DataFlow Task - with OLE DB Source and Record Set Destination
    Execute SQL Task that puts the record set into a object [User::rsDetails]
    ForEachLoop Container - ADO Enumerator; ADO object Source variable User::rsDetails
    Variable Mappings of my column names.

    Within the loop I have FileSytem Task but having problems setting the destination and source connection as an expression.  Both values would be a combination of FilePath and FileName from the rsDetails.

    Also Have a

     Execute Process Task to zip the files after they have been copied to the Destination then finally a FTP task.

    I have read through several forums and tried several examples but I am having a hard time.  If you have screenshots or detailed examples that would be great.

    • Changed type Rattler1887 Tuesday, June 12, 2012 8:36 PM Need New Answer
    • Changed type Rattler1887 Friday, June 15, 2012 5:20 PM Still need an answer
    Tuesday, June 5, 2012 7:15 PM

Answers

  • You have to use the Script task, we ought to see the resulting string, it is the issue.

    Arthur My Blog

    • Marked as answer by Rattler1887 Monday, June 18, 2012 6:24 PM
    Friday, June 15, 2012 8:50 PM
  • You can do it using using the File System Task with its operation set to "Create Directory"

    Your path is going to be variable driven, so I guess it is worth looking up at more details here: http://sqlserversolutions.blogspot.ca/2009/01/creating-directory-using-ssis.html


    Arthur My Blog

    • Marked as answer by Rattler1887 Monday, June 18, 2012 6:24 PM
    Monday, June 18, 2012 5:45 PM

All replies

  • Seems like an Execute process task would work better than the FileSystem task in your case.  create an expression to "Copy " + filepath + "\\" + partialfilename + " " + destination path

    Chuck Pedretti | Magenic – North Region | magenic.com


    Tuesday, June 5, 2012 7:19 PM
  • Shredding the recordset properly is what you need 1st: http://www.sqlis.com/post/Shredding-a-Recordset.aspx. Then tell in more details what exactly (e.g. errors) goes wrongly.

    2nd thing is zipping the files is easy doing like in http://www.rafael-salas.com/2008/10/ssis-compress-files-using-execute.html or http://dataintegrity.wordpress.com/2009/10/13/dynamically-zipping-files-in-ssis/

    I trust FTPing the compressed archive is a no brainer for you, otherwise please feel free to ask


    Arthur My Blog

    • Marked as answer by Eileen Zhao Monday, June 11, 2012 7:25 AM
    • Unmarked as answer by Rattler1887 Monday, June 11, 2012 7:35 PM
    Tuesday, June 5, 2012 7:32 PM
  • I apologize for the delay.  My system went out and had to repair and restore. 

    Arthur, I was able to clear out the initial errors that I encountered(mostly syntax and being consitent with upper and lowercase).  My first Dataflow Task and GetFull Result set is processing without errors.  My first problem is that I don't know how to use the results from the record set for my loop.

    I think that I will have a loop to get the directory and inside of that a loop to iterate the files but not quite sure the best approach.

    The links have been really helpful but it's really challenging for me to piece all of it together.  I hope that it starts making since soon.  I thank each of you for your time and patience.

    Monday, June 11, 2012 7:46 PM
  • This is the error that I get....

    My varible expressions are

    Source:  Expression @[User::rsPath]
    Destination: Expression @[User::strDestFilePath] + "\\" +  @[User::rsBookType]  + "\\" +  @[User::rsBook] + "\\"

    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at File System Task [File System Task]: "SourcePath" is not valid on operation type "CopyFile".

    Error at File System Task: Failed to lock variable "\\\*" 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.".

    Error at File System Task [File System Task]: An error occurred with the following error message: "Failed to lock variable "*\\\*" 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.".
    ".

    Error at File System Task: There were errors during task validation.

     (Microsoft.DataTransformationServices.VsIntegration)

    Tuesday, June 12, 2012 8:30 PM
  • Also tried to use my record set details in my expression for my ConnectionString but get the following error:

    TITLE: Expression Builder
    ------------------------------

    ADDITIONAL INFORMATION:

    Attempt to parse the expression ""C:\temp\destination\" +  @[User::rsBookType]  + "\\" +  @[User::rsBook] + "\\"" failed.  The token "\" at line number "1", character number "51" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

     (Microsoft.DataTransformationServices.Controls)

    Tuesday, June 12, 2012 8:39 PM
  • It is possible you have a typo in the variable name (hence you got "The variable cannot be found")

    Arthur My Blog

    Tuesday, June 12, 2012 8:42 PM
  • You need to use \\ instead of \ in all places

    This expression will work

    "C:\\temp\\destination\\" +  @[User::rsBookType]  + "\\" +  @[User::rsBook] + "\\"


    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, June 12, 2012 10:00 PM
  • Arthur,

    I am thinking that the result set is not reading into the loop as expected.

    The directory layout is as follows:
    \\ServerName\ORImage\BOOK_TYPE\BOOK\

    with data it looks like \\ServerName\ORImage\O\01632\

    FileName in the above path would be O.01632.0304.0001.tif, however, I want to mask the filename to copy the pattern O.01632.0304.*.tif

    for testing purposes, I have my variable strSourceFolderPath defaulted to C:\temp\source\ORG\and the filename is *.tif.

    The strSourceFolderPath - Evaluate as Expression is Set to True and the Expression is @[User::rsPath] + "\\" + @[User::rsBookType]  + "\\" +  @[User::rsBook] + "\\"

    The strSourceFileName - Evaluate as Expression is Set to True and the Expression is
    @[User::rsBookType] + "." + @[User::rsBook]+ "." + @[User::rsPage]+ ".*.tif"

    The Book_Type and Book values comes from the result set.


    • Edited by Rattler1887 Friday, June 15, 2012 5:19 PM removed links
    Friday, June 15, 2012 5:18 PM
  • Could be, were you able to see the exact path generated by the expression?

    I would start there. To see the content of the resulted variable, make another one and then break the package execution to inspect its value once initialized.

    PS: The mask appears correct, and I do not quite understood the "with data it looks like \\ServerName\ORImage\O\01632\" part but it appears like like you have a typo between the O and 01 it must be a . no?


    Arthur My Blog

    Friday, June 15, 2012 6:13 PM
  • I was not able to see the path generated by the expression, that's what I am trying to get help with.  I don't think I am retrieving the result set correctly. 

    I can access it via Script, but I don't want to use a script.

    PS:  just added the statement to show that book_type and book were used from the result set for the SourcePath and file name.

    No typo, the path and file names are using results from the record set.

    Friday, June 15, 2012 8:43 PM
  • You have to use the Script task, we ought to see the resulting string, it is the issue.

    Arthur My Blog

    • Marked as answer by Rattler1887 Monday, June 18, 2012 6:24 PM
    Friday, June 15, 2012 8:50 PM
  • What should be coded in the task script?

    I have a sample that displays a message box

    Public Sub Main()
            Dim oleDA As New OleDbDataAdapter
            Dim dt As New DataTable
            Dim col As DataColumn
            Dim row As DataRow
            Dim sMsg As String
            oleDA.Fill(dt, Dts.Variables("rsDetails").Value)
            sMsg = ""
            For Each row In dt.Rows
                For Each col In dt.Columns
                    sMsg = sMsg & col.ColumnName & ": " & _
                           row(col.Ordinal).ToString & vbCrLf
                Next
                MsgBox(sMsg)
                sMsg = ""
            Next
            Dts.TaskResult = ScriptResults.Success
        End Sub

    Monday, June 18, 2012 12:25 PM
  • I made further progress...now I am getting the error:

    "Could not find a part of the path 'C:\temp\destination\ORG\O\01732\O.01732.0612.0001.tif'

    The folder "01732" does not exists.  How do I create a folder before the system begins to copy it to the destination?

    Monday, June 18, 2012 5:18 PM
  • You can do it using using the File System Task with its operation set to "Create Directory"

    Your path is going to be variable driven, so I guess it is worth looking up at more details here: http://sqlserversolutions.blogspot.ca/2009/01/creating-directory-using-ssis.html


    Arthur My Blog

    • Marked as answer by Rattler1887 Monday, June 18, 2012 6:24 PM
    Monday, June 18, 2012 5:45 PM
  • Thanks for your time and patience.  I am working on adding the above solution to what I have already.  Thanks again.
    Monday, June 18, 2012 7:37 PM