SSIS: Send dynamically named csv file to FTP server

onesegun
onesegun used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.

Author

Commented:
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

Author

Commented:
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
Commented:
Ok found the solution at this link below:

SSIS - Dynamic File Name for FTP Component

Author

Commented:
Solution based on web search with link

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial