SSIS 2005 package developed with VS 2008

Discussion:

(too old to reply)

Tagwim

2009-12-08 15:18:01 UTC

I have an instanace of SQL 2005 and 2008 residing on the same box. Most of
our data resides on SQL 2005. I am using VS 2008 to maintain and develop all
SSIS packages (2005 and 2008). I import the dtsx package into SQL 2005, If I
run it from the SSIS package it works fine. If I schedule it in a JOB I get
the following error message;

Executed as user: LW_SQL_01\SYSTEM. Microsoft (R) SQL Server Execute Package
Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 10:17:05 AM Could not load
package "\MSDB\FTPBpe" because of error 0xC001000A. Description: Exception
of type 'Microsoft.SqlServer.Dts.Server.PackageNotFoundException' was thrown.
Source: MsDtsSrvr Started: 10:17:05 AM Finished: 10:17:05 AM Elapsed:
0.172 seconds. The package could not be loaded. The step failed.

Can anyone help me? or am I beyond help?

Thank you in advance for any insight into this issue.

Todd C

2009-12-08 18:34:01 UTC

Permalink

SSIS Packages designed in VS 2008 CANNOT be run in VS 2005 or installed in
SQL 2005 instances.

However, packages desinged in VS 2005 CAN be installed in SQL 2008. But if
you open the package in VS 2008, it will want to upgrade the package, making
it un-usable for 2005 versions.

This is NOT like Office 2007 program like Word that had the ability to save
the .docx file in backward compatibility mode as a .doc file.

My suggestion: do one of the following:
*Upgrade all your instances to 2008, or
*Do all your designing of SSIS (as SSAS) in Visual Studio 2005

HTH
=====
Todd C

Post by Tagwim
I have an instanace of SQL 2005 and 2008 residing on the same box. Most of
our data resides on SQL 2005. I am using VS 2008 to maintain and develop all
SSIS packages (2005 and 2008). I import the dtsx package into SQL 2005, If I
run it from the SSIS package it works fine. If I schedule it in a JOB I get
the following error message;
Executed as user: LW_SQL_01\SYSTEM. Microsoft (R) SQL Server Execute Package
Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp
1984-2005. All rights reserved. Started: 10:17:05 AM Could not load
package "\MSDB\FTPBpe" because of error 0xC001000A. Description: Exception
of type 'Microsoft.SqlServer.Dts.Server.PackageNotFoundException' was thrown.
0.172 seconds. The package could not be loaded. The step failed.
Can anyone help me? or am I beyond help?
Thank you in advance for any insight into this issue.

Tagwim

2009-12-08 19:59:02 UTC

Permalink

Thank you Todd.

Another thought? If I can run them manually on the 2005 instance, why can't
I run them through a job?

Post by Todd C
SSIS Packages designed in VS 2008 CANNOT be run in VS 2005 or installed in
SQL 2005 instances.
However, packages desinged in VS 2005 CAN be installed in SQL 2008. But if
you open the package in VS 2008, it will want to upgrade the package, making
it un-usable for 2005 versions.
This is NOT like Office 2007 program like Word that had the ability to save
the .docx file in backward compatibility mode as a .doc file.
*Upgrade all your instances to 2008, or
*Do all your designing of SSIS (as SSAS) in Visual Studio 2005
HTH
=====
Todd C

Todd C

2009-12-09 13:54:01 UTC

Permalink

Let me get this straight:
You create an SSIS package using Visual Studio 2008, then deploy it to SQL
Server 2005 Integration Services instance, and are able to run it from there?

Or, when you say you "run them manually on the 2005 instance" do you mean
you are executing the package from Visual Studio 2005?

I kind of suspect the latter.

If your packages are deployed to any instance of SSIS, but they won't run,
it is most likely permissions related.
*If the Connection Managers use Windows Integrated Security, then most
likely the Service Account for SQL Agent does not have the required
permissions to access the resource (SQL Database, Network file share, etc).
*If you have Connection Managers that use saved passwords, and did NOT
change the Package Protection Level from its default of Save Sensitive with
user Key, then SQL Agent cannot decrypt the saved password.
If the latter, read up on SSIS Configurations at
http://toddchitt.wordpress.com/

Good luck.
=====
Todd C
MCTS SQL Server 2005

Post by Tagwim
Thank you Todd.
Another thought? If I can run them manually on the 2005 instance, why can't
I run them through a job?

Tagwim

2009-12-09 20:01:03 UTC

Permalink

Hi Todd,

This gets so confusing. OK Here I go.

Using Integration Services 10.0.1600 (Is this SQL 2008 Integration Services?)
I have my packages deployed here to interact with SQL Server 9.0.4035 (SQL
2005).

So you may be correct that it's a permission's issue.

If this is true then you have helped me more then you will ever realize and
I thank you for it.

Post by Todd C
You create an SSIS package using Visual Studio 2008, then deploy it to SQL
Server 2005 Integration Services instance, and are able to run it from there?
Or, when you say you "run them manually on the 2005 instance" do you mean
you are executing the package from Visual Studio 2005?
I kind of suspect the latter.
If your packages are deployed to any instance of SSIS, but they won't run,
it is most likely permissions related.
*If the Connection Managers use Windows Integrated Security, then most
likely the Service Account for SQL Agent does not have the required
permissions to access the resource (SQL Database, Network file share, etc).
*If you have Connection Managers that use saved passwords, and did NOT
change the Package Protection Level from its default of Save Sensitive with
user Key, then SQL Agent cannot decrypt the saved password.
If the latter, read up on SSIS Configurations at
http://toddchitt.wordpress.com/
Good luck.
=====
Todd C
MCTS SQL Server 2005

Post by Tagwim
Thank you Todd.
Another thought? If I can run them manually on the 2005 instance, why can't
I run them through a job?

Tagwim

2009-12-10 13:10:01 UTC

Permalink

How would I set the permissions for the following?

SQL 2008 Integration Services (2008 SSIS package)
Job Setup on SQL 2005 instance.

Todd C

2009-12-11 13:59:01 UTC

Permalink

I saw your other post on this subject, but we'll keep this thread alive.

Permissions may be the least of your worries here. The account will need
membership in one of the roles in the MSDB database of the machine that hosts
the SSIS instance. Those roles being db_ssisoperator, db_ssisltduser, and
db_ssisadmin).

I bet you're going to have issues with the versions:
I know you can get SQL Agent to kick off packages on another *server*, but I
have never had the opportunity or the need to try it if they are different
*versions*.

I know if you are running DTEXEC.exe command line utility and are executing
a package on another machine, then it needs the version 10 (aka SQL 2008) of
DTEXEC. Not sure if SQL Agent would need the same, but I'm guessing so.

Go ahead and try it, what do you have to lose?
--
Todd C
MCTS SQL Server 2005

Post by Tagwim
How would I set the permissions for the following?
SQL 2008 Integration Services (2008 SSIS package)
Job Setup on SQL 2005 instance.