Calling a DTS package with SQL statement as parameter

[skurpyun]

Hello all.
Help needed here from anyone who has done this before or have an idea of how to do this.

SITUATION: I have an ASP page that creates a DTS.Package object in order to initialize and run a DTS package i will create on my MS SQL Server 2000 database. Package name is [COLOR=red yellow]pkgExtract[/color]. The ASP code when called will create an SQL statement and store it in a string variable. That SQL statement needs to be run by the DTS package in order to, in the end, generate a file that is a database extraction of specified data.

PROBLEM: How does one send an SQL statement as a globalvariable parameter to a DTS package and have it be execute by the package?

DISCLAIMER (sort of): i'm not a SQL Server/DTS Package guru, quite new to it actually. Possible solutions i thought of were to put the sql statement into a temporary database table, then pull the value out of the table when the DTS package starts. The problem still exists i think, i.e. once the sql statement value is retrieved from the database, can i still run it directly in the package? do i just do that in the 'ActiveX Script Task' part of the dts package?

Any thoughts or ideas would be greatly appreciated.

- skurpyun

 

[DreXor]

dont know about all the DTS stuff, but pulling a recordset and making a text file output of it isn't as involved.

the major hinge on the matter is how exactly are you activating this? a schedule or an asp page that a query parameter is sent to ?

as for the file output of the recordset at that point, search for CSV in this forum

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

more specifically this thread : thread333-962116


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[skurpyun]

Thanks for the response DreXor.
I was able to get the SQL Server DTS package to run after much research on the web and a lot of trial and error coding.

Basically, I wanted to create an admin tool where the user could request the extract and, instead of waiting for the page to reload with the result of the extract, the user would get a message saying their request is being processed and the user can continue to surf the web. The code would call the DTS package which would run the extract, generate an excel/CVS file, then email the user to inform them that the file is ready for download. Since the extract could potentially be a high number of rows of data, I did not want the user to wait for an asp page to reload.

Ok, now here is my code for the asp page which will call the DTS.Package (thanks to: [highlight]

http://www.sqldts.com/default.aspx?t=6&s=104&i=207&p=1&a=7

[/highlight] and [highlight]

http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/4/

[/highlight])
[blue][tt]
<%@ language=VbScript %>
<%
[/tt][/blue]
[sup]

'select statement to create the sql query depending on the 'users input from a form.

[/sup]
[blue][tt]
select case PARAMETER
case 0
strQuery = sql0
case 1
strQuery = sql1
case 2
strQuery = sql2
case 3
strQuery = sql3
case else
strQuery = sql4
end select
[/tt][/blue]
[sup]

'declare variable and make it an object 'reference' to our 'DTS package.

[/sup]
[blue][tt]
dim oPkg
set oPkg = server.createobject("DTS.Package")
[/tt][/blue]
[sup]
'connect/load the pre-written DTS package.

[/sup]
[blue][tt]
oPkg.LoadFromSQLServer "(local)","db username","db password",DTSSQLStgFlag_UseTrustedConnection,,,,"name of dts package in sql server"
[/tt][/blue]
[sup]
'set the variables we want to access from the package, i.e. my sql statement and the user's email (defined by variable 'strEmail'.
[/sup]
[blue][tt]
oPkg.GlobalVariables("gSQL").Value = strQuery
oPkg.GlobalVariables("gEmail").Value = strEmail
[/tt][/blue]
[sup]
'execute the package
[/sup]
[blue][tt]
oPkg.execute
[/tt][/blue]
[sup]
'find out of package got initialize and started to run 'successfully.
[/sup]
[blue][tt]
For each oStep in oPkg.Steps
if oStep.ExecutionResult = 1 then
process_failed = true
else
process_failed = false
end if
Next

%>
[/tt][/blue]

The code above it a bit incomplete but it is the main part of my code that calls, loads, and executes the DTS package, then prints a message stating whether the package was successfully loaded or not.

In the DTS package, using an ActiveX task, I am able to grab the sql statement as a global variable using the following lines:
[sup]
'this code is in the dts package itself using vbscript, NOT in the asp 'file.
[/sup]
[blue][tt]
dim dtsSql, dtsEmail
dtsSql = DTSGlobalVariables("gSQL").Value
dtsEmail = DTSGlobalVariables("gEmail").Value
[/tt][/blue]

I then create a connection object, run the query, and loop through the result sets writting the data to an text file. I havent done the code for writting the results to an excel file yet, but i found a site which i think will be useful in doing this ([highlight]

http://www.4guysfromrolla.com/webtech/022801-1.shtml

[/highlight]).

If all this is old news to you, then please, post any possible improvements I can make to my code, or any other thoughts you have as a guru with respect to what i'm trying to do. Otherwise, i've learned something and hope others can pick up a thing or two from my hours of scouring the web! lol. Thanks.

- skurpyun

 

[rajkum]

Hi,

I am trying to run DTS from ASP but to no avail.
I can run from Enterprise manager though.

Could you help me understand what the permissions in SQL Server and(or) IIS are that I have to set to access the package from ASP.

Thanks

 

[skurpyun]

rajkum,

check the links i noted in my last post. I found them to be very helpfull in getting my ASP web based app to connect to a sql server dts package.

For the IIS you really dont need any special permissions as far as I know. If you can successfully connect to a database and print the data from a simple database table in asp, using ADODB or something like that, then all your permissions/settings are fine. In otherwords, try writing a simple asp script to list all the employee names in the EMPLOYEES table of the NORTHWIND database and load it into a browser. If that's successful, then IIS is ok.

As for the SQL Database itself, I used the sa login to test initially. When all was well, I changed to use another login which had the correct rights over the packages I needed to run. Then I connected using the following lines in asp:

'declare and initialize package variable
dim oPkg
set oPkg = server.createobject("DTS.Package")

'connect/load the pre-written DTS package.
oPkg.LoadFromSQLServer "(local)","db username","db password",DTSSQLStgFlag_UseTrustedConnection,,,,"name of dts package in sql server"

Here, "(local)" is just a reference to where my sql server is. In my case it was on my local drive. You can replace this with an ip address if you sql server is located on a remote pc. The other params are pretty much self explanatory.
Good luck.

-sk

 

[rajkum]

Hi skurpyun,

From ASP:
I could send an email using ActiveX script but it would not allow me to do anything with database.

Thanks,
Raj.