Problem
When running an SSIS package with SSIS Report Generator Task or any other SSIS connector via SQL Server Agent Job you may encounter this error (while running it locally in Visual Studio works fine):
Cause
As the error message states, the problem occurs because the path contains illegal characters in path, such as:
"(double quotes)?(question mark)*(asterisk)<(lower than symbol)>(higher than symbol)
This could be happening because:
- You simply made an error while constructing the path in a variable using an SSIS expression or a Script Task
- It’s a work of devil! I mean, a bug in SQL Server 2019 or 2022 (and Microsoft is the devil, right?)
If you have this setup, it is an act of the devil (even you think everything is cool with your parameters):
When you run the package, the MyFilename parameter value gets silently transformed and double quotes are added. Let’s display these values with SSIS Logging Task:
And this happens only because SQL Server (dtexec.exe, specifically) cannot handle \ at the end of the parameter value:
Something just goes wrong with its mind and it tries to deal with the last backslash internally.
Workaround
Simply remove the trailing backslash from the value of your path parameter:
Finally, we can live again!
Contact us
If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.