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:
40,476
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://learn.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

khoa
Guest

Re: Using SSIS variables in a script

Hi martin

Using this script below works for me. But when I try to pass the parameter with the variable in.. I get an error.

Argument works here
/script=C:\WinSCP\SFTP_Batch_Script.txt /parameter extract*.txt

Argument failed when I pass the the variable.
/script=C:\WinSCP\SFTP_Batch_Script.txt /parameter +@VFolderName

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
40,476
Location:
Prague, Czechia

Re: Using SSIS variables in a script

khoa wrote:

Argument failed when I pass the the variable.
/script=C:\WinSCP\SFTP_Batch_Script.txt /parameter +@VFolderName
This does not look like the syntax I've posted above. You are missing the quotes. If this does not help, we need to know more than "failed". Though this is actually not a WinSCP question, so you better seek a support on an SSIS related site.

Reply with quote

Advertisement

bleys
Guest

Re: Using SSIS variables in a script

@khoa: Were you able to solve this issue? Please share the solution if yes. I have the same problem.

Reply with quote

martin
Site Admin
martin avatar

Re: Using SSIS variables in a script

@bleys: What about my answer above? We need way more information, if you want our help. (You will hardly get an answer from an anonymous user after two years)

Reply with quote

Advertisement

You can post new topics in this forum