 | |
DTS Failed when Sheduled
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#61
 
| |||
| |||
|
Re: DTS Failed when Sheduled -
11-24-2008
, 07:39 AM
|
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 (AT) discussions (DOT) microsoft.com> wrote in message news:6CD10A38-A85A-481B-A67D-7BE2FBDCF3B4 (AT) microsoft (DOT) com... 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/d...displaylang=en RLF "GC" <GC (AT) discussions (DOT) microsoft.com> wrote in message news:4AAB8201-9F06-4825-8240-5E645F81C94E (AT) microsoft (DOT) com... 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 (AT) discussions (DOT) microsoft.com> wrote in message news:ACBD7342-5879-4E6C-AD96-918342F1A8FA (AT) microsoft (DOT) com... 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! |
#62
| |||
| |||
|
|
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 (AT) discussions (DOT) microsoft.com> wrote in message news:6CD10A38-A85A-481B-A67D-7BE2FBDCF3B4 (AT) microsoft (DOT) com... 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/d...displaylang=en RLF "GC" <GC (AT) discussions (DOT) microsoft.com> wrote in message news:4AAB8201-9F06-4825-8240-5E645F81C94E (AT) microsoft (DOT) com... 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 (AT) discussions (DOT) microsoft.com> wrote in message news:ACBD7342-5879-4E6C-AD96-918342F1A8FA (AT) microsoft (DOT) com... 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! |
#63
| |||
| |||
|
|
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 (AT) discussions (DOT) microsoft.com> wrote in message news:6CD10A38-A85A-481B-A67D-7BE2FBDCF3B4 (AT) microsoft (DOT) com... 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/d...displaylang=en RLF "GC" <GC (AT) discussions (DOT) microsoft.com> wrote in message news:4AAB8201-9F06-4825-8240-5E645F81C94E (AT) microsoft (DOT) com... 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 (AT) discussions (DOT) microsoft.com> wrote in message news:ACBD7342-5879-4E6C-AD96-918342F1A8FA (AT) microsoft (DOT) com... 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! |
#64
| |||
| |||
|
|
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 (AT) discussions (DOT) microsoft.com> wrote in message news:6CD10A38-A85A-481B-A67D-7BE2FBDCF3B4 (AT) microsoft (DOT) com... 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/d...displaylang=en RLF "GC" <GC (AT) discussions (DOT) microsoft.com> wrote in message news:4AAB8201-9F06-4825-8240-5E645F81C94E (AT) microsoft (DOT) com... 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 (AT) discussions (DOT) microsoft.com> wrote in message news:ACBD7342-5879-4E6C-AD96-918342F1A8FA (AT) microsoft (DOT) com... 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! |
#65
| |||
| |||
|
|
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 (AT) discussions (DOT) microsoft.com> wrote in message news:6CD10A38-A85A-481B-A67D-7BE2FBDCF3B4 (AT) microsoft (DOT) com... 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/d...displaylang=en RLF "GC" <GC (AT) discussions (DOT) microsoft.com> wrote in message news:4AAB8201-9F06-4825-8240-5E645F81C94E (AT) microsoft (DOT) com... 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 (AT) discussions (DOT) microsoft.com> wrote in message news:ACBD7342-5879-4E6C-AD96-918342F1A8FA (AT) microsoft (DOT) com... 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! |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.