September 18, 2013 at 10:56 am
Hi all,
At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS package delivering the data in a flat file.
This week i had to change the location of the sharedrive in the package, after doing the changes the package is not working and the following message appears (see below), does anyone knows how to solve this one?
Executed as user: (Database 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: 9:46:26 AM
Error: 2013-09-18 09:46:27.42
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [111]
Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error
Error: 2013-09-18 09:46:27.42
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Destination" (111) failed the pre-execute phase and returned error code 0xC020200E. End Error
Error: 2013-09-18 09:46:27.42
Code: 0xC020200E Source: Data Flow Task Flat File Destination [16]
Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error
Error: 2013-09-18 09:46:27.42
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Destination" (16) failed the pre-execute phase and returned error code 0xC020200E. End Error
Error: 2013-09-18 09:46:27.44
Code: 0xC020200E
Source: Data Flow Task Flat File Destination [166]
Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error
Error: 2013-09-18 09:46:27.44
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Destination" (166) failed the pre-execute phase and returned error code 0xC020200E. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:46:26 AM
Finished: 9:46:27 AM
Elapsed: 0.672 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
09/18/2013 09:00:00,Aurum - Backup to share,Error,0,(Database Name),Aurum - Backup to share,(Job outcome),,The job failed. The Job was invoked by Schedule 14 (daily).
The last step to run was step 1 (*****).,00:00:00,0,0,,,,0
09/18/2013 09:00:00,Aurum - Backup to share,Error,1,(Database Name),Aurum - Backup to share,Aurum Share Backup,,
Best regards,
Daniel
September 18, 2013 at 11:25 am
Hi Daniel
Please check permissions for user: (Database Name)\SYSTEM for shared folder.
Owner of the Job should have write access to that directory.
Br.
Mike
September 19, 2013 at 7:09 am
Hi Michal,
So i have to add the user (databasename)/System to the sharedrive permissions, is it correct?
Best regards,
Daniel
September 19, 2013 at 7:16 am
daniel.carreira (9/19/2013)
Hi Michal,So i have to add the user (databasename)/System to the sharedrive permissions, is it correct?
Best regards,
Daniel
Yes, it must have read/write access to the shared directory.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2013 at 8:07 am
Koen Verbeeck (9/19/2013)
daniel.carreira (9/19/2013)
Hi Michal,So i have to add the user (databasename)/System to the sharedrive permissions, is it correct?
Best regards,
Daniel
Yes, it must have read/write access to the shared directory.
And if the destination folder is way down in the directory structure, it usually must have perms on the root folder too.
September 20, 2013 at 3:09 am
Hi Guys, many thanks for the help, i tried to add that user to the shared driver but it didn't recognize the name and didn't let me do it, any suggestion?
September 20, 2013 at 3:18 am
Brandie Tarvin (9/19/2013)
Koen Verbeeck (9/19/2013)
daniel.carreira (9/19/2013)
Hi Michal,So i have to add the user (databasename)/System to the sharedrive permissions, is it correct?
Best regards,
Daniel
Yes, it must have read/write access to the shared directory.
And if the destination folder is way down in the directory structure, it usually must have perms on the root folder too.
+1.
September 20, 2013 at 3:28 am
daniel.carreira (9/20/2013)
Hi Guys, many thanks for the help, i tried to add that user to the shared driver but it didn't recognize the name and didn't let me do it, any suggestion?
Are they on the same domain?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 20, 2013 at 3:53 am
Hi Koen, where can i see the domain of the sharedrive?
September 20, 2013 at 4:07 am
Hi Daniel
Please open cmd and type command: set USERDOMAIN on both machnies and comapre the results.
If they are the same, your machines exist in one domain, else in different.
Br.
Mike
September 20, 2013 at 4:27 am
Hi SSC,
What happens if they are not in the same domain? Which seems to be the case!?
Many thanks,
Daniel
September 20, 2013 at 5:32 am
daniel.carreira (9/20/2013)
What happens if they are not in the same domain? Which seems to be the case!?
Then you have domain trust issues. Get with your domain admins and see what you can do to hash out the trust issue (you'll need their help to resolve it). Once that's fixed, you or the network admin should be able to add it to the Share perms. And make sure they are adding it to the virtual share info instead of the direct path Security options. Otherwise you'll lose it if you have a cluster that fails over.
September 25, 2013 at 11:52 am
Hi Brandie,
Just got information they are in same domain, what action should i take on this situation?
Thanks,
Daniel
September 25, 2013 at 12:26 pm
I would make things easy on yourself. Change the SQL Service Account to an AD account, and not use a Local box Account name.
Was the share that you used first, happen to be on the same box? You can make any directory shared to anyone on the same box.
And then use either an XML Config File, or a SQL table to store all your SSIS Package configuration. That way, it is very simple to edit the text file or data table to modify things like a path to another box.
Andrew SQLDBA
September 26, 2013 at 6:27 am
Daniel,
I would agree with Andrew. This sounds like a case of your services running under a local account instead of an plain vanilla Domain (Active Directory) account. Local accounts can only access items directly attached to them.
Or your AD account may not have the proper Group Policy rights...
Check that to verify.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply