Avatar of onesegun
onesegun
Flag 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
Microsoft SQL Server 2008SSIS

Avatar of undefined
Last Comment
onesegun

8/22/2022 - Mon
Jim Horn

>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.
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
onesegun

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
onesegun

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
onesegun

ASKER
Solution based on web search with link