asked
SSIS Package is failing
My Packages are in my development machine i want to execute them in our production SQL server, i.e. i want to map my network drive and execute the packages in stored procedures
Did you move the packages to the production SQL Server? Did you map the network drive on the production SQL Server? Did you execute the package from a stored procedure on the Production SQL Server? Without knowing more, my first guess is that it's the mapped network drive. That's a very common place to get tripped up (the drive is often mapped with your user account so the SQL Server service account can't use it). Instead of a mapped drive, use an UNC path `\\server\share\path\etc...` Second guess is permissions. The SQL Server service account doesn't have permissions to something on the shared drive, or the account executing the package doesn't have the appropriate permissions to run the package. Are you calling the package via `xp_cmdshell` or through a job? Either way the account that executes the procedure needs the appropriate permissions, either directly assigned or, maybe, via `execute as`.
I have SSIS package which are getting executed on BI and also from CMD prompt. I use DTSEXE command in CMD prompt. I don't have any issues with that. When i execute the same package from a stored procedure I'm not able to execute the package.The stored procedure also used the same DTSEXEC cmd. here is the error message extract i get ------------------------------------------------------------------------------- Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. NULL Started: 5:35:43 PM Error: 2013-04-23 17:36:06.09 Code: 0xC0202009 Source: Package Connection manager "ORACLEConnectionString" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12154: TNS:could not resolve the connect identifier specified". End Error Error: 2013-04-23 17:36:06.09 Code: 0xC020801C Source: IMPORT_PRODUCT_URL ORACLE Source [174] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ORACLEConnectionString" failed with error code 0xC0202009. There may be error messages posted before this wi th more information on why the AcquireConnection method call failed. End Error Error: 2013-04-23 17:36:06.09 Code: 0xC0047017 Source: IMPORT_PRODUCT_URL SSIS.Pipeline Description: component "ORACLE Source" (174) failed validation and returned error code 0xC020801C. End Error Error: 2013-04-23 17:36:06.09 Code: 0xC004700C Source: IMPORT_PRODUCT_URL SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-04-23 17:36:06.09 Code: 0xC0024107 Source: IMPORT_PRODUCT_URL Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:35:43 PM Finished: 5:36:06 PM Elapsed: 22.719 seconds NULL -------------------------------------------------------------------------------
Does the SQL Server service account have permission to read the tnsnames.ora file (if you're using one)? 32-bit vs 64-bit DTEXEC? Try specifying the full path to the executable in your command Here is some troubleshooting information for this oracle error:
http://ora-12154.ora-code.com MSDN blog covering this error from the SQL side (it focuses on linked servers, but much of the information will apply to SSIS as well):
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/06/30/ora-12154-tns-could-not-resolve-the-connect-identifier-specified-error-while-creating-a-linked-server-to-oracle.aspx
Run the code below to execute the package in a stored procedure: EXEC master..xp_cmdshell 'DTEXEC.exe /SQL "PackageName" /SERVER SERVERNAME'
15 People are following this question.
