February 10, 2016 at 6:45 am
I have an SSIS package that I am trying to move to our production server. It works fine on the QA server but now that I have moved it to production I am getting errors:
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".
End Error
Error: 2016-02-08 14:01:51.24
Code: 0xC020801C
Source: Data Flow Task OLE DB Destination [39652]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MYSERVER.MYDB" failed with error code 0xC0202009. There may be error messages posted before this with more
information on why the AcquireConnection method call failed.
These errors are occurring when I try to run the package from the stored procedure I created to run the package when initiated through Access.
ALTER PROCEDURE [dbo].[sp_import]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery AS VARCHAR(2000)
DECLARE @ServerName VARCHAR(200)
SET @ServerName = 'MYSERVER'
SET @sqlquery = 'DTExec /F ^"\\MYSERVER\Import\Package.dtsx^" '
EXEC master..xp_cmdshell @sqlquery
END
I should mention that the package, source file and database are all located on the same machine.
Thanks, Scott
February 10, 2016 at 6:52 am
Did you appropriately configure the connection to MYSERVER.MYDB, somehow, after moving the package?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2016 at 7:04 am
Thanks for the response Phil.
The package runs fine on the production server when I run it from SSIS. It is only when I execute the stored procedure that I get the errors. I am new to SSIS and read up on configuration file to change the settings at runtime. But if the settings are working in SSIS I am not sure why I would change them.
February 10, 2016 at 7:13 am
scotdg (2/10/2016)
Thanks for the response Phil.The package runs fine on the production server when I run it from SSIS. It is only when I execute the stored procedure that I get the errors. I am new to SSIS and read up on configuration file to change the settings at runtime. But if the settings are working in SSIS I am not sure why I would change them.
OK. May I ask why your proc defines @Servername but does not subsequently use it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2016 at 7:37 am
Sorry - I meant to remove that. There was a line of code that was commented out that I removed before posting. The procedure runs in my test environment without that or the other line of code.
February 10, 2016 at 8:13 am
OK.
The package runs fine on the production server when I run it from SSIS
Can you elaborate on exactly what you mean here - what steps are you taking?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2016 at 9:01 am
Okay - I created a package on my QA server and the package is saved to the file system. When I run the package fro SSIS or from the stored procedure it runs perfectly.
All I did was copy the package to the production server also to the file system. I changed my connection to the new server and made sure all of my tasks are using the new connection. When I run the package in SSIS it runs without issue. When I run it from the stored procedure I get the error messages. the stored procedure is a total of 3 lines so I am thinking it has to be something in the DTEXEC command.
I also tried creating a config file and using that. I changed my DTEXEC command in the stored procedure to:
SET @sqlquery = 'DTExec /F ^"\\MYSERVER\...\...\Package.dtsx^" /CONF ^"\\MYSERVER\...\...\ConfigFile.DTSConfig^"'
February 10, 2016 at 9:07 am
Scott, please will you post the connection string from the MYSERVER.MYDB connection manager?
Thanks
John
February 10, 2016 at 9:16 am
Data Source=MYSERVER;Initial Catalog=MYDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
February 10, 2016 at 9:23 am
OK, so you're using a trusted connection. That means that whatever security context the package runs under needs access to the MYDB database. Usually, if you run the package in Visual Studio or SSDT it'll run as you. If it runs from a stored procedure, it'll run as the SQL Server service account. If it runs from a job, it'll run as the SQL Server Agent account.
John
February 10, 2016 at 9:32 am
When I run the package from SSIS
I still don't know for sure what this means. I presume you mean SSDT.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2016 at 11:25 am
Both the production and QA SQL server services are logging on with the local system account.
That account is not directly mapped to the database in QA or production. Even though QA works as expected I tried mapping to the database in production but it still does not work.
February 10, 2016 at 11:43 am
Deleted post
February 10, 2016 at 11:47 am
SQL Server Integration Services
Phil Parkin (2/10/2016)
When I run the package from SSIS
I still don't know for sure what this means. I presume you mean SSDT.
February 10, 2016 at 11:56 am
scotdg (2/10/2016)
SQL Server Integration ServicesPhil Parkin (2/10/2016)
When I run the package from SSIS
I still don't know for sure what this means. I presume you mean SSDT.
Here[/url] are some of the ways I know of executing SSIS packages.
In addition, if they are stored in SSISDB, they can be executed from a stored proc.
But none of these ways is 'from SSIS'. No matter how many times you repeat it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply