Kimball SCD, Excel Connections in SQL Job 64bits

Topics: Issues 3: Executing the Component (Run-Time)
Mar 17, 2011 at 10:48 AM
Edited Mar 17, 2011 at 10:50 AM

Hi guys,
I have a problem 'deploying' a SSIS Package as SQL Job, i developped a ssis package that contains excel connections and a Kimball SCD. When I try to create a SQL Job i get an error if a execute the job in 32 bit runtime : 

 


Executed as user: XXXXXXXXX. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:54:06 AM  Error: 2011-03-17 00:55:26.13     Code: 0x00000000     Source: DIM_Employee SCD Any changes? (EmployeeIDCW, FirstName, LastName, Gender, Language, TypeDiploma, LabelDiploma, CategoryDiploma, Trigram, EmployerLabel, WorkRate, StartDate, BirthDate)     Description: A problem occurred reading the metadata for the Any changes? (EmployeeIDCW, FirstName, LastName, Gender, Language, TypeDiploma, LabelDiploma, CategoryDiploma, Trigram, EmployerLabel, WorkRate, StartDate, BirthDate) component: Unable to generate a temporary class (result=1).  error CS2001: Source file 'C:\WINDOWS\TEMP\ps0zirv-.0.cs' could not be found  error CS2008: No inputs specified    Metadata will be regenerated, but some settings may be lost.  If you also receive an error message stating the component is 'Unable to generate a temporary class', then you may need to grant the account executing the package rights to the Windows Temporary folder.  End Error  Error: 2011-03-17 00:55:26.15     Code: 0x00000000     Source: DIM_Employee SCD Any changes? (EmployeeIDCW, FirstName, LastName, Gender, Language, TypeDiploma, LabelDiploma, CategoryDiploma, Trigram, EmployerLabel, WorkRate, StartDate, BirthDate)     Description: Component properties could not be read (Unable to reinitialize meta data.)  End Error  Error: 2011-03-17 00:55:26.15     Code: 0xC004706B     Source: DIM_Employee SCD SSIS.Pipeline     Description: "component "Any changes? (EmployeeIDCW, FirstName, LastName, Gender, Language, TypeDiploma, LabelDiploma, CategoryDiploma, Trigram, EmployerLabel, WorkRate, StartDate, BirthDate)" (12204)" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2011-03-17 00:55:26.15     Code: 0xC004700C     Source: DIM_Employee SCD SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-03-17 00:55:26.15     Code: 0xC0024107     Source: DIM_Employee SCD      Description: There were errors during task validation.  End Error  Error: 2011-03-17 00:55:26.15     Code: 0xC00220E4     Source: Execute Package Data Import      Description: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.  .  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:54:06 AM  Finished: 12:55:26 AM  Elapsed:  79.484 seconds.  The package execution failed.  The step failed..




And if I execute the SQL Job in 64 bits i get the error:

 


Executed as user: XXXXXXXX. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:01:05 AM  Error: 2011-03-17 01:02:22.39     Code: 0xC00F9304     Source: Data Import Release Connection manager "Jours Ouvrables"     Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.  End Error  Error: 2011-03-17 01:02:22.39     Code: 0xC020801C     Source: IN_WorkingDays IN_JoursOuvrables [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Jours Ouvrables" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2011-03-17 01:02:22.39     Code: 0xC0047017     Source: IN_WorkingDays SSIS.Pipeline     Description: component "IN_JoursOuvrables" (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2011-03-17 01:02:22.39     Code: 0xC004700C     Source: IN_WorkingDays SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-03-17 01:02:22.40     Code: 0xC0024107     Source: IN_WorkingDays      Description: There were errors during task validation.  End Error  Error: 2011-03-17 01:02:22.40     Code: 0xC00220E4     Source: Execute Package Data Import      Description: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.  .  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:01:05 AM  Finished: 1:02:22 AM  Elapsed:  76.75 seconds.  The package execution failed.  The step failed.


 

So, My question is, It exists the possibility of create a SSIS Package Job with Kimball SCD and Excel Connection that works in a SQL Job???? 

My configuration is:Windows Server 2003 64bitVisual Studio 2008 

Thanks!


Coordinator
Apr 8, 2011 at 7:52 PM

The problem with executing in 64-bits is solely related to the Excel provider.  You'd probably get the same error with this component in 64-bits - it's just that the Connection Managers get validated first, so the package load stops at that point.

If you dig through your first error there, it says it was "Unable to generate a temporary class".  Later in the message, it says "If you also receive an error message stating the component is 'Unable to generate a temporary class', then you may need to grant the account executing the package rights to the Windows Temporary folder"  Try that.