September 27, 2013 at 8:22 am
I am pretty new at this, is there any good video tutorial or webpage that can show me how to do this?
Thanks,
Daniel
September 27, 2013 at 8:36 am
daniel.carreira (9/27/2013)
I am pretty new at this, is there any good video tutorial or webpage that can show me how to do this?
Google the term "switch SQL Server services accounts" and look up Service Accounts in SQL Server Books Online. There is plenty of information on how to do this out there and several of your first links should arrive at Microsoft pages.
September 27, 2013 at 9:09 am
Let me just add some information on some tests we have done, to see if this can have an Eureka moment in any of you.
Last week me and the creator of the package that does the backup to the share drive were trying several situations, I am going to place below line by line the tests and results:
1st test: open package in my PC visual studio and try to execute it (it didn't show the option to execute) Failed
2nd test: open package in pc (visual studio) that was used to create it and with the user that created it, execution option appeared and package run, even the one with altered shared drive (new one) Pass
3rd test: place package with new sharedrive in proper location for the sql job, it failed, this package was altered in the original pc that was used for the creation of it Failed
Any conclusion can be taken of this?
Regards,
Daniel
September 27, 2013 at 10:34 am
Eureka!
Check the package's ProtectionLevel (package properties). I bet it's set to EncryptSensitiveWithUserKey.
Change it to DontSaveSensitive, save it, and put it back up on the server.
October 1, 2013 at 8:50 am
Hi Brandie no luck on your suggestion, i did a new package, and it runs in my PC but not in the Job, i am pasting below the error message....
Date10/1/2013 7:45:37 AM
LogJob History (******** - Backup to share)
Step ID1
Server********
Job Name********
Step Name********
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: "server name"\SYSTEM.
Microsoft (R) SQL Server Execute Package Utility Version 10.50.4279.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 7:45:37 AM Error: 2013-10-01 07:45:38.87
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [175]
Description: Cannot open the datafile "\\............_Remits.txt". End Error
Error: 2013-10-01 07:45:38.87
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [52] Description: Cannot open the datafile "\\..........Remit_Invoices.txt". End Error
Error: 2013-10-01 07:45:38.87
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [121]
Description: Cannot open the datafile "\\............_Master.txt". End Error
Error: 2013-10-01 07:45:38.89
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Destination" (175) failed the pre-execute phase and returned error code 0xC020200E. End Error
Error: 2013-10-01 07:45:38.89
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Destination" (121) failed the pre-execute phase and returned error code 0xC020200E. End Error
Error: 2013-10-01 07:45:38.89
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Destination" (52) failed the pre-execute phase and returned error code 0xC020200E. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 7:45:37 AM
Finished: 7:45:38 AM Elapsed: 1.468 seconds. The package execution failed. The step failed.
October 1, 2013 at 12:17 pm
What is the ProtectionLevel of your new package?
October 2, 2013 at 9:01 am
EncryptSensitiveWithUserKey
October 2, 2013 at 10:13 am
That's your problem. Look back at my post on the previous page. I've already told you how to fix the issue.
October 2, 2013 at 10:25 am
I believe i have tried it already, gonna try now, thanks Brandie
October 2, 2013 at 10:55 am
No change
October 2, 2013 at 11:32 am
It is a problem with that service account that SQL Server uses. You are running the services as
"Executed as user: "server name"\SYSTEM."
That account has permission only on the box that is it running. You cannot share a drive using that account, because the package on the other box is using a different account.
Change that to an Active Directory account. Grant the AD account read and write permission on the share. Also, the protection level is an issue. You cannot move a package with the protection level set that way. Set to "Do Not Save Sensitive", all the info that you need should be in a XML config file or a database table. Things such as path statements, comments, database server login credentials, etc....
Import the revised package into the MSDB database, modify the config file, or database table data, and it will then execute without any issue.
Andrew SQLDBA
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply