Question

Cameronh on Wed, 02 Jan 2013 02:08:28


Hi All,

Setup:

Server: Windows 2008 R2

SQL: Microsoft SQL Server Management Studio                        10.50.4000.0
       Microsoft Analysis Services Client Tools                            10.50.4000.0
       Microsoft Data Access Components (MDAC)                      6.1.7601.17514
       Microsoft MSXML                                                         3.0 4.0 6.0
       Microsoft Internet Explorer                                            9.0.8112.16421
       Microsoft .NET Framework                                             2.0.50727.5466
       Operating System                                                        6.1.7601

Visual Studio: 2008 3.5 SP1

I have created a package that loads the contents of a flat file in to a SQL table. When I run i execute package in visual studio(run as administrator) it works correctly. But when I try running it from a job I get an error message.

"  Description: The version of component "SQL Server Destination" (21) is not compatible with this version of the DataFlow. " ......(there's more information)

I tired running SSMS as administrator as well but this didn't make a difference. We are currently in test phase so I can keep running it manually but I need to find a solution so the job can scheduled. From what I can see on the net is there maybe a versions issues but SSMS and VS are 2008, so is this still the problem?

Thanks for helping.


Sponsored



Replies

Cameronh on Wed, 02 Jan 2013 03:45:48


Full Error Message:

Message
Executed as user: domain\sqlservice. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  7:20:47 AM  Error: 2013-01-02 07:20:48.41     Code: 0xC0048020     Source: Data Flow Task Data Flow Task (SSIS.Pipeline)     Description: The version of component "SQL Server Destination" (21) is not compatible with this version of the DataFlow.  End Error  Error: 2013-01-02 07:20:48.42     Code: 0xC0048020     Source: Data Flow Task SSIS.Pipeline     Description: The version of component "SQL Server Destination" (21) is not compatible with this version of the DataFlow.  End Error  Error: 2013-01-02 07:20:48.42     Code: 0xC004706C     Source: Data Flow Task SSIS.Pipeline     Description: Component "component "Copy Column" (261)" could not be created and returned error code 0x80070005. Make sure that the component is registered correctly.  End Error  Error: 2013-01-02 07:20:48.42     Code: 0xC0048021     Source: Data Flow Task SQL Server Destination [21]     Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "SQL Server Destination;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;5".  End Error  Error: 2013-01-02 07:20:48.42     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component "SQL Server Destination" (21) failed validation and returned error code 0xC0048021.  End Error  Error: 2013-01-02 07:20:48.42     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-01-02 07:20:48.42     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  7:20:47 AM  Finished: 7:20:48 AM  Elapsed:  1.391 seconds.  The package execution failed.  The step failed.

COZYROC on Wed, 02 Jan 2013 20:42:46


Can you try to execute the package in 32bit mode? Report back the result.

Cameronh on Wed, 02 Jan 2013 23:19:20


I have tried using the 'Use 32 bit runtime' option under execution options but then it comes up with a different error.

Message
Executed as user: HKDC\sqlservice. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:24:01 AM  Error: 2013-01-03 04:24:06.46     Code: 0xC000F427     Source: Data Flow Task SSIS.Pipeline     Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:24:01 AM  Finished: 4:24:06 AM  Elapsed:  4.765 seconds.  The package execution failed.  The step failed.

Well clearly SSIS is installed so why does it say this?

I checked services.msc and there didn't seem to be a SSIS service?

COZYROC on Wed, 02 Jan 2013 23:22:18


I have tried using the 'Use 32 bit runtime' option under execution options but then it comes up with a different error.

Message
Executed as user: HKDC\sqlservice. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:24:01 AM  Error: 2013-01-03 04:24:06.46     Code: 0xC000F427     Source: Data Flow Task SSIS.Pipeline     Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:24:01 AM  Finished: 4:24:06 AM  Elapsed:  4.765 seconds.  The package execution failed.  The step failed.

Well clearly SSIS is installed so why does it say this?

I checked services.msc and there didn't seem to be a SSIS service?

I don't see how it is clear SSIS is installed. In fact I do believe you are running some kind of SQL Server Express edition on this machine, where most of the SSIS functionality is not available. I would recommend you do re-install of the SSIS standard edition or higher.

Cameronh on Thu, 03 Jan 2013 01:06:03


as I said at the start the server has 2008 R2 and visual studio 2008, why doesn't this include a SSIS service? shouldn't it be included as part of the Visual Studio installation?

COZYROC on Thu, 03 Jan 2013 01:17:11


as I said at the start the server has 2008 R2 and visual studio 2008, why doesn't this include a SSIS service? shouldn't it be included as part of the Visual Studio installation?
SSIS is part of the SQL Server installation. It is a separate option you have to select during the installation process.

GSS138 on Thu, 03 Jan 2013 03:03:45


SSIS is part of SQL server install, but you do not need SSIS server installed to run an SSIS package on any computer as it can be executed with DTEXEC.exe without integration services installed.

The error at first seems like a versioning problem, so you might try reinstalling Visual studio to get the current version of the components.  I would do this first-then get any service packs.  If your dev environment has a different component version than your runtime/liveenvironment there will be a problem.  However if this package is the only package having this problem, then it is most likely not the issue.

The second part of that error message implies some sort of build/runtime validation of the component which has more to do with schema validations when a dtsx package builds.  try this, on the production/runtime server, open BIDS.  Import the file you are trying to run into a SSIS project.  Does it run from the IDE on the production server?  Also, are you using he OLE or SQL DB Native adapter for the destination connection?  I have seen problems with the SQL native adapter from dev IDE to production runtime.  I recommend the OLE DB connector.

Cameronh on Thu, 03 Jan 2013 04:34:12


The jobs are now working.

1. Install SSIS service
2. In SSIS project>properties set Run64BitRunTime to False
3. In Job Step>execution options select Use 32 bit runtime
4. In the drive folders holding excel files gave full access to the account running the jobs.


Many thanks for the replies.