Calling a DTS from a stored proc 3

[Cenedra]

Here is my scenario:

I have a web site that has different site numbers for different customers, and these customers can upload their data files to us, and from that point, I run a stored proc to process these files.

The problem is that I am going to have a different location for the source text file each time the DTS package will be run. Is there a way that I can execute the DTS package in a stored proc and pass to it it's source file location so I can make this package reuseable?

Any help with this is greatly appreciated!

 

[tb]

There are some examples on the net - goto

http://www.sqldts.com

* Global Variables and SQL statements in DTS
* Global Variables and Stored Procedure Parameters



"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Cenedra]

Thanks,

This site is helpful, however I can't find any information (yet) on how to change the source file location within the package code. That is what I really need.

 

[LogicalmanUS]

Cenedra,

We had the same problem, finding different locations dynamically. So we used the DTS Global Variables to do the job for us.

Open the DTS package. Click File - Properties and then the Global Varibales Tab.

Create a variable using the new button (and remember it's name). Click OK.

You can access these variables from any script on the DTS package, thus:

I have created a GV named ERRORSTAGE.
I want to SET the value:
DTSGlobalVariables("ERRORSTAGE&quot.Value = 10
I want to GET the value:
myVariable = DTSGlobalVariables("ERRORSTAGE&quot.Value

You can use the same for getting connect strings (as you would using a Global ASA file for example), thus:

dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim hasRecord ' Record flag
dim strFileName ' Input Path
dim fPath ' Main Folder
dim fInput ' Input folder
dim strExt ' File extension
dim intExt ' Lookup
dim strNewfn ' New File Name
dim intfCounta ' Process Count

' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection&quot
set RSSQL = CreateObject("ADODB.Recordset&quot

' OPEN DATA CONNECTION
ConnSQL1.Open = DTSGlobalVariables("SQLCONN&quot.Value

To dynamically get different locations from the database, we set the GVs to particular select statements run against the database. So, in your case, you can set the values in a table using the SProc, run the DTS package, retrieve those values into GVs and then use them throughout the DTS package.

Hope this assists,

Logicalman

 

[Rhys666]

I've been looking for a way to do this too. Much appreciated.

<SARCASM>
I probably should get on a VB 6 & VB Script refresher course, as moving completely away from VB 6 to asp.Net (c#) appears to have overwritten large quantities of data in memory. Must remember to write to hard disk this time instead of temporary media. Ahem...
</SARCASM>


Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[Cenedra]

LogicalmanUS,

Thanks for the code above, it is very helpful!

Do you know how to pass the value from a global variable to your DTS as the source file path?

And another question - I did save my DTS package as A Visual Basic module. I run it on the desktop, and it runs fine; I place it inside of an Active X DLL and it seems to not run. Do you know if I can not run DTS code through a DLL?

 

[Rhys666]

When placing inside an ActiveX dll, don't you have to reference the SQL DTS object from the VB project, as it's unlikely to be referenced already?

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[Cenedra]

Rhys666,

I'm attempting 2 different possiblities:

1. Use the DTS module code in a DLL.

2. Run a stored proc that calls the DTS package on the server.

Whichever one I can get to work first wins.

 

[Rhys666]

You can use xp_CmdShell and command line parameters to run a DTS package from within a sProc.

To use VB to create/run DTS packages I believe you need to reference 'dtspkg.dll' in the VB project. In VB 6.0, go to Project==>References, and I think the library is called 'Microsoft DTSPackage Object Library'.

Hope this helps...

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[tb]

This is my code from VB.
Reference the object that Rhys mentioned

Dim objPackage As DTS.Package2

Set objPackage = New DTS.Package2

'Set the server/username/password and the DTS package to be run
objPackage.LoadFromSQLServer &quot;SERVER NAME&quot;, &quot;LOGON USER&quot;, &quot;LOGON PWD&quot;, DTSSQLStgFlag_Default, &quot;&quot;, &quot;&quot;, &quot;&quot;, &quot;DTS NAME&quot;
'Pass the date values to the DTS Package
objPackage.GlobalVariables(&quot;FromDate&quot.Value = dtpFrom.Value
objPackage.GlobalVariables(&quot;ToDate&quot.Value = dtpTo.Value

'Set object to raise an error on failure and to do eventlogs
objPackage.FailOnError = True
objPackage.WriteCompletionStatusToNTEventLog = True

'Run DTS Package
objPackage.Execute

objPackage.UnInitialize

Set objPackage = Nothing

Hope you find this useful.


&quot;All I ask is the chance to prove that money can't make me happy.&quot; - Spike Milligan

 

[LogicalmanUS]

Cenedra,

Unfortunately we didn't look at any way to pass parameters when initiating a DTS Package, but I'll look into it.

The way we did it was to prepopulate a table with the data and then just pick it up again on the First ActiveScriptTask in the DTS pack and assign the GV values that way. The other reason to use GVs is because they are persistant throught the life of the DTS pkg, and are dynamic.

For processing files we use a slightly different strategy. The following script looks to a server folder where we store the files to be processed, using a Scripting.FileSystemObject we then list each of the filenames in a table, and then process them one at a time.




' GET INPUT FOLDER, LIST ALL FILES IN TURN
' CHECK IF FILENAME IS IN INPUT TABLE AND STORE IT IF NOT
'Returns a Folder object corresponding to the folder in a specified path.
'object.GetFolder(folderspec) Arguments
'object Required. Always the name of a FileSystemObject.
'folderspec Required. The folderspec is the path (absolute or relative) to a specific folder.
'Remarks An error occurs if the specified folder does not exist.

Dim fso, f, f1, fc, s, fn
Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot
Set f = fso.GetFolder(fInput)
Set fc = f.Files
intfCounta = 1
For Each f1 in fc
strExt = right(f1,3)
if strExt = &quot;txt&quot; or strExt = &quot;TXT&quot; then
fn = left(f1.name, len(f1.name)-4)
strNewfn = &quot;&quot;
strSQL = &quot;Select count(*) as Counta from tXLFILES WHERE [FILENAME] LIKE '&quot; & fn & &quot;%'&quot;
RSSQL.Open strSQL, ConnSQL1
if RSSQL(&quot;Counta&quot = 0 then
' Next line to replace filename for renaming script object
strNewfn = fn & &quot;_v1&quot;
else
strNewfn = fn & &quot;_v&quot; & RSSQL(&quot;Counta&quot +1
end if
strSQL = &quot;INSERT tXLFILES (FILENAME) VALUES ( '&quot; & strNewfn & &quot;')&quot;
if intfCounta = 1 then
ConnSQL1.execute strSQL
f1.name = strNewfn & &quot;.txt&quot;
end if
intfCounta = intfCounta + 1
RSSQL.close
end if
Next
ShowFileList = s

Hope this assists,

Logicalman

 

[Cenedra]

Thanks guys for all your help.

I have everything working now and wanted to post my solution.

I ended up putting the DTS generated Module code into my DLL. At first, this was working on my desktop, but not on the server.

I had a desktop app that I was testing the DLL code with before I was moving it to the devlopment server, so I packaged that and deployed it onto the dev server (after having to fiddle with the SETUP.LST file to make it actually install instead of end up in the reboot loop).

We got the deployed app to work on the server, so then decided to try the DLL through the web page again and viola! It works!

So just installing the DLL in Component Services did not provide the server with all of the files it needed to run!

I guess that's a Microsoft feature

Thanks for all of your time!