Running a package from a sql agent that has the checkpoint file

Question

Running a package from a sql agent that has the checkpoint file

ssisnovice

SSC-Addicted

Points: 481
More actions
September 29, 2011 at 11:05 am

#243808

Hi, we are using SSIS 2005. All packages have a checkpoints and indirect configerations which is an environment variable that points to xml configeration file. When we run the packages in the file system they are running fine. But when we run the packages using a sql agent or deploy the packages to ssis package store, the packages getting failed. Can't we deploy packages to sql server or run the packages that contains checkpoints from the sql agent.
Please find the snapshot of an error in the attachment

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

According to the error message, the filename is invalid. Have you made sure the file is where the package expects it to be?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ssisnovice SSC-Addicted Points: 481 More actions · Answer 2

Hi, thanks a lot for the quick reply.

Yes, file name is stored in a variable and verified that its correct. When we run the package from file system its running fine. Package has the configerations stored in xml file. An environment variable is used to point to configeration xml file.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

nag_raj11 (9/29/2011)
Hi, thanks a lot for the quick reply.
Yes, file name is stored in a variable and verified that its correct. When we run the package from file system its running fine. Package has the configerations stored in xml file. An environment variable is used to point to configeration xml file.

Have you set the "DelayValidation" property of the connection manager that points to that file to true? If you haven't, SSIS will attempt to validate that connection before the configuration was applied.

I have also had issues before when trying to set the connection string to the log in a config file. After lots of struggling, it came down to the fact that SSIS starts logging before applying configurations. That meant that it could not connect to the database we used for logging, and the package failed.

I think the same may also be true for the checkpoint files (if I am assuming correctly that you are setting the path for the checpoint file through the configuration). SSIS may attempt to start writing to the checkpoint file before applying configurations, and seeing that your variables have no values in it at that point in time the connection fails.

Hope this helps.

Martin.

ssisnovice SSC-Addicted Points: 481 More actions · Answer 4

Hi martin,

I apologize for the late reply,

Thanks a lot

setting a delay validation true worked

Thank you