Error Executing, SSIS Packages Coppied from another server

Hello All,

I am working on an application used to move packages (SSIS Packages) from one server to another.The packages are saved under MSDB folder.

Case: When the application is running on my system, i try to copy packages created from another server to my Server.I am using package protection level as "Server Storage". When i try to execute the coppied package from my system, it is giving me error.
An oledb error has occured, Error Code 0x80040E4D, An Oledb record is available, Source : Microsoft OLedb Provider for Sql Server", Description : "Login failed for sa".

Case : When the Package Coppier application is running on my system, i try to copy packages created on my server to my another Server.I use the same package protection level as "Server Storage". When i try to execute the copied package on destination server it is working fine without errors.

Please guide me on this issue, as soon as possible

Thanks And Regards
Subin

Make sure all the permissions are properly set on your server.

DId you read this (http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx)

ServerStorage. No encryption is added to the package. Instead the package’s contents are secured based on the database’s object access security. If the ServerStorage value is used, the package must be saved to SQL Server in the sysdtspackages90 table in the msdb database. It cannot be saved to the file system.

The SSIS package security model is also extensible. There is a Sensitive attribute in the XML schema of an SSIS package that controls access to the package’s properties. For example, if the Sensitive attribute is set to 1, when the user opens the package all sensitive data will be removed. Developers can incorporate this Sensitive attribute to get the same type of protection for their SSIS custom components.

If you are using the ServerStorage ProtectionLevel property, the method of controlling access to the packages saved in the database is by using SQL Server Database roles. By default, SQL Server 2005 provides the following roles for SSIS package management . You can find these by opening SQL Server Management Studio, then expanding the msdb database, Roles, Database Roles node:

?

db_dtsadmin. SSIS package administrator rights.

?

db_dtsltduser. Rights to execute only the SSIS packages the user has been given permission for.

?

db_dtsoperator. Operation rights to SSIS packages including the ability to run as well as backup and restore packages.

You can also create your own custom database roles for SSIS Package management. Add the appropriate users to those roles and then assign those roles to your own SSIS packages.

You can enable package roles using the SQL Server Management Studio by right-clicking a saved package and then selecting the Package Roles option on the shortcut menu as is shown in Figure 11

BTW, is there any reason for not using SSMS to import/export the packages (http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx)

Error executing SSIS package

Hello,
I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
created several SSIS packages and run them under proxy account. At first
they worked just fine, but then all of them started to fail with the error
messages like this one:
Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
NonTransactableSql Execute SQL Task Description: An error occurred with
the following error message: "Could not find file 'I:\Users\Peter
Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
step failed.
If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
deleting my tmp files periodically, so I don't have these files. It's hard
to believe that SQL Server relies on tmp files that are often deleted. Plus,
I was running the same packages on Windows XP for over a year and never had
problems like this.
What else could it be and how to fix it? Is it a security issue?
I would appreciate your suggestions very much.
Thank you,
Peter Afonin
Hi Peter
"Peter Afonin" wrote:

> Hello,
> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
> created several SSIS packages and run them under proxy account. At first
> they worked just fine, but then all of them started to fail with the error
> messages like this one:
> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
> NonTransactableSql Execute SQL Task Description: An error occurred with
> the following error message: "Could not find file 'I:\Users\Peter
> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
> step failed.
> If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
> deleting my tmp files periodically, so I don't have these files. It's hard
> to believe that SQL Server relies on tmp files that are often deleted. Plus,
> I was running the same packages on Windows XP for over a year and never had
> problems like this.
> What else could it be and how to fix it? Is it a security issue?
> I would appreciate your suggestions very much.
> Thank you,
> --
> Peter Afonin
>
I would suspect that the issue is probably with 'I:\Users\Peter
Afonin\AppData\Local\Temp' not existing where is your temp directory set to?
John
|||Hi John,
No, this directory is real. This is a default directory in Vista (I have it
on partition I).
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John
|||John, I can confirm that SQL Server is indeed looking for these tmp files.
1. I've recreated my packages, they worked fine.
2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
The packages stopped working, giving me the error I mentioned before.
3. I put these tmp files back to this folder, the packages work fine again.
This is something that I've never heard before and that doesn't make any
sense. Why this never happened on Windows XP, only Vista? What should I do -
it looks like I cannot cleanup my computer anymore?
As a last resort - can I change the folder for tmp files that are needed for
SSIS packages execution, so I could exclude it somehow from the disk
cleanup?
Any advice would be greatly appreciated.
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John
|||Hi Peter
"Peter Afonin" wrote:

> John, I can confirm that SQL Server is indeed looking for these tmp files.
> 1. I've recreated my packages, they worked fine.
> 2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
> The packages stopped working, giving me the error I mentioned before.
> 3. I put these tmp files back to this folder, the packages work fine again.
> This is something that I've never heard before and that doesn't make any
> sense. Why this never happened on Windows XP, only Vista? What should I do -
> it looks like I cannot cleanup my computer anymore?
> As a last resort - can I change the folder for tmp files that are needed for
> SSIS packages execution, so I could exclude it somehow from the disk
> cleanup?
> Any advice would be greatly appreciated.
> Thank you,
> Peter
>
SSIS may use files in a users temporary directory, if they have been
automatically generated or converted. You will need to check the datasources
and the package steps to see what they are. If necessary they can be moved to
a "common" directory that has the correct permissions for all users that will
run the package.
You may have found that removing the equivalent files in windows XP would
have had a similar result.
John
|||Thank you, John, I'll edit my packages and see what's going on.
Peter
On Mar 22, 1:44 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> HiPeter
>
>
> "PeterAfonin" wrote:
>
>
>
> SSIS may use files in a users temporary directory, if they have been
> automatically generated or converted. You will need to check the datasources
> and the package steps to see what they are. If necessary they can be moved to
> a "common" directory that has the correct permissions for all users that will
> run the package.
> You may have found that removing the equivalent files in windows XP would
> have had a similar result.
> John- Hide quoted text -
> - Show quoted text -
sql

Error executing SSIS package

Hello,
I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
created several SSIS packages and run them under proxy account. At first
they worked just fine, but then all of them started to fail with the error
messages like this one:
Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
NonTransactableSql Execute SQL Task Description: An error occurred with
the following error message: "Could not find file 'I:\Users\Peter
Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
step failed.
If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
deleting my tmp files periodically, so I don't have these files. It's hard
to believe that SQL Server relies on tmp files that are often deleted. Plus,
I was running the same packages on Windows XP for over a year and never had
problems like this.
What else could it be and how to fix it? Is it a security issue?
I would appreciate your suggestions very much.
Thank you,
Peter AfoninHi Peter
"Peter Afonin" wrote:

> Hello,
> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
> created several SSIS packages and run them under proxy account. At first
> they worked just fine, but then all of them started to fail with the error
> messages like this one:
> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
> NonTransactableSql Execute SQL Task Description: An error occurred wit
h
> the following error message: "Could not find file 'I:\Users\Peter
> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
> step failed.
> If the cause is really in this tmp file, it doesn't make any sense. Yes, I
'm
> deleting my tmp files periodically, so I don't have these files. It's hard
> to believe that SQL Server relies on tmp files that are often deleted. Plu
s,
> I was running the same packages on Windows XP for over a year and never ha
d
> problems like this.
> What else could it be and how to fix it? Is it a security issue?
> I would appreciate your suggestions very much.
> Thank you,
> --
> Peter Afonin
>
I would suspect that the issue is probably with 'I:\Users\Peter
Afonin\AppData\Local\Temp' not existing where is your temp directory set to?
John|||Hi John,
No, this directory is real. This is a default directory in Vista (I have it
on partition I).
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||John, I can confirm that SQL Server is indeed looking for these tmp files.
1. I've recreated my packages, they worked fine.
2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
The packages stopped working, giving me the error I mentioned before.
3. I put these tmp files back to this folder, the packages work fine again.
This is something that I've never heard before and that doesn't make any
sense. Why this never happened on Windows XP, only Vista? What should I do -
it looks like I cannot cleanup my computer anymore?
As a last resort - can I change the folder for tmp files that are needed for
SSIS packages execution, so I could exclude it somehow from the disk
cleanup?
Any advice would be greatly appreciated.
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||Hi Peter
"Peter Afonin" wrote:

> John, I can confirm that SQL Server is indeed looking for these tmp files.
> 1. I've recreated my packages, they worked fine.
> 2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folde
r.
> The packages stopped working, giving me the error I mentioned before.
> 3. I put these tmp files back to this folder, the packages work fine again
.
> This is something that I've never heard before and that doesn't make any
> sense. Why this never happened on Windows XP, only Vista? What should I do
-
> it looks like I cannot cleanup my computer anymore?
> As a last resort - can I change the folder for tmp files that are needed f
or
> SSIS packages execution, so I could exclude it somehow from the disk
> cleanup?
> Any advice would be greatly appreciated.
> Thank you,
> Peter
>
SSIS may use files in a users temporary directory, if they have been
automatically generated or converted. You will need to check the datasources
and the package steps to see what they are. If necessary they can be moved t
o
a "common" directory that has the correct permissions for all users that wil
l
run the package.
You may have found that removing the equivalent files in Windows XP would
have had a similar result.
John|||Thank you, John, I'll edit my packages and see what's going on.
Peter
On Mar 22, 1:44 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> HiPeter
>
>
> "PeterAfonin" wrote:
>
>
>
>
>
>
> SSIS may use files in a users temporary directory, if they have been
> automatically generated or converted. You will need to check the datasourc
es
> and the package steps to see what they are. If necessary they can be moved
to
> a "common" directory that has the correct permissions for all users that w
ill
> run the package.
> You may have found that removing the equivalent files in Windows XP would
> have had a similar result.
> John- Hide quoted text -
> - Show quoted text -

Error executing SSIS package

Hello,
I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
created several SSIS packages and run them under proxy account. At first
they worked just fine, but then all of them started to fail with the error
messages like this one:
Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
NonTransactableSql Execute SQL Task Description: An error occurred with
the following error message: "Could not find file 'I:\Users\Peter
Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
step failed.
If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
deleting my tmp files periodically, so I don't have these files. It's hard
to believe that SQL Server relies on tmp files that are often deleted. Plus,
I was running the same packages on Windows XP for over a year and never had
problems like this.
What else could it be and how to fix it? Is it a security issue?
I would appreciate your suggestions very much.
Thank you,
--
Peter AfoninHi Peter
"Peter Afonin" wrote:
> Hello,
> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
> created several SSIS packages and run them under proxy account. At first
> they worked just fine, but then all of them started to fail with the error
> messages like this one:
> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
> NonTransactableSql Execute SQL Task Description: An error occurred with
> the following error message: "Could not find file 'I:\Users\Peter
> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
> step failed.
> If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
> deleting my tmp files periodically, so I don't have these files. It's hard
> to believe that SQL Server relies on tmp files that are often deleted. Plus,
> I was running the same packages on Windows XP for over a year and never had
> problems like this.
> What else could it be and how to fix it? Is it a security issue?
> I would appreciate your suggestions very much.
> Thank you,
> --
> Peter Afonin
>
I would suspect that the issue is probably with 'I:\Users\Peter
Afonin\AppData\Local\Temp' not existing where is your temp directory set to?
John|||Hi John,
No, this directory is real. This is a default directory in Vista (I have it
on partition I).
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>> Hello,
>> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
>> created several SSIS packages and run them under proxy account. At first
>> they worked just fine, but then all of them started to fail with the
>> error
>> messages like this one:
>> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
>> NonTransactableSql Execute SQL Task Description: An error occurred
>> with
>> the following error message: "Could not find file 'I:\Users\Peter
>> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
>> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
>> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
>> step failed.
>> If the cause is really in this tmp file, it doesn't make any sense. Yes,
>> I'm
>> deleting my tmp files periodically, so I don't have these files. It's
>> hard
>> to believe that SQL Server relies on tmp files that are often deleted.
>> Plus,
>> I was running the same packages on Windows XP for over a year and never
>> had
>> problems like this.
>> What else could it be and how to fix it? Is it a security issue?
>> I would appreciate your suggestions very much.
>> Thank you,
>> --
>> Peter Afonin
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||John, I can confirm that SQL Server is indeed looking for these tmp files.
1. I've recreated my packages, they worked fine.
2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
The packages stopped working, giving me the error I mentioned before.
3. I put these tmp files back to this folder, the packages work fine again.
This is something that I've never heard before and that doesn't make any
sense. Why this never happened on Windows XP, only Vista? What should I do -
it looks like I cannot cleanup my computer anymore?
As a last resort - can I change the folder for tmp files that are needed for
SSIS packages execution, so I could exclude it somehow from the disk
cleanup?
Any advice would be greatly appreciated.
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>> Hello,
>> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
>> created several SSIS packages and run them under proxy account. At first
>> they worked just fine, but then all of them started to fail with the
>> error
>> messages like this one:
>> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
>> NonTransactableSql Execute SQL Task Description: An error occurred
>> with
>> the following error message: "Could not find file 'I:\Users\Peter
>> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
>> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
>> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
>> step failed.
>> If the cause is really in this tmp file, it doesn't make any sense. Yes,
>> I'm
>> deleting my tmp files periodically, so I don't have these files. It's
>> hard
>> to believe that SQL Server relies on tmp files that are often deleted.
>> Plus,
>> I was running the same packages on Windows XP for over a year and never
>> had
>> problems like this.
>> What else could it be and how to fix it? Is it a security issue?
>> I would appreciate your suggestions very much.
>> Thank you,
>> --
>> Peter Afonin
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||Hi Peter
"Peter Afonin" wrote:
> John, I can confirm that SQL Server is indeed looking for these tmp files.
> 1. I've recreated my packages, they worked fine.
> 2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
> The packages stopped working, giving me the error I mentioned before.
> 3. I put these tmp files back to this folder, the packages work fine again.
> This is something that I've never heard before and that doesn't make any
> sense. Why this never happened on Windows XP, only Vista? What should I do -
> it looks like I cannot cleanup my computer anymore?
> As a last resort - can I change the folder for tmp files that are needed for
> SSIS packages execution, so I could exclude it somehow from the disk
> cleanup?
> Any advice would be greatly appreciated.
> Thank you,
> Peter
>
SSIS may use files in a users temporary directory, if they have been
automatically generated or converted. You will need to check the datasources
and the package steps to see what they are. If necessary they can be moved to
a "common" directory that has the correct permissions for all users that will
run the package.
You may have found that removing the equivalent files in windows XP would
have had a similar result.
John|||Thank you, John, I'll edit my packages and see what's going on.
Peter
On Mar 22, 1:44 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> HiPeter
>
>
> "PeterAfonin" wrote:
> > John, I can confirm that SQL Server is indeed looking for these tmp files.
> > 1. I've recreated my packages, they worked fine.
> > 2. I removed all files from I:\Users\PeterAfonin\AppData\Local\Temp folder.
> > The packages stopped working, giving me the error I mentioned before.
> > 3. I put these tmp files back to this folder, the packages work fine again.
> > This is something that I've never heard before and that doesn't make any
> > sense. Why this never happened on Windows XP, only Vista? What should I do -
> > it looks like I cannot cleanup my computer anymore?
> > As a last resort - can I change the folder for tmp files that are needed for
> > SSIS packages execution, so I could exclude it somehow from the disk
> > cleanup?
> > Any advice would be greatly appreciated.
> > Thank you,
> >Peter
> SSIS may use files in a users temporary directory, if they have been
> automatically generated or converted. You will need to check the datasources
> and the package steps to see what they are. If necessary they can be moved to
> a "common" directory that has the correct permissions for all users that will
> run the package.
> You may have found that removing the equivalent files in windows XP would
> have had a similar result.
> John- Hide quoted text -
> - Show quoted text -