0

I have a simple SSIS package that I made in visual studio, it takes data from an Access data base and pass it to an SQL database. I can execute the package without any problems in Visual Studio 2008 but when I try to implement and execute it on a job in SQL Server 2008 it fails, I get this error message as output:

Executed as user: SRV06 \ SYSTEM . Execute Package Utility for Microsoft (R ) SQL Server Version 10.0.1600.22 for 32 -bit Copyright ( C ) Microsoft Corp 1984-2005 . All rights reserved . Started: 12:41:02 PM Error: 2014-05-30 12:41:02.59 Code: 0xC0202009 Source : Fill Complaints Connection Management " IndicadoresQuejas " Description: SSIS Error Code DTS_E_OLEDBERROR . OLE DB error . Error Code: 0x80040E4D . There is an OLE DB record is available . Source : " Microsoft SQL Server Native Client 10.0 " Result : 0x80040E4D Description: " Login failed for user ' sa' . " . End Error Error: 2014-05-30 12:41:02.59 Code: 0xC020801C Origin: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER : Data Flow OLE DB Destination [525 ] Description Task . Error of the method call AcquireConnection administrator " IndicadoresQuejas " connection . Error Code: 0xC0202009 . There may be other exposed Error messages before this with more information on why the error AcquireConnection method. End Error Error: 2014-05-30 12:41:02.59 Code: 0xC0047017 Source : Data Flow Task SSIS.Pipeline Description : Error in validating component " OLE DB Destination " ( 525 ); 0xC020801C error code was returned. End Error Error: 2014-05-30 12:41:02.59 Code: 0xC004700C Source : Data Flow Task SSIS.Pipeline Description: Failed to validate one or more components. End Error Error: 2014-05-30 12:41:02.59 Code: 0xC0024107 Source : Data Flow Task Description : There were errors during task validation . End DTExec error : package execution returned DTSER_FAILURE ( 1). Started: 12:41:02 AM Finished : 12:41:02 PM Elapsed: 0.281 seconds . Package execution failed . Unable to perform the step.

I know that it's in spanish but I hope this won't be a problem. I'll give some details about the package and the job.

In the package the "IndicadoresQuejas" is a data source that connects to the SQL Server data base, to enter this data base I've already typed and saved the user and pasword inside the package. There's another data source clled "Quejas" that gets the data from the Acces data base, this one doesn't need user and password. There's an OLDB connection named SRVXX\BI2008.IndicadoresQuejas.sa

The SSIS package has Prtection Level of EncryptSensitiveWithPassword

About the Job, it has jusy one step called "Llenar Quejas" this one executes the SSIS package but as I mentioned it fails. I've alredy enter the job properties and edited the job's step, it has the type of "SQL Package Server Integration Services", the "execute as" is "Service Account of SQL Server Agent" the package origin is "system file", In execution options everything is unchecked, the SQL Server 2008 is 32 bits as well as Visual Studio. The command line is:

/FILE "F:\Proyectos\Indicadores Quejas\SSIS Quejas\Llenar Quejas.dtsx" /DECRYPT /CONNECTION IndicadoresQuejas;"\"Data Source=SRVXX\BI2008;User ID=sa;Initial Catalog=IndicadoresQuejas;Provider=SQLNCLI10.1;Persist Security Info=True;\"" /CONNECTION Quejas;"\"Dsn=Quejas;\"" /CONNECTION "SRVXX\BI2008.IndicadoresQuejas.sa";"\"Data Source=SRV06\BI2008;User ID=sa;Initial Catalog=IndicadoresQuejas;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{B52933E5-68A2-4A0E-8A60-8F905810CF31}SRVXX\BI2008.IndicadoresQuejas.sa;\"" /CHECKPOINTING OFF /REPORTING E

In the data sources the data sources of the package (Quejas, and Indicadores Quejas) and the OLDB connection(SRVXX\BI2008.IndicadoresQuejas.sa) are checked.

I've searched on the internet about this problem, but nothing has helped me, and I've visited the Microsoft support page as well but nothing.

I know that the problem is that it's not connecting to "IndicadoresQuejas" correctly, and I've checked several times that the users and passwords are correct. But I really don't know what's wrong. I've also checked the ODBCs and everything looks fine.

I've also tried to import the package to the MSDB folder of the Integration Services in SQL server but I've been told that from all the other jobs that had been done, importing packages to that folder has never been necessary.

I don't really expect to get the actual solution here, but if you could give me at least a clue of what I should be checking or reviewing.

I hope that the Spanish messages and names will not be a problem. If more details are needed I'll provide them. Thanks

billinkc
  • 59,250
  • 9
  • 102
  • 159
Luis Kråke
  • 11
  • 4
  • The error, Error de inicio de sesión del usuario 'sa' / Login failed for user' sa indicates that you're having connection issues. The package using encryptWithPassword could be failing to decrypt due to a bad password. Since you also have DSNs in the mix, those come in two different flavors - 32 and 64 bit. The error message indicates it was executed in 32 bit mode so it could just be that you need to use the 64bit version to match your ODBC provider. The quick test is to run odbcad32.exe from the two directories http://support.microsoft.com/kb/942976 and compare the 32 to 64 bit – billinkc May 30 '14 at 20:55
  • The other quick way to test the 32 vs 64 bit is to log on to the server and run the package using the different dtexec See http://stackoverflow.com/questions/13088974/running-an-ssis-package-using-dtexec/13092260#13092260 – billinkc May 30 '14 at 21:05
  • I guess when you scheduled it you typed the package password in? There are ways to connect to SQL Server / MS Access that do not require a DSN OR a password, and this can simplify these kinds of issues because there are a lot less moving parts. – Nick.Mc Jun 01 '14 at 22:39
  • Any update on whether this is a 32 vs 64 bit issue? – billinkc Jun 03 '14 at 03:15
  • Personally I run any package using any drivers besides MS SQL in 32-bit mode, so that the design environment matches runtime. IMO the performance hit is not worth the config/setup headaches. The whole 32 vs 64 bit mess is a massive fail by Microsoft IMO! – Mike Honey Jun 03 '14 at 03:23
  • Sorry for the delay! I've checked the drivers everything runs at 32,and it's not the first time that we've done jobs using these ODBCs, most of them use the same, but this one is the only one that uses an Acces ODBC, but it's also 32bit, but from what I can see that one it's not the problem, the one that's causing problems is the one that connects to SQL server. – Luis Kråke Jun 03 '14 at 15:21

0 Answers0