package doesn't run when moved to production server

Question

Post reply

package doesn't run when moved to production server

Viewing 6 posts - 16 through 20 (of 20 total)

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

scotdg Right there with Babe Points: 771 More actions · Answer 1

Thanks - I am familiar with the different ways to run the package.

What I mean is if I go to the development environment (SSIS/Visual Studio) and run the package it works in Prod and QA.

If I run it from the Stored Procedure using the DTEXEC utility in QA it works but not in prod.

EDIT: Sorry - I guess I should be calling it BIDS

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".

Is your SSIS package attempting to make connection to a server other than where it is running locally. The Named Pipes Provider only supports local (non network) connections.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

scotdg Right there with Babe Points: 771 More actions · Answer 3

No - it is on the same machine. That machine also has only TCP/IP enabled currently. I tried enabling Named Pipes so it was consistent with my QA machine and received the same errors.

P Jones SSChampion Points: 12361 More actions · Answer 4

It sounds like the account that runs it as a job (the sql agent service account) does not have permission to access the MyServer MyDB database.

You need to give it permissions to read the data and also windows permissions to any folders and files accessed in the package such as access db or excel sheets or flat files.

When you personally run the package from the data tools - what you call SSIS, it uses your account credentials so you have permission and the package runs.

It is also usual to give the agent account db_ssisadmin permission to msdb in the user mapping section.

bark stuff SSC-Addicted Points: 475 More actions · Answer 5

Often running a package in 64 bit mode will throw that error. If your package runs locally, check whether it is running 64 bit or 32 and ensure it is set in production accordingly.

scotdg Right there with Babe Points: 771 More actions · Answer 6

I am running it from a stored procedure, not a job so I think it is using the SQL Server service, not the agent service.

I did notice that the package in BIDS had the CreatorComputerName set to the QA machine and the CreatorName set to my username. Once I changed that to the production server and the system account many of the errors were removed but they were replaced by the following which I am investigating now.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.".