Re: DTS Failed when Sheduled
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Mon, 24 Nov 2008 08:39:34 -0500
GC - Sorry, no idea why the path would look different. - RLF
"GC" <GC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C9015753-79E2-4FF8-A1AC-6E4868E48524@xxxxxxxxxxxxxxxx
Hi Russel,
I finaly find time to answer to your question
Ok so I insert a new task in my DTs that run the WhoAmi.exe
I might find the error but I dont know how to resolve It
The problem is in the name of the Table JC65ASUMÿÿÿJCÿJobÿSummary
I open the Transform Task Properties in my DTS and the source name of the
table that the ODBC Driver use is JC65ASUM JC Job Summary
When the Scheduled job execute the DTS all the space in the name of the
source table are replace by the "ÿ" caracter.
This happen only when the DTs is execute by the scheduled Job when I execute
de DTs manualy no problem.
There are some "ÿ" caracter in the name of the table which is not suppose to
That is why the error log indicate "Path invalid opening user file in
SI_AppCreate"
Do you have any idea of how to resolve this problem
This is few lines of the LogerrorFile
FN\da
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSCreateProcessTask_1
DTSRun OnFinish: DTSStep_DTSCreateProcessTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecutePackageTask_1
DTSRun OnError: DTSStep_DTSExecutePackageTask_1, Error = -2147467259
(80004005)
Error string: Copy Data from JC65ASUMÿÿÿJCÿJobÿSummary to JC65ASUM Step:
[SSDB]Path invalid opening user file in SI_AppCreate
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 1207 (4B7)
Error string:
Error source:
Help file:
Help context: 0
thanks in advance !
-----------------------------------------------------------------------------
"Russell Fields" wrote:
GC,
I am sorry if this is wasting your time, but let's try one more thing and
see if it adds any light.
Instead of running WHOAMI from the command prompt, update your DTS Package
with an additional Execute Process Task that will run WHOAMI.EXE from
within DTS. Let's try to see what it says in that frame of reference.
Start your SQL Agent job that causes the DTS package to try to run. Be sure
on the Job Steps properties to click in the Advanced panel and select "Log
to table". After running the job, you can come back to this panel and
click "View" to see what was written to the table.
RLF
"GC" <GC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6CD10A38-A85A-481B-A67D-7BE2FBDCF3B4@xxxxxxxxxxxxxxxx
> Hi Russell,
>
> the SELECT IS_SRVROLEMEMBER('sysadmin','FN\da') return 1
>
> I execute the whoAmie.exe /all at the command prompt on the server > where
> Sql Server and my DTS are and it return this :
> [User] = "FN\da" S-1-5-21-81001459-1319886459-2848612100-1149
> [Group 1] = "FN\Domain Users"
> S-1-5-21-81001459-1319886459-2848612100-513
> [Group 2] = "Everyone" S-1-1-0
> [Group 3] = "FNDEV4\OLAP Administrators"
> S-1-5-21-1343024091-789336058-1060284298-1003
> [Group 4] = "BUILTIN\Users" S-1-5-32-545
> [Group 5] = "BUILTIN\Administrators" S-1-5-32-544
> [Group 6] = "NT AUTHORITY\INTERACTIVE"
>
> The command EXEC master.dbo.xp_sqlagent_proxy_account N'GET' return
> Domain User
> FN da
>
>
> For the path I open Explorer and type \\MyExternalServerName\Accpac\
> To see i the user da which I am logging on have the rights
>
> da is a domain administator of the domain
> I also add on the accpac repertory the user da with Full Control
>
> The service SqlAgent and MSSQLSERVER are running as FN\da
>
> So there is something missing and I will be very happy when I will find > it
> :)
> If you think something else to try or configure let me know
>
> Thanks in advance!
>
>
>
>
>
>
>
>
>
>
>
>
> "Russell Fields" wrote:
>
>> GC,
>>
>> The jobs fails, according to your previous note, with an invalid path
>> message. I understand that you believe that it must be running in the
>> same
>> security context. This error means that the security context of the >> job
>> is
>> not the same as when you run interactively.
>>
>> One thing to note is that the "SQL Agent Proxy Account" is not the >> same
>> as
>> the account used to run the SQL Agent Service. Look at:
>> xp_sqlagent_proxy_account.
>>
>> You say regarding this account "all the Server Roles activated and All
>> DataBase Access activated" from which I would understand that it is a
>> sysadmin. Is that correct? If so, the following query should return >> a
>> '1'
>> and you should be running as the SQL Server service account. (Is that
>> the
>> same as the SQL Agent service account?)
>>
>> SELECT IS_SRVROLEMEMBER('sysadmin','yourdomain\yourlogin')
>>
>> Perhaps you could have your DTS Package run WHOAMI.EXE with an >> Execute
>> Process Task and see what it says about the security context.
>> http://www.microsoft.com/downloads/details.aspx?familyid=3E89879D-6C0B-4F92-96C4-1016C187D429&displaylang=en
>>
>> RLF
>>
>> "GC" <GC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:4AAB8201-9F06-4825-8240-5E645F81C94E@xxxxxxxxxxxxxxxx
>> > Hi,
>> >
>> > The user I use to run Sql Server Agent is a Domain Administrator
>> > (not the localsystem user)
>> > This Domain Administrator user has been added into the Login user in
>> > Sql
>> > Server Entreprise with all the Server Roles activated and All >> > DataBase
>> > Acces
>> > activated
>> > Also I add this Domain administrator user on the repertory where the
>> > DTS
>> > is
>> > looking for a file to read and I give Full Control to this user
>> >
>> > This user is the same as the one I am logging on
>> > So if I run the DTS manually the DTS work fine
>> > If I run the DtS from a scheduled task the DTS failed
>> >
>> > Everything is full grant it is very open now.
>> > If this does not work with full grant what else can I try?
>> > There is something missing some where
>> > Any Idea
>> >
>> > Thanks in advance!
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> GC,
>> >>
>> >> When you run the DTS manually, you run with your login's rights.
>> >>
>> >> We you run DTS from a scheduled job how it runs depends on who owns
>> >> the
>> >> SQL
>> >> Agent job:
>> >> 1 - a sysadmin - Runs as the SQL Server service account.
>> >> 2 - a non-sysadmin - Runs as the SQL Agent Proxy Account.
>> >>
>> >> Therefore, the proper account must be granted rights to the file >> >> path
>> >> that
>> >> you are trying to access. (Therefore, these accounts must be >> >> domain
>> >> accounts, not just running as local system.)
>> >>
>> >> RLF
>> >>
>> >> "GC" <GC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:ACBD7342-5879-4E6C-AD96-918342F1A8FA@xxxxxxxxxxxxxxxx
>> >> > Hi I have more information about the error form the Job History
>> >> > Error
>> >> > details
>> >> >
>> >> > Executed as user: FN\da. ...SRun OnStart:
>> >> > DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
>> >> > DTSStep_DTSExecuteSQLTask_1
>> >> > DTSRun OnStart: DTSStep_DTSExecutePackageTask_1 DTSRun >> >> > OnError:
>> >> > DTSStep_DTSExecutePackageTask_1, Error = -2147467259 (80004005)
>> >> > Error string: Copy Data from JC65ASUM JC Job Summary to >> >> > JC65ASUM
>> >> > Step: [SSDB]Path invalid opening user file in SI_AppCreate
>> >> > Error source: Microsoft OLE DB Provider for ODBC Drivers
>> >> > Help file: Help context: 0
>> >> > Error Detail Records:
>> >> > Error: -2147467259 (80004005);
>> >> > Provider Error: 1207 (4B7)
>> >> > Error string:
>> >> > Error source:
>> >> > Help file:
>> >> > Help context: 0
>> >> > DTSRun OnError: DTSStep_DTSExecutePackageTask_1, Error =
>> >> > -2147467259(80004005)
>> >> > Error string: Copy Data from JC65ANME JC CustomerManager Names >> >> > to
>> >> > JC65ANME Step: [SSDB]Path invalid opening user file in >> >> > SI_AppCreate
>> >> > Error source: Microsoft OLE DB Provider for ODBC Drivers
>> >> > Help file: ... Process Exit Code 0. The step succeeded.
>> >> >
>> >> > If that can help
>> >> >
>> >> > I Have no idea of what is going on
>> >> > Remember that the path is good because the DTS work well when I
>> >> > start
>> >> > de
>> >> > DTS
>> >> > manualy.
>> >> >
>> >> >
>> >> >
>> >> > "GC" wrote:
>> >> >
>> >> >> Hi,
>> >> >>
>> >> >> I have a DTS that run weel when I run it manualy I mean when I
>> >> >> right
>> >> >> click
>> >> >> on it et select Execute DTS.
>> >> >>
>> >> >> When I Scheduled The DTS it fails.
>> >> >> The DTS is importing Data From an accpac Application with a >> >> >> SYSTEM
>> >> >> DATA
>> >> >> SOURCE ODBC driver
>> >> >> The accpac Files are on another server.
>> >> >> First I was using a Map Drive.
>> >> >> After I try a UNC path.
>> >> >> I test the UNC path and it work ok
>> >> >> I start manualy the DTS and it work ok
>> >> >>
>> >> >> The user that run the sql Agent service and MSqlServer is an >> >> >> domain
>> >> >> Administrator user
>> >> >>
>> >> >>
>> >> >> Soon as I scheduled the DTS it fails with this error message :
>> >> >>
>> >> >>
>> >> >> Step 'Copy Data from JC65ANME JC CustomerManager Names to
>> >> >> JC65ANME
>> >> >> Step'
>> >> >> failed
>> >> >>
>> >> >> Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
>> >> >> Step Error Description:[SSDB]Path invalid opening user file in
>> >> >> SI_AppCreate
>> >> >> Step Error code: 80004005
>> >> >> Step Error Help File:
>> >> >> Step Error Help Context ID:0
>> >> >>
>> >> >> I dont know what else I can try
>> >> >>
>> >> >> Do you have any idea
>> >> >>
>> >> >> Thanks in advance!
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>
.
- References:
- DTS Failed when Sheduled
- From: GC
- RE: DTS Failed when Sheduled
- From: GC
- Re: DTS Failed when Sheduled
- From: Russell Fields
- Re: DTS Failed when Sheduled
- From: GC
- Re: DTS Failed when Sheduled
- From: Russell Fields
- Re: DTS Failed when Sheduled
- From: GC
- Re: DTS Failed when Sheduled
- From: Russell Fields
- Re: DTS Failed when Sheduled
- From: GC
- DTS Failed when Sheduled
- Prev by Date: Compare customizing SSIS 2005 vs SSIS 2008 objects
- Next by Date: Can SSIS "populate from source" like DTS?
- Previous by thread: Re: DTS Failed when Sheduled
- Next by thread: XML Destination
- Index(es):
Relevant Pages
|
