none
Send File with Dynamic Name through SFTP task

    Question

  • Hi,

    I am using SQL server 2008. I appended date and time(yymmddhhmm) to the csv file generated by the SSIS package I created through adding variables and expression in the connection string.


    However, when I tried to send the file through SFTP task, the package failed to run and return message: 'Source: SFTP Task SFTP Task Description: The File "Flat File Connection Manager" doesn't contain file path(s).'

    I set the local 'flat file connection manager' in my SFTP task. It worked well in the past but started to have issue recently.

    I think the issue is related to the running time of package..It only happens when it takes more than 1 min to run...as I append hour and min to the file name, I wonder is it because when the file finishes running, SFTP can't find the file attached with current time? For example, when the package starts to run , the connection manager creates a file called 201210170101(1:01am). When the SFTP task intends to send out the file, it's already 1:03am. So SFTP will try to look for a file with name 201210170103, which doesn't exist. This is just what I guess. Not sure if this is the cause and I don't know how to solve it.


    Could anyon
    e help out?

    Thank you very much.

    Thursday, October 18, 2012 1:50 PM

Answers

  • Hi CozyRoc,

    I only evaluated the expression in the flat file connection manager. But I set the both the destination of the file system task and the local of SFTP task the file connection manager. Will that evaluate the expression twice? If this this the case, how can I get the result the my fist expression?

    Thank you very much for your explanation!

    The expression will be evaluated every time you acquire connection to the flat file connection manager. If you have two components acquiring connection, then the expression will be evaluated for each and there goes your issue. This is an interesting problem. Let me suggest a workaround. What if you setup your expression to be based on the System::StartTime variable? In this way the date/time will be exactly the same during the package execution.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    • Marked as answer by JOCHANG Monday, October 22, 2012 7:13 PM
    Monday, October 22, 2012 5:54 PM

All replies

  • What SFTP task is that?

    I think you need to parametrize the file name for the task and feed it with the one making sure the file already exists, not before.


    Arthur My Blog

    Thursday, October 18, 2012 1:55 PM
  • Hi Arthur,

    Thanks for your reply. It's COZYROC SFTP Task Editor. http://www.cozyroc.com/ssis/sftp-task

    I am not sure how to parmetrize the file name..could you please shed some light on that? Thank you.

    Thursday, October 18, 2012 2:01 PM
  • CozyRoc's components are top notch, I can see it allows file parametrization:

    Imports CozyRoc.SqlServer.SSIS
    02. 
    03....
    04. 
    05.Dim fis As Object()
    06.Dim vars As Variables
    07.Call Dts.VariableDispenser.LockOneForRead("ResultVar", vars)
    08.Try
    09.fis = CType(vars("ResultVar").Value, Object())
    10.Finally
    11.Call vars.Unlock()
    12.End Try
    13. 

    14.Dim firstInfo As IFileInfo = CType(fis(0), IFileInfo)

    Try creating a package variable with the proper file name and assign it to the task, it must work. The above code segment is to how to assign the file name to the variable.


    Arthur My Blog

    Thursday, October 18, 2012 2:08 PM
  • Hi Arthur,

    I did create the variable with name like 'D:\file' and edited the connection string in the connection manager with codes:

    @[User::file]+(DT_STR,4,1252) DatePart("yyyy",getdate()) +

    Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +

    Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)+

    Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2)+

    Right("0" + (DT_STR,4,1252) DatePart("mi",getdate()),2)+".csv"

    Then I set the local in the sftp task the flat file connection manager. I thought it would send out whatever the flat file connection was created but looks like it doesn't work now. Could you please point out what might go wrong here? Thank you very much!

    PS, my package only contains

    flat file system-->data flow task-->sftp task

    though the file was failed to be sent out, I can see it's created in my local folder with name 201210170101 and createtime( or update time?)0103am

    Thursday, October 18, 2012 2:20 PM
  • Well, 1st thing

    What the resulting value of the SSIS expression above? What file name did you get?

    I am asking because looking up there the name 201210170101(1:01am) is not valid for a file


    Arthur My Blog

    Thursday, October 18, 2012 2:44 PM
  • Sorry I didn't explain that clearly.

    The file name is 'file'. The resulting file name generated by the expression is 'file201210170101.csv

    Thanks !!'

    Thursday, October 18, 2012 2:53 PM
  • OK, no problem here,

    so I assume the expression works, then is that possible the SFTP task attempts to send a file while data is still being written to it?

    Do you still get "The File "Flat File Connection Manager" doesn't contain file path(s).'" error?


    Arthur My Blog

    Thursday, October 18, 2012 2:55 PM
  • I am not sure...yea still get that.....Can you think of any other causes? I am out of idea here..and it only happens when it takes more than 1 min to run......
    Thursday, October 18, 2012 3:08 PM
  • Hi,

    Is there anything that I can check for troubleshooting?

    Thanks.

    Friday, October 19, 2012 2:17 PM
  • Hi,

    The error you are seeing will be shown if you specify input file(s) which doesn't exist. I would recommend you check again your expressions . I think your suspicion about the time when one expression is evaluated and when the other is correct. You have to make sure you use the same file name in both instances.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Saturday, October 20, 2012 2:18 AM
  • Hi CozyRoc,

    Thank you very much for your confirmation! I am wondering is there anyway for me to keep the filename as it is when the package first runs(keep it as file201210170101.csv)?Because it automatically changes to file201210170102.csv when the SFTP task calls the connection...

    Monday, October 22, 2012 1:45 PM
  • Hi CozyRoc,

    Thank you very much for your confirmation! I am wondering is there anyway for me to keep the filename as it is when the package first runs(keep it as file201210170101.csv)?Because it automatically changes to file201210170102.csv when the SFTP task calls the connection...

    This would happen if you evaluate the same expression in two different places. You have to use only one expression to dynamically setup the filename . And in the second instance use the result of your first expression.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, October 22, 2012 2:07 PM
  • Hi CozyRoc,

    I only evaluated the expression in the flat file connection manager. But I set the both the destination of the file system task and the local of SFTP task the file connection manager. Will that evaluate the expression twice? If this this the case, how can I get the result the my fist expression?

    Thank you very much for your explanation!

    Monday, October 22, 2012 4:51 PM
  • Hi CozyRoc,

    I only evaluated the expression in the flat file connection manager. But I set the both the destination of the file system task and the local of SFTP task the file connection manager. Will that evaluate the expression twice? If this this the case, how can I get the result the my fist expression?

    Thank you very much for your explanation!

    The expression will be evaluated every time you acquire connection to the flat file connection manager. If you have two components acquiring connection, then the expression will be evaluated for each and there goes your issue. This is an interesting problem. Let me suggest a workaround. What if you setup your expression to be based on the System::StartTime variable? In this way the date/time will be exactly the same during the package execution.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    • Marked as answer by JOCHANG Monday, October 22, 2012 7:13 PM
    Monday, October 22, 2012 5:54 PM
  • Thank you very much CozyRoc!! It's working now. :)
    Monday, October 22, 2012 7:13 PM