Error while running SQL Agent job(SSIS) after set value in Set Values tab of package

Question

Error while running SQL Agent job(SSIS) after set value in Set Values tab of package

Kari Suresh

Hall of Fame

Points: 3712
More actions
June 25, 2010 at 7:55 am

#141690

Buddies,
I created a job with five SSIS packages in SQL Server Agent->Jobs folder. If I run the job it is working fine. And I wanted to change a package level variable called User::tempFilePath by job properties -> Steps -> package say( bulk copy) -> Set Values tab:
Property Path as \Package.Variables[User::tempFilePath].Properties[Value] and Value as D:\temp\. Then if I select Command line tab, I can see the following arguments [p]/FILE "C:\Reports\SSIS\SSIS_01\BulkCopy.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package.Variables[User::tempFilePath].Properties[Value]";"D:\temp\" /REPORTING E[/p]
If I run the job now, I'm getting the following error:[p]The argument "\Package.Variables[User::tempFilePath].Properties[Value];D:\temp" /REPORTING E " has mismatched quotes. The command line parameters are invalid. The step failed.[/p]
Can anybody suggest me how to resolve this issue..?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 1

[p]Any suggestions please.. :pinch:[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 2

[p]Or may I know how to pass value to a package level variable in SSIS Agent job..?

Any suggestions please..[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Elliott Whitlow

SSC Guru

Points: 102402

June 29, 2010 at 10:58 am

#1186537

Maybe double up the \ so it is D:\\temp

CEWII

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 4

[p]Hhhhh.. finally got it.

Thank you Mr. Elliott for your suggestion, it worked.

solution for this problem is: D:\temp\[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Elliott Whitlow

SSC Guru

Points: 102402

July 6, 2010 at 2:49 pm

#1189346

Great to hear you got it worked out.

CEWII

David Webb-220209 Valued Member Points: 53 More actions · Answer 6

I had the same issue:

"The argument "\Package.Variables[ArchivePath].Value;D:\Teamcorp\APLExtractArchive" ... has mismatched quotes. The command line parameters are invalid. The step failed."

The value set was "D:\Teamcorp\APLExtractArchive\" and the error message had the ending "\" missing from the value. My guess is that the script interpreted \" as an escape character resulting in a missing quote.

So I changed the value "D:\Teamcorp\APLExtractArchive\" to "D:\Teamcorp\APLExtractArchive\\" only doubling the ending backslash and that solved the problem.

Thanks for posting the solution Elliott!

Jim Coons Valued Member Points: 50 More actions · Answer 7

Sweeeeet...

Elliott, you are the man!

Thanks for the help,

Jim

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Elliott Whitlow

SSC Guru

Points: 102402

September 2, 2010 at 4:01 pm

#1215969

You guys are very welcome, always happy to help.

CEWII

steve block SSCertifiable Points: 7698 More actions · Answer 9

Thanks for the help! I thought maybe you had to double up all backslashes but just the last one was the key.

Steve

anuj-952895 Grasshopper Points: 13 More actions · Answer 10

Hey you are awesome Man

its Friday Midnight i was fighting this this issue your solution saved my week-end.

thanks a ton keep it up

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

Elliott Whitlow

SSC Guru

Points: 102402

July 22, 2011 at 2:50 pm

#1357308

wow, old thread, glad to be of help.

CEWII

Alan Samuel SSC Journeyman Points: 75 More actions · Answer 12

Cheers, you're still saving people

Was convinced it was spaces in a variable used for a folder path breaking stuff, but I just needed the \\ on the end.

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 13

Thanks, this thread from 7 years ago helped me out today. I have a package that has some project parameters. It ran great from visual studio... it even ran great when I deployed it to our server and set it up in a SQL job. The job/package failed when I went in to the job and overrode the project parameters (I needed to put in new values to run the package in our QA environment). What finally worked was using a double \\ at the end of the project parameter where I was setting the file source, and destination paths.

jamestow SSC Veteran Points: 211 More actions · Answer 14

And still helping after all that time. Same issue as above: package configuration is overridden in Agent Job where a slash is part of a file path. Setting the parameters correctly in the base package fixes the issue as well as adding a double slash at the end of the file path in the Agent Job.

Thanks for taking the time to share your solutions!

Error while running SQL Agent job(SSIS) after set value in Set Values tab of package

Cookies on SQLServerCentral