DTS

[Norman55]

I have created a DTS to import form an Excel Speadsheet into SQL
When I execute the DTS from enterprise manager it works fine
When I activate it via asp I get a error saying

Step [DTSStep_DTSDataPumpTask_1] failed
Task "Transform Data Task: undefined"

Does anyone have any ideas im desperate !

 

[Sheco]

when it is executed from enterprise mangler it is running under the security context of the current user...

if the ADO connection in the ASP is using windows integrated security instead of a SQL Server login then, unless the default was changed in IIS, running it from ASP will happen in the security context of the local IUSR_<MachineName> account... and that account probably doesnt have any permissions on your database.

 

[Norman55]

the ADO connection is using a SQL user account that has full permission to the database ???

 

[Sheco]

Hmmm.. well so much for that theory... but it doesn bring up the question about what account's security will be used to read the Excel file.

 

[Norman55]

i used the sa account when i set up the DTS

i used this code to activate the package does it look about right ?
<%
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sMessage, bStatus

Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "servername","sa","password",DTSSQLStgFlag_Default,"","","","ProvAct"
oPkg.Execute()

bStatus = True

For Each oStep In oPkg.Steps
sMessage = sMessage & "<p> Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sMessage = sMessage & " failed<br>"
bStatus = False
Else
sMessage = sMessage & " succeeded<br>"
End If
sMessage = sMessage & "Task """ & oPkg.Tasks(oStep.TaskName).Description & """</p>"
Next

If bStatus Then
sMessage = sMessage & "<p>Package [" & oPkg.Name & "] succeeded</p>"
Else
sMessage = sMessage & "<p>Package [" & oPkg.Name & "] failed</p>"
End If

Response.Write sMessage
Response.Write "<p>Done</p>"

%>

 

[Sheco]

To help debug maybe you could make a separate, little DTS package with only one step... ActiveX Script Task that creates an instance of the Scripting.FileSystemObject and uses it to rename your Excel file.

If such a package is able to run then it would seem to rule out security permissions.

 

[foxbox]

The problem is not with the DTS itself (you canb start it without errors in the Enterprise Manager), it has to do with the way you start it.
There is more than one way to do that. I normally cretae a stored procedure that fires the DTS ( conn.execute "<my stopred procedure>"). In order to start a DTS with a SP, you use typically commands like sp_OACreate, sp_OAMethode en sp_OADestroy, found in the Master | Extended Stored Procedures database. You'll need to give permissions to the IUSR_<servername> users to run those stored procs....
(Google more information on this: you do not want to give more permissions then stricly necessary)

 

[unclerico]

try:

<%
    Const DTSSQLStgFlag_Default = 0
    Const DTSStepExecResult_Failure = 1
    
    Dim oPkg, oStep, sMessage, bStatus
    
    Set oPkg = Server.CreateObject("DTS.Package[b][red]2[/red][/b]")
    oPkg.LoadFromSQLServer "servername","sa","password",DTSSQLStgFlag_Default,"","","","ProvAct"
    oPkg.Execute()
    
    bStatus = True
    
    For Each oStep In oPkg.Steps
        sMessage = sMessage & "<p> Step [" & oStep.Name & "] "
        If oStep.ExecutionResult = DTSStepExecResult_Failure Then
            sMessage = sMessage & " failed<br>"
            bStatus = False
        Else
            sMessage = sMessage & " succeeded<br>"
        End If
        sMessage = sMessage & "Task """ & oPkg.Tasks(oStep.TaskName).Description & """</p>"
    Next
    
    If bStatus Then
        sMessage = sMessage & "<p>Package [" & oPkg.Name & "] succeeded</p>"
    Else
        sMessage = sMessage & "<p>Package [" & oPkg.Name & "] failed</p>"
    End If
    
    Response.Write sMessage
    Response.Write "<p>Done</p>"

    %>

Or try scheduling the package as a job and then run the sp_start_job stored procedure in the msdb database

strSql = "EXEC msdb..sp_start_job @job_name='<name of the job>'"

 

[Norman55]

I have managed to get that part to work now
The files i want to import sits on on a different server to the web and SQL server
the users do not have access to save their files onto the server though so i need to have a DTS connection to a remote server ???

 

[onpnt]

You should be able to set up a File Transfer Protocol Task in the DTS package and change it to Directory from FTP Site as the type. Make the changes tot he properties as needed and save it. Then create a Dynamic Properties Task and add a entry to the list. In the properties box navigate to the DTSTask for the FTP Task and select it. There should be properties in there for you to enter a username and password for the source files to copy over. You can use global variables and set those int he program prior to executing the tasks also if that is values you want to pass from you ASP script. Just remember to .Remove the global variables in the ASP Sscript and re-add them prior to setting the value of them.

Any further conversation I would ask in the DTS forum. That from how it sounds is the method you should use in order to use the correct tools for the task (being the SQL Server DTS package)


General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[nightjam]

If I seperate the task i recognised the SQL task to delete records from the table runs ok.

the second dsk task fails on dts data pump 1 transformation 1 which import column 1 of csv file to sql database table.

it works fine when ran from enterprise manager but from asp it fails.