January 21, 2023 at 7:39 pm
I am in the process of migrating my SSIS packages from SQL Server 2016 to 2019. This is generally a pretty tedious, but fairly simple process. I am taking the time to move my SQL connections from the classic SQL Native Client driver to the new SQL Server OLE DB driver so I have to touch each package and redeploy to the new server. I have several packages that read in downloaded flat files from a number of network directories. My process is to create a Flat File and a Database Connection Manager for my package. I use a Foreach Loop Container to process multiple files at a time. I then configure the Flat File connection string to use a package parameter with the UNC path to the source directory and concatenate the file name provided by the Foreach Loop Container. It is not uncommon to have spaces in the UNC path.
I make my package changes in VS. Test the package to ensure that it is connecting to the database using the new driver and is processing the package correctly. Everything is working great. I'm deploying the packages to my new SQL Server 2019 server. I am also migrating my SQL Agent Jobs to the new server and executing them to ensure that they are working as expected. All is going fine until one of the jobs fails with a message like the following.
Executed as user: <Your User>. Microsoft (R) SQL Server Execute Package Utility Version 15.0.4236.7 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. The argument "FileProcessedDirectory;"\\Some UNC path\with spaces\ /Par "$ServerOption::LOGGING_LEVEL(Int16)";1 /Par "$ServerOption::SYNCHRONIZED(Boolean)";True /CALLERINFO "SQLAGENT" /REPORTING "E" " has mismatched quotes. The command line parameters are invalid. The step failed.
This perplexed me as this is not the first flat file package that I had migrated. After some searching on the internet I found posts relating to the error message I was getting and file paths that contained spaces in them. I verified that the path did indeed have spaces. What did not make sense to me is that the package was running fine on my SQL 2016 server. Many of the posts were older, but I was game for trying the posted solutions to see if it helped me. Most of the posts were focused on command line release characters (\") for double quotes to surround the file path. Other recommendations were to override the Command Line command in the SQL Agent Job step. That tab no longer exists in the SSIS job task dialog.
My internet searches sent me down many wrong paths and I had to resort to trial and error. Considering that the release characters contain a backslash I decided to remove the terminating backslash on my file path parameter in my package. This allowed the SQL Agent Job to execute the SSIS package, but the package failed as the file path was incorrect. This led me to the deduction that the SQL Agent Job was unable to properly process file path parameters with a terminating back slash in SQL Server 2019, but it was able to do so in 2016.
I modified my package parameter (@[$Package::FileImportDirectory]) value to "\\Some UNC path\with spaces" (No terminating backslash)
In my File Connection Manager I changed the Connection String expression from @[$Package::FileImportDirectory] + @[User::FileName] to @[$Package::FileImportDirectory] + "\\" + @[User::FileName] to add the necessary backslash in the file path.
I also had a File System task that uses a package parameter to define an archive directory to move the processed files to. I had to make a similar adjustment to that path expression as well. @[$Package::FileProcessedDirectory] + "\\" + @[User::FileName]
After making these simple changes to my package and removing the terminating backslash from the parameter fields in my SQL Agent Jobs, my packages now execute successfully in VS as well as in a SQL Agent Job.
I hope that this post saves someone the research and testing time that I had to go through to get this working in my environment.
January 22, 2023 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply