SQL Server Performance


Discussion in 'SQL Server 2005 Integration Services' started by tthomasol, Jun 6, 2008.

  1. tthomasol New Member

    I have an SSIS package setup to check for XML files from a specific folder on an FTP server.
    The first Task is an FTP download and copies everything to a local directory.
    Then I setup a ForEachLoop container on the directory to iterate through all the downloaded files.
    The first thing I want it to do is to delete any file off of the FTP server that I have just downloaded. I could find no way to get the original FTP task to MOVE the file so I'm assuming that I have to create yet another FTP task that now deletes these from the FTP server based on the name of the file that I'm capturing in my loop. So I setup 2 variables var1 = ftpRemotePath and var2 = XMLFileName and var3 = fullpath. I then setup my FTP task to delete the file inside of my ForEachLoop Task and I set it up as follows
    >>FileTransfer>>IsRemotePathVariable = True
    >>FileTransfer>>RemoteVariable = fullpath
    >>FileTransfer>>Operation = DeleteRemoteFiles
    >>FileTransfer>>IsTransferASCII = True
    >>Expressions>>RemotePath = @[User::fullpath] = @[User::ftpRemotePath] + @[User::XMLFileName]
    I set the Execution Property for Delay Validation to True and attempt to run it.
    I get this error.
    Error: Failed to lock variable "/Prod/Outbound/TransportXML/Transport-110034057.xml" 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.".
    The Path and Filename listed in Quotes is exactly what I'm trying to delete but it's as though SSIS is resolving the variable and yet saying it's unable to resolve the variable at the same time. Can anyone help me with this....I expected this to take about 5 min to setup and I've been at it for much longer than I care to admit.
  2. tthomasol New Member

    Well, I took a different approach here and it is much cleaner and also seems to be quicker.
    I am using a script task instead of the ftp task. The above setup required an FTP task that downloaded all of the files and then a loop that iterated on the files that the job just downloaded. Then I was attempting to concatenate variables together to combine the path and file name of each file that it was iterating over and issue a single delete request to the ftp server as every file was processed. Not only could I not get passed the file locking problem (it might have something to do with UNIX ftp) but I suspect that this is a horribly inefficient way of doing this as it appears (visually) that the job would be opening and closing the ftp connection repeatedly. Here is the contents of my script in case anyone else is interested.Imports
    Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain Public Sub Main()
    TryDim cm As ConnectionManager = Dts.Connections.Add("FTP")
    "ServerName").SetValue(cm, "ftp.*******.net")cm.Properties("ServerUserName").SetValue(cm, "*********")
    "ServerPassword").SetValue(cm, "*********")cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "60")
    "ChunkSize").SetValue(cm, "1000") '1000 kbcm.Properties("Retries").SetValue(cm, "1")Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
    Dim fileNames() As String
    Dim folderNames() As Stringftp.SetWorkingDirectory("/Production/Outbound/XML/")
    ftp.GetListing(folderNames, fileNames)
    ftp.ReceiveFiles(fileNames, "E:SSISFTPDATA DOWNLOADSXML", True, True)
    ftp.Close()Catch ex As Exception
    Dts.TaskResult = Dts.Results.Failure
    End TryDts.TaskResult = Dts.Results.SuccessEnd Sub

Share This Page