Post reply

DTS package issue

  • July 28, 2009 at 2:20 am

    #224042

    Hi all,

    I have one trivial issue in Production box.

    I am facing error in running a dts package.

    In side the package we are taking one of the table data and moving the data to the text file, which is located in other box.

    Initially it was runned successfully but now it is failing..

    It is giving the below error.

    Error:

    Executed as user: GEXPRDCRDB01\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

    Guys Please help me with some suggestions....

    It is very urgent to me... bcoz issue pending from 3 days...

  • July 28, 2009 at 2:35 am

    #1030895

    - Does the SQL agent have access to pick up the files?

    - What account are you using?

    - Is the file set to read-only?

  • July 28, 2009 at 3:23 am

    #1030911

    Thanks for the quick response..

    Sql Agent intially running under User Account and I have changed it to local system account.

    the file has access rights

  • July 28, 2009 at 4:31 am

    #1030937

    Is the file that it is collecting on the local machine or a share?

  • July 28, 2009 at 4:45 am

    #1030944

    The file is not in local mech.

    It is in shared folder of other box.

  • July 28, 2009 at 4:50 am

    #1030947

    There could be the issues.

    The Local system account doesnt have access to network shares/resources as it isn't a domain account.

  • July 28, 2009 at 5:04 am

    #1030952

    change the Agent account back to run under a domain user who has permission to that shared location.

    It's always a good practice to run SQL services under a domain account.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • July 28, 2009 at 5:09 am

    #1030955

    "It depends"

    If you are wanting to access network resources then "yes" you must use a network account.

    I have a number of domain accounts for SQL server. These are tied so that they have full access to the SQL Server(s) and and network shares (replication shares).

  • July 28, 2009 at 6:00 am

    #1030969

    Thanks for the reply...

    i have one question: is this issue having any relation with job owner?

  • July 28, 2009 at 6:11 am

    #1030973

    Thanks for the replies...

    I am waiting for the approval from appl' owner to change the SQL AGENT log on

    account...

    Some more inf' about the error(found this from event viewer)

    The execution of the following DTS Package succeeded:

    Package Name: Weekday - GEXPRO SSO Export

    Package Description: M-F 7am, 11am, 3pm, 7pm, 11pm ET

    Package ID: {2CE8AFB5-8209-4CD3-A31D-38FD3E87AF9D}

    Package Version: {E8D7E709-53BA-4DF0-95DB-8F553C1D61C1}

    Package Execution Lineage: {0FDCD17E-EC2C-4850-BAC3-B383EDEB64C9}

    Executed On: GEXPRDCRDB01

    Executed By: SYSTEM

    Execution Started: 7/28/2009 7:10:00 AM

    Execution Completed: 7/28/2009 7:11:24 AM

    Total Execution Time: 83.516 seconds

    Package Steps execution information:

    Step 'DTSStep_DTSExecuteSQLTask_1' succeeded

    Step Execution Started: 7/28/2009 7:10:51 AM

    Step Execution Completed: 7/28/2009 7:11:20 AM

    Total Step Execution Time: 29.734 seconds

    Progress count in Step: 0

    Step 'DTSStep_DTSDataPumpTask_1' failed

    Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider

    Step Error Description:Error opening datafile: Access is denied.

    Step Error code: 80004005

    Step Error Help File:DTSFFile.hlp

    Step Error Help Context ID:0

    Step Execution Started: 7/28/2009 7:11:20 AM

    Step Execution Completed: 7/28/2009 7:11:24 AM

    Total Step Execution Time: 3.141 seconds

    Progress count in Step: 0

  • July 28, 2009 at 7:36 am

    #1031004

    "Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider

    Step Error Description:Error opening datafile: Access is denied.

    This looks like the permissions issues when trying to open the file.

    JL

  • July 28, 2009 at 7:55 am

    #1031030

    Please tell me one thing.

    If SQL Services running under domain account, if he changes his account psw from outside (like, suppose psw expire after every 15 days in the domain level),

    then we need to change the password in the SQL Services log-on account also, am I correct?

    if i am wrong, please clear my doubt?

    Thanks in advance....

  • July 28, 2009 at 8:14 am

    #1031037

    Im my cases the users password is one that doesnt change.

    We generally create a p/w where it normally can not be remembered (just in case you leave or the IT guys leave i.e A!gEnt?P455w0rd5 ;-))

    To answer your questions... Yes if the AD p/w changes then the agent would also need to be changed.

    Obviously the above p/w is not mine, just in case :w00t:

  • July 28, 2009 at 8:33 am

    #1031057

    Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply