Using SSIS variables in a script

Advertisement

samspritzer
Joined:
Posts:
2
Location:
The Byte Mines

Using SSIS variables in a script

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

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
29,815
Location:
Prague, Czechia

Re: Using SSIS variables in a script

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

Reply with quote

samspritzer
Joined:
Posts:
2
Location:
The Byte Mines

Re: Using SSIS variables in a script

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

Reply with quote

Advertisement

You can post new topics in this forum