Link to home
Start Free TrialLog in
Avatar of onesegun
onesegunFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SSIS: Send dynamically named csv file to FTP server

Hi Experts,

I have a package that queries an OLE DB source and creates a CSV file to a folder using a dynamic name like so:
@[User::Dynamic_Filename] + (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) + ".csv"

Open in new window


Basically, in the flat file connection I created the connection using the code above so that the file is dynamically named with a specified name and the current date.

It looks like there is no way I can send the extract csv straight to FTP . So I'm having to send it to a flat file source in a specified folder.

But it is this same file that I want to send to FTP i.e. with the dynamic date appendage. How can I create another variable to use for the local path so I use the file that was just dynamically created?

Thanks,

OS
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>It looks like there is no way I can send the extract csv straight to FTP
<Potentially stupid answer>
Since you already have the file being created with the dynamic name, immediately after that can you create a File System Task to move the file to wherever your FTP connection is?

Create a variable that stores the dynamic name (if you're not doing that already), create a variable that stores the target FTP name, then the File System task connects to both source and target variables.
Avatar of onesegun

ASKER

Hi  Jim,

The File System Task only seems to work with folders though. I can't seem to define an FTP source inside it.

Thanks,

OS
Hi Jim,

One more thing:

I assigned the following to a variable which should give me the path of the recently created file right?
@[User::Dynamic_Filename] + (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) + ".csv"

Open in new window


When I now use this variable in the local path for the FTP task is says
"User:_File_FTP" doesn't contain file path(s)."
But it's the full path of what I just created?

Thanks,

OS
ASKER CERTIFIED SOLUTION
Avatar of onesegun
onesegun
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Solution based on web search with link