March 9, 2005 at 7:40 am
Hi. I use a DTS package to import data from one db to another. This DTS has a script that selects data on date, usually looking at the previous months data. I need to be able to select a date /month to extract. Is it possible to pass a variable within DTS package?. I can write a small VB or Access type input screen that will collect the variable, and pass it on.
Regards
Allan
March 9, 2005 at 2:07 pm
Allan,
Take a look at this site - some good examples using variables.
http://www.sqldts.com/default.aspx?107
Darrell
March 10, 2005 at 10:22 am
To execute the package from vb, use the following code. Note that the variables on the package are populted by using index in the code.
Also, the package is written using nt security (hence trusted connection'. There is syntax for passing sql username and password. Just search for 'DTSSQLStgFlag_UseTrustedConnection on google
Just replace the upper case values with your values.
Public Function fnSendMail(YOURVARIABLE)
On Error GoTo Err_fnSendMail
Dim oPKG As New DTS.Package
Dim oPKGname As String
oPKGname = "PACKAGENAME"
oPKG.LoadFromSQLServer "SERVERNAME", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , oPKGname ' connect to the package
oPKG.GlobalVariables(1).Value = YOURVARIABLE
' Set Exec on Main Thread as VB is apartment threaded and DTS is free threaded
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
oPKG.Execute ' execute the package
Close_fnSendMail:
' seek and destroy open objects
oPKG.UnInitialize 'disconnect from package
Set oStep = Nothing
Set oPKG = Nothing
Exit Function
' Error handling
Err_fnSendMail:
Print #1, "Error in fnSendMail " & Err.Number & " " & Err.Description
Resume Close_fnSendMail
End Function
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply