SSIS Job only runs on one node

Question

Post reply

SSIS Job only runs on one node

timscronin

SSCertifiable

Points: 6851
More actions
November 29, 2011 at 8:12 am

#252779

We have an active/passive cluster at a client site. SSIS installed on both nodes, packages are part of cluster resource, run as file level packages, not msdb. When on node one everything works great. However when it fails to node 2, the job fails with the packages unable to connect to the datasource (the same server, sqlexec (service level) account runs sql server and the job). I can login to the active server, open up BIDS and run the packages manually just fine. I can go to the job the and steps point to the correct location. Any one seen this before?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 1

I am having the exact issue and have been unable to solve it. Did you find a solution?

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

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

Where are the packages stored? you mentioned in the file system.. Is it in a shared location? Is it on a drive that is part of the SQL resource group? Or is it a drive on each node? Tell us a bit more about that?

CEWII

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 3

I've tried both the Filesystem and SSIS pkg store. Both throw this message: "NOTE: The step was retried the requested number of times (1) without succeeding. The step failed." No other message.

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

Are you sure that there are no other more-detailed error messages?

Do you know which step is failing? What is it trying to do?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

timscronin SSCertifiable Points: 6851 More actions · Answer 5

Odd solution, the issue was the developer had put a code break in a script. It runs in BIDS not as a job, removed the code break and it worked.

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 6

It's failing on the step that calls the ssis. No other message is thrown, which is why it's so weird.

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 7

I don't think this is my issue - no code breaks.

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

timscronin SSCertifiable Points: 6851 More actions · Answer 8

Any chance the sql agent service account does not have rights to a file share/folder necessary for the package to execute?

timscronin SSCertifiable Points: 6851 More actions · Answer 9

One of the developers reminded me there were 2 issues that fixed this. One was the code break, the other was the shared resource that was used by this process had a variable in their code that referenced a drive folder that was not a shared folder but a hardcoded drive and folder (they had about 30 variable in the package) All the variables used the shared resource, except for this one (which was used to move error files) which is why it only broke some of the time.

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

Ahh.. This is why I like to allocate a shared file drive that is placed in the same resource group as my SQL Server. I can then know that drive X: is always available to that SQL instance no matter what node it is on.

CEWII