Package does not run from stored procedure

SSIS and Import/Export
1

I am not sure this should be in this category or not but here is my issue.

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^" ' --SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"' 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

2

You aren't using @ServerName in your command. Use /Server parameter.

Run PRINT @SQLQuery to view the command you built.

3

I should have commented that out. All I really need are these three lines which is how I ran it on my QA server.

DECLARE @SQLQuery AS VARCHAR(2000) SET @SQLQuery = 'DTExec /F ^"\\MYSERVER\Import\Package.dtsx^" ' EXEC master..xp_cmdshell @SQLQuery

4

So have you tried adding /Server to your command to see if that's what will make it work on production?

Is production a named instance and QA is a default instance?

5

The part you had commented out didn't specify /Server.

6

They are both named instances on separate machines.

Do I need the /server in the dtexec command if it is running from the same server?

7

Is QA using port 1433 where it won't matter if it's a named instance? Or an alias exists to redirect MYSERVER to MYSERVER\INSTANCENAME?

Add the /Server parameter and see if it works. I bet that's your issue.

8

Port 1433 is being used on both machines.

I am new to this and I am not sure how to use the /server option. My package is in the file system and I cannot use the /F option with /Server. If I use just /Server I obviously get an error because it thinks my file path is a dtexec option.

9

Sorry, I totally misread your command. You're right that you wouldn't specify /Server when using /F option.

So now you've got to check the connection managers in the package and verify that those do successfully connect from the database server's perspective. Or use the /conf option to use runtime values instead of what's configured inside the package. This is helpful when your package is pointing to a test server, and you want the connection manager to point to a production server.

10

I will check that option, thanks.

The connection in SSIS to the production server works.

11

I just read your post again. If I already have the package on the production server and it runs okay from within SSIS on that server, will using the /conf option do anything? I understand how it would be beneficial on a Dev server so you can create your package pointing to the production server in connection manager and then override it with the config file. But I already have it in prod and the connection works in SSIS.

Any resource on how to properly form the XML to send the server name and file location. I assume I would use both and in my stored procedure just run dtexec /conf?

12

Try modifying the package's connection manager to use a higher connection timeout.

I don't have any resources regarding the config file. I've always loaded the packages into SQL Server rather than calling the dtsx files.With the packages in SQL Server, I can overrride parameters/variables/connections in a SQL Agent job very easily.

13

Thanks, I tried but with no success.

14

And just for the record, I also tried the config file without success.

Thanks again for your help.

15

Maybe a named pipes issue?

I'm really out of ideas.

16

I appreciate the help but I did try enabling named pipes on the machine and it didn't help.

I also checked the login for the service and it is using the local account. I tried mapping that account to the database (even though it was not mapped on QA) to no avail of course.

Thanks again.

Anyone else have any ideas?

17

Maybe a bug?

18

I saw that but dismissed it since I wasn't having the problem on the QA machine. I will look it over again.

19

I tried changed the provider to SQLOLEDB.1 without any success.

I did notice that the creator machine and creator name were still set to the QA machine and my network username. I changed them to the production machine name and system account and now I get the following:

   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
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.".
End Error
Error: 2016-02-11 11:10:57.33
   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.

I also imported the package to the file system package store and tried running it using:

exec master..xp_cmdshell 'DTExec /dts "\File System\AGT_Package"'

Anyone have any other ideas?