RE: executing sql server dts package from cf
Steven, There are a couple of ways to do this. The first using CFObject is outlined on cfcomet.com. Antoher way is to run DTS using cfexecute as follows. CFSET sArg = /S servername /U username /P password /N packagename CFEXECUTE name=c:\program files\microsoft sql server\80\tools\binn\dtsrun.exe arguments=#sArg# outputfile=c:\MyExportLog.txt timeout=1800/ HTH, Dave -Original Message- From: Steven Dworman [mailto:[EMAIL PROTECTED]] Sent: 10 May 2002 15:12 To: CF-Talk Subject: executing sql server dts package from cf I've never done this. Where can I find information? Thanks, Steven D Dworman Macromedia Certified Developer - Web Consultant Systems Administrator ComSpec International - http://www.comspecinternational.com phone: 248.647.8841 cell: 248.767.9925 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: executing sql server dts package from cf
I use this code all of the time. I have changed some of the documentation on this code so that you will know what is going on. CFOBJECT TYPE=COM NAME=objDTS CLASS=DTS.Package ACTION=CREATE There are 3 primary locations DTS packages are generally loaded from: From SQL Server, from Storage File, or from repository. There are 3 different primary methods that can be executed, one for each of these DTS locations. LoadFromSQLServer Syntax: ObjectName.LoadFromSQLServer(ServerName, ServerUserName, ServerPassword, Flags, PackagePassword, PackageGuid, PackageVersionGuid, PackageName, pVarPersistStgOfHost) NOTE: If you want to retrieve the PackageGuid, PackageVersionGuid, PackageName from the database, run the sp_enum_dtspackages system stored procedure. It returns the following variables: name, id, versionid, description, createdate, owner, size, packagedata, isowner LoadFromStorageFile Syntax: ObjectName.LoadFromStorageFile(UNCFile, Password, PackageID, VersionID, Name, pVarPersistStgOfHost) LoadFromRepository Syntax: ObjectName.LoadFromRepository(RepositoryServerName, RepositoryDatabaseName, RepositoryUserName, RepositoryUserPassword, PackageID, VersionID, PackageName, Flags, pVarPersistStgOfHost) This is sample code to execute a (SQLServer) DTS from ColdFusion (objDTS is the Object I created, server is shorwith, username is sa with no password, test is the name of the DTS): CFTRY CFOBJECT TYPE=COM NAME=objDTS CLASS=DTS.Package ACTION=CREATE CFCATCH TYPE = Object CFSET error_message = The DTS Package Object Could Not Be Created. /CFCATCH /CFTRY CFTRY CFSET r = objDTS.LoadfromSQLServer(SHORWITH,sa,,0test,) CFCATCH CFSET error_message = The DTS Package Could Not Be Loaded From the SQL Server at this time. /CFCATCH /CFTRY CFIF IsDefined(error_message) CFOUTPUT#error_message#/CFOUTPUT /CFIF CFSET p = objDTS.Execute() -Original Message- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:07 AM To: CF-Talk Subject: RE: executing sql server dts package from cf Steven, There are a couple of ways to do this. The first using CFObject is outlined on cfcomet.com. Antoher way is to run DTS using cfexecute as follows. CFSET sArg = /S servername /U username /P password /N packagename CFEXECUTE name=c:\program files\microsoft sql server\80\tools\binn\dtsrun.exe arguments=#sArg# outputfile=c:\MyExportLog.txt timeout=1800/ HTH, Dave -Original Message- From: Steven Dworman [mailto:[EMAIL PROTECTED]] Sent: 10 May 2002 15:12 To: CF-Talk Subject: executing sql server dts package from cf I've never done this. Where can I find information? Thanks, Steven D Dworman Macromedia Certified Developer - Web Consultant Systems Administrator ComSpec International - http://www.comspecinternational.com phone: 248.647.8841 cell: 248.767.9925 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: executing sql server dts package from cf
you can also create an SP that executes your DTS package, then call the SP from CF with CFQUERY. that's what we do here, since i've had mixed results instantiating a COM object as CFCOMET recommends. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:07 AM To: CF-Talk Subject: RE: executing sql server dts package from cf Steven, There are a couple of ways to do this. The first using CFObject is outlined on cfcomet.com. Antoher way is to run DTS using cfexecute as follows. CFSET sArg = /S servername /U username /P password /N packagename CFEXECUTE name=c:\program files\microsoft sql server\80\tools\binn\dtsrun.exe arguments=#sArg# outputfile=c:\MyExportLog.txt timeout=1800/ HTH, Dave -Original Message- From: Steven Dworman [mailto:[EMAIL PROTECTED]] Sent: 10 May 2002 15:12 To: CF-Talk Subject: executing sql server dts package from cf I've never done this. Where can I find information? Thanks, Steven D Dworman Macromedia Certified Developer - Web Consultant Systems Administrator ComSpec International - http://www.comspecinternational.com phone: 248.647.8841 cell: 248.767.9925 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: executing sql server dts package from cf
thanks fellas. you've helped out a bunch. -Original Message- From: Christopher Olive [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:27 AM To: CF-Talk Subject: RE: executing sql server dts package from cf you can also create an SP that executes your DTS package, then call the SP from CF with CFQUERY. that's what we do here, since i've had mixed results instantiating a COM object as CFCOMET recommends. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Dave Wilson [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 10:07 AM To: CF-Talk Subject: RE: executing sql server dts package from cf Steven, There are a couple of ways to do this. The first using CFObject is outlined on cfcomet.com. Antoher way is to run DTS using cfexecute as follows. CFSET sArg = /S servername /U username /P password /N packagename CFEXECUTE name=c:\program files\microsoft sql server\80\tools\binn\dtsrun.exe arguments=#sArg# outputfile=c:\MyExportLog.txt timeout=1800/ HTH, Dave -Original Message- From: Steven Dworman [mailto:[EMAIL PROTECTED]] Sent: 10 May 2002 15:12 To: CF-Talk Subject: executing sql server dts package from cf I've never done this. Where can I find information? Thanks, Steven D Dworman Macromedia Certified Developer - Web Consultant Systems Administrator ComSpec International - http://www.comspecinternational.com phone: 248.647.8841 cell: 248.767.9925 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
