RE: DTS from SP
Are you just trying to run a DTS package? If so you can use SQL Agent to run it or you can run it from a cfm page. Shawn Regan -Original Message- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:30 AM To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
Just create a scheduled job (in Sql) 'JOB1' then right click on that job, properties then click on STEPS tab. Click edit it will give you the code to run the DTS. You can copy this DTS code and paste it under DTSRUN e.g master.dbo.xp_cmdshell 'DTSRun /~Z0x3B2318455D75B2F1BC92AF9FA5AD4F411F72662FE92795909F7E45FD2518C5A3D9A 348AD5FF91DB24B84FC23F9E36EAD2975C5143E87E99773' Now you can run it as a query in coldfusion, SQL or save it as stored procedure. You can delete the job afterwards as you've created the job just to get the DTS ID Shaz _ From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 14:30 To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
I want to run it on demand from a CFM page, hence my problem. -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 9:49 AM To: CF-Talk Subject: RE: DTS from SP Are you just trying to run a DTS package? If so you can use SQL Agent to run it or you can run it from a cfm page. Shawn Regan -Original Message- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:30 AM To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
What do you mean by "paste it under DTSRUN"? The generates a different dtsrun string for me, but I still get the same error when trying to execute it in QA. I might need to install EM on the intranet web server so I can do it through COM. I hate to do that since I can get it to run from a DOS prompt, but cannot get it to run from QA or a SP. Dan -Original Message- From: Shahzad.Butt [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 10:02 AM To: CF-Talk Subject: RE: DTS from SP Just create a scheduled job (in Sql) 'JOB1' then right click on that job, properties then click on STEPS tab. Click edit it will give you the code to run the DTS. You can copy this DTS code and paste it under DTSRUN e.g master.dbo.xp_cmdshell 'DTSRun /~Z0x3B2318455D75B2F1BC92AF9FA5AD4F411F72662FE92795909F7E45FD2518C5A3D9A 348AD5FF91DB24B84FC23F9E36EAD2975C5143E87E99773' Now you can run it as a query in coldfusion, SQL or save it as stored procedure. You can delete the job afterwards as you've created the job just to get the DTS ID Shaz _ From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 14:30 To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: DTS from SP
On Wednesday 14 Jan 2004 16:57 pm, Daniel O'Keefe wrote: > I want to run it on demand from a CFM page, hence my problem. Can you execute a DTS from a stored proc ? -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
Hey Dan, Running a DTS via CF is easily performed... exec spSQLserverName exec spDTSrun '#qServerName.output#','#attributes.package#' You can obviously pass in the Username & Password or you can keep them in the SP (which is more secure, especially if you encrypt it.) HTH Neil From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 16:58 To: CF-Talk Subject: RE: DTS from SP I want to run it on demand from a CFM page, hence my problem. -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 9:49 AM To: CF-Talk Subject: RE: DTS from SP Are you just trying to run a DTS package? If so you can use SQL Agent to run it or you can run it from a cfm page. Shawn Regan -Original Message- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:30 AM To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
no problem try this: objDTS.LoadfromSQLServer("SQLSERVER","sa","password",0,"","","","DTSPackage" ,"")> #error_message# This is what i use. Shawn Regan -Original Message- From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 8:58 AM To: CF-Talk Subject: RE: DTS from SP I want to run it on demand from a CFM page, hence my problem. -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 9:49 AM To: CF-Talk Subject: RE: DTS from SP Are you just trying to run a DTS package? If so you can use SQL Agent to run it or you can run it from a cfm page. Shawn Regan -Original Message- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:30 AM To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
you shouldnt need the /G switch. _ From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 17:00 To: CF-Talk Subject: RE: DTS from SP What do you mean by "paste it under DTSRUN"? The generates a different dtsrun string for me, but I still get the same error when trying to execute it in QA. I might need to install EM on the intranet web server so I can do it through COM. I hate to do that since I can get it to run from a DOS prompt, but cannot get it to run from QA or a SP. Dan -Original Message- From: Shahzad.Butt [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 10:02 AM To: CF-Talk Subject: RE: DTS from SP Just create a scheduled job (in Sql) 'JOB1' then right click on that job, properties then click on STEPS tab. Click edit it will give you the code to run the DTS. You can copy this DTS code and paste it under DTSRUN e.g master.dbo.xp_cmdshell 'DTSRun /~Z0x3B2318455D75B2F1BC92AF9FA5AD4F411F72662FE92795909F7E45FD2518C5A3D9A 348AD5FF91DB24B84FC23F9E36EAD2975C5143E87E99773' Now you can run it as a query in coldfusion, SQL or save it as stored procedure. You can delete the job afterwards as you've created the job just to get the DTS ID Shaz _ From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 14:30 To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
No, that is my original problem, cannot run it from SP or QA. I have it running from a DOS prompt. So I put it in a .BAT file and it run, so now I am going to try and call the bat file with xp_CmdShell. Dan -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 12:03 PM To: CF-Talk Subject: Re: DTS from SP On Wednesday 14 Jan 2004 16:57 pm, Daniel O'Keefe wrote: > I want to run it on demand from a CFM page, hence my problem. Can you execute a DTS from a stored proc ? -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
You have CF and SQL on the same box right? I do not. I am aware of the COM method and might resort to that since it is an INTRANET only. Dan -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 12:06 PM To: CF-Talk Subject: RE: DTS from SP no problem try this: objDTS.LoadfromSQLServer("SQLSERVER","sa","password",0,"","","","DTSPackage" ,"")> #error_message# This is what i use. Shawn Regan -Original Message- From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 8:58 AM To: CF-Talk Subject: RE: DTS from SP I want to run it on demand from a CFM page, hence my problem. -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 9:49 AM To: CF-Talk Subject: RE: DTS from SP Are you just trying to run a DTS package? If so you can use SQL Agent to run it or you can run it from a cfm page. Shawn Regan -Original Message- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:30 AM To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
If you are passing the GUID as I am, you do -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 12:01 PM To: CF-Talk Subject: RE: DTS from SP you shouldnt need the /G switch. _ From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 17:00 To: CF-Talk Subject: RE: DTS from SP What do you mean by "paste it under DTSRUN"? The generates a different dtsrun string for me, but I still get the same error when trying to execute it in QA. I might need to install EM on the intranet web server so I can do it through COM. I hate to do that since I can get it to run from a DOS prompt, but cannot get it to run from QA or a SP. Dan -Original Message- From: Shahzad.Butt [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 10:02 AM To: CF-Talk Subject: RE: DTS from SP Just create a scheduled job (in Sql) 'JOB1' then right click on that job, properties then click on STEPS tab. Click edit it will give you the code to run the DTS. You can copy this DTS code and paste it under DTSRUN e.g master.dbo.xp_cmdshell 'DTSRun /~Z0x3B2318455D75B2F1BC92AF9FA5AD4F411F72662FE92795909F7E45FD2518C5A3D9A 348AD5FF91DB24B84FC23F9E36EAD2975C5143E87E99773' Now you can run it as a query in coldfusion, SQL or save it as stored procedure. You can delete the job afterwards as you've created the job just to get the DTS ID Shaz _ From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 14:30 To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP
Yeah we have it on the same box. Sorry about that. I believe someone else said you can use a sql command to run a dts package Shawn -Original Message- From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 9:57 AM To: CF-Talk Subject: RE: DTS from SP You have CF and SQL on the same box right? I do not. I am aware of the COM method and might resort to that since it is an INTRANET only. Dan -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 12:06 PM To: CF-Talk Subject: RE: DTS from SP no problem try this: objDTS.LoadfromSQLServer("SQLSERVER","sa","password",0,"","","","DTSPackage" ,"")> #error_message# This is what i use. Shawn Regan -Original Message- From: Daniel O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 8:58 AM To: CF-Talk Subject: RE: DTS from SP I want to run it on demand from a CFM page, hence my problem. -Original Message- From: Shawn Regan [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 9:49 AM To: CF-Talk Subject: RE: DTS from SP Are you just trying to run a DTS package? If so you can use SQL Agent to run it or you can run it from a cfm page. Shawn Regan -Original Message- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:30 AM To: CF-Talk Subject: DTS from SP I have combed the archives, and then net, and I think I have found the info I need to be able to do this. I used the DTSRUNUI to create this string: 'DTSRun /S "TESTDATA" /U "sa" /P "password" /N "frDay01_upload" /G "{F96F01E6-D82C-4ECF-899E-7DDADB1976C6}" /W "0" ' When I run it from the DOS prompt, it works. When I try to run it with: exec master..xp_cmdshell, both from QA and a SP, I get the following error: DTSRun: Loading... Error: -2147024629 (8007010B); Provider Error: 0 (0) Error string: The directory name is invalid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713 NULL naturally the most frustrating part is trying to find out more info on the error. I have looked in the mentioned .hlp file as well as MS & google. Anybody have any ideas? Dan _ _ _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: DTS from SP - Update
I have it running from the dos prompt, put the string in a .BAT file and run it in QA and it works, so I would think I should be able to do: Exec Master..xp_CmdShell 'h:\frDayUpl.bat' but I get the same error. The data source is a DBF on a mapped drive, so that gas got to be the cause. I am going to try and move it locally and see what happens. Dan [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]