Topic "Using SSIS variables in a script"

Author Message
samspritzer
[View user's profile]

Joined: 2017-05-11
Posts: 2
Location: The Byte Mines
I have an SSIS package that gets files from an FTP site. All of the files are downloaded at once. Then, SSIS loops through each file sets a variable to the file's name. Then it processes the file. This is the script to get the files...
option batch on
option confirm off
open sftp...
get /from-cfs/*Redemptions*.* \\SVRDWDB\etl\CoinFlip\
close
exit

As you can see, Redemptions has wildcards since there could be several for different times of the day.

After all of the files are processed, SSIS exits the loop and runs the script to delete the files.
option batch on
option confirm off
open sftp...
rm /test/from-cfs/*Redemptions*.*
close
exit


Ideally, I would like to include the delete task as part of the file processing so that after the file is archived, it should get deleted from the remote site. I know which file to delete because the file name is stored as an SSIS variable.

The question I have is....is it possible to pass this variable as part of the rm step? If so, how?

Thanks in advance,
Sam
martin◆
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 26890
Location: Prague, Czechia
SSIS allows you to pass the variable to WinSCP command-line by using an expression in the Arguments property.

You can use /parameter switch to pass that further to the script, like:

"/script=c:\path\to\script.txt /parameter " + @Variable

Then in the script, use %1% to use that parameter:

rm /test/from-cfs/%1%

References:
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-process-task
https://winscp.net/eng/docs/scripting#arguments
https://winscp.net/eng/docs/script_upload_multiple_servers
samspritzer
[View user's profile]

Joined: 2017-05-11
Posts: 2
Location: The Byte Mines
Thank you!

martin wrote:
SSIS allows you to pass the variable to WinSCP command-line by using an expression in the Arguments property.

You can use /parameter switch to pass that further to the script, like:

"/script=c:\path\to\script.txt /parameter " + @Variable

Then in the script, use %1% to use that parameter:

rm /test/from-cfs/%1%

References:
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-process-task
https://winscp.net/eng/docs/scripting#arguments
https://winscp.net/eng/docs/script_upload_multiple_servers
Advertisements

You can post new topics in this forum

Search

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License