SSIS and WinSCP
Hi Martin,
2 things when working with SSIS:
1. the source code provided in csharp for integrating winscp in SSIS package uses a try/except construct. In the exception part, the demo code simply 'spits' out the exception object, but I learned the hard way (see point 2) that the 'real' error message is in the 'innerexception' property of the exception object. I would propose to adapt the demo code to 'spit' out the e.InnerException by default.
2. I created a SSIS package with parameters, so that during development the package would connect to a test server. Once deployed on a SQL server, I scheduled a package in which I adapted (override) the default parameters, in order to have winscp upload files to production server. As a result, the server fingerprint also changes, which is why I provided this value as a parameter. When entering the server fingerprint as a parameter in the scheduled package, I copy/pasted the value as I got it from WinSCP and got something like 'rsa 2048 xxxxxx'. The package would systematically fail upon scheduled execution and it took me some time to find root cause (see point 1). As it turns out, the SSIS package got a parameter value from the scheduled job in this format 'rsa "2048" xxxxx' (note the quotes surrounding the fingerprint key length), which WinSCP refused as it did not match the expected fingerprint format (regex expression). So while this is _NOT_ a WinSCP problem, I wanted to signal this and make my point for the change documented in point 1. If the demo code had given me the InnerException value, I would have found the root cause quicker.
For your info, all is working now as I 'danced around' this parameter issue.
thanks for considering this and if you need more info or a demo, don't hesitate
2 things when working with SSIS:
1. the source code provided in csharp for integrating winscp in SSIS package uses a try/except construct. In the exception part, the demo code simply 'spits' out the exception object, but I learned the hard way (see point 2) that the 'real' error message is in the 'innerexception' property of the exception object. I would propose to adapt the demo code to 'spit' out the e.InnerException by default.
2. I created a SSIS package with parameters, so that during development the package would connect to a test server. Once deployed on a SQL server, I scheduled a package in which I adapted (override) the default parameters, in order to have winscp upload files to production server. As a result, the server fingerprint also changes, which is why I provided this value as a parameter. When entering the server fingerprint as a parameter in the scheduled package, I copy/pasted the value as I got it from WinSCP and got something like 'rsa 2048 xxxxxx'. The package would systematically fail upon scheduled execution and it took me some time to find root cause (see point 1). As it turns out, the SSIS package got a parameter value from the scheduled job in this format 'rsa "2048" xxxxx' (note the quotes surrounding the fingerprint key length), which WinSCP refused as it did not match the expected fingerprint format (regex expression). So while this is _NOT_ a WinSCP problem, I wanted to signal this and make my point for the change documented in point 1. If the demo code had given me the InnerException value, I would have found the root cause quicker.
For your info, all is working now as I 'danced around' this parameter issue.
thanks for considering this and if you need more info or a demo, don't hesitate
- rap.png (111.47 KB)
Description: bottom part of screenshot is error message using demo code, which I got in SQL job execution log. Top part is error message I got when using InnerException property, which is more meaningful...
