Run DTS Package - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Run DTS Package

Author

Topic

desdevmar
Starting Member

2 Posts

Posted - 2005-10-03 : 14:41:05

Hi all,

I have been trying to run a DTS package and tried the following methods.

3) Trying to execute from asp.net page(hybrid code. classic asp and asp.net). when I run it the following error was displayed - "cannot create an ActiveX component". Code worked on my local machine, but, not on the server.

Code snippet from the function:
....
....
objLoadPackage = CreateObject("DTS.Package")
....
....

Tried other things like "Dim objLoadPackage As New DTS.Package" or Package2 etc.. As I am not very proficient in these things, tried all the declarations and including namespaces etc. as i dont have much time to explore on these things at the moment.

2) Tried to execute the package from Stored Procedure. the error message was something like "there is no stored procedure master..xp_cmdshell"(or master.dbo.xp_cmdshell). I think this is not avialable for the security.

1) Tried to populate the temporary table using bulk insert statement, data from a tab delimited text file. This is what my DTS package is for, now. The error message displayed was - "You dont have permission to use Bulk Insert statement" and etc..

Could somebody help me to find a way. All these things worked fine on my local machine. But, have problem with implementing these on the website server.

My main objective is - I get data in a text file, daily. After modifying that data in excel to meet my requirements, I have a tab delimited text file. Then, I prepared a screen to upload it to the server. And, I have prepared the procedures to transform the data and get the data i need into main table. The pending thing is, I have to populate a temporary table from the data in text file. I tried all the above three things. I sent an e-mail to hosting provider. Waiting for their response. But, no much hope.

Regards and thank you for your time.

bnhcomputing
Starting Member

22 Posts

Posted - 2005-10-03 : 22:27:05

I have the same problem.

I have a completed DTS package in my SQL server.

The examples talk about creating and executing a DTS from VB, but no
connection information is ever provided. I just want to execute the
already existing package, not create another.

I would REALLY like to execute the package, that’s already in my SQL
server, from an ASP. Does anyone have an example / documentation on
how to simply execute an already existing DTS from VB or an ASP?

Hubert Hoffman

desdevmar
Starting Member

2 Posts

Posted - 2005-10-04 : 01:50:29

Try this,
-----------------------------
Function ExecuteDtsPackage(pkgName,strfile)
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
Dim oPkg, strError, iCount
Set oPkg = Server.CreateObject("DTS.Package2")

oPkg.LoadFromSQLServer "Server", UserName, Password,DTSSQLStgFlag_Default,"PackagePassword", "", "", pkgName

oPkg.GlobalVariables("glVar").Value = strfile
oPkg.Execute

'Check For Errors
Dim bStatus,oStep,sMessage
bStatus = True
For Each oStep In oPkg.Steps
sMessage = sMessage & " Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sMessage = sMessage & " failed "
bStatus = False
Else
sMessage = sMessage & " succeeded "
End If
Next
If bStatus Then
sMessage = sMessage & "Package [" & oPkg.Name & "] succeeded "
Else
sMessage = sMessage & "Package [" & oPkg.Name & "] failed "
End If
set oPkg = Nothing
ExecuteDtsPackage = bStatus
End Function
-----------------------------

Use your ServerName, userName and Password in the below line,
oPkg.LoadFromSQLServer "Server", UserName, Password,DTSSQLStgFlag_Default,"PackagePassword", "", "", pkgName

If you are using global variable for the file path and name replace the global varriable glVar with your variable. if you want to load from the URL you mentioned in the package, you can simply delete that line and arguement from the function.

Hope this works for you.

tlflow
Starting Member

1 Post

Posted - 2005-10-17 : 09:31:53

Hi there....

I tried the code, however its not working right. For instance, I am able to access and read each task in my package. However each task is coming back as "failed".

If I have access to see all tasks does this automatically mean that my permissions are setup correctly and I need to start looking somewhere else for a problem?

tlflow

-tlflow

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-17 : 09:36:46

Are u able to create the object of DTS now? If not then install the components required for DTS. Remember DTS always runs from the clients end.

Please check this link

http://www.sqldts.com/default.aspx?225

regards
Sachin

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-17 : 10:09:31

Sounds like the website server is preventing access - you need to talk to whoever administeres it and find out what is available or what they are willing to give you permission to do.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources