DTS pull with dynamic sql
-
-
Ok, here's my code of my ActiveX object. It's the first thing executed in the package.
Function Main()
Dim SelectStmt
SelectStmt = "SELECT * FROM schedule WHERE status='A' and DATE= '" & CStr(Year(Date)) & "-"
DeleteStmt = "delete FROM IEX..IEXSCHEDULE WHERE CONVERT(CHAR(10),DATE,21)= '" & CStr(Year(Date)) & "-"
if Month(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Month(Date)) & "-"
DeleteStmt = DeleteStmt & "0" & CStr(Month(Date)) & "-"
else
SelectStmt = SelectStmt & CStr(Month(Date)) & "-"
DeleteStmt = DeleteStmt & CStr(Month(Date)) & "-"
End If
if Day(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Day(Date)) & "'"
DeleteStmt = DeleteStmt & "0" & CStr(Day(Date)) & "'"
else
SelectStmt = SelectStmt & CStr(Day(Date)) & "'"
DeleteStmt = DeleteStmt & CStr(Day(Date)) & "'"
End If
DTSGlobalVariables("SQLString").Value = SelectStmt
DTSGlobalVariables("DelString").Value = DeleteStmt
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask
oDataPump.SourceSQLStatement = SelectStmt
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_1").CustomTask
oDataPump.SourceSQLStatement = DeleteStmt
Main = DTSTaskExecResult_Success
End Function
I have verified that the following are the names of my delete and insert portions of the package.
DTSStep_DTSDataPumpTask_1
DTSStep_DTSExecuteSQLTask_1
When I run my package, I get the following error:
Task 'DTSStep_DTSDataPumpTask_1' was not found.
I pull up the workflow properties of each object and it's exactly the same. Not sure what I am doing wrong. Thanks for the quick reply.
Live to Throw
Throw to Live
Will Summers -
"Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask"
You'll want the task name here, not the step name. Check disconnected edit to get the right name.
--------------------
Colt 45 - the original point and click interface
-
What is 'disconnected edit'? How do I get the name from that?
Live to Throw
Throw to Live
Will Summers -
I found it in on my 2000 server, but not on my 7.0 server. How do I do it in 7.0?
Live to Throw
Throw to Live
Will Summers -
Ok, I changed DTSStep to DTSTask and ran it. Now I get the following error:
Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description - object doesn't support this property or method: 'oDataPump.SourceSQLStatement'
Error on Line 27
Is there anywhere that shows all of the methods and properties for all of the tasks and connections in the DTS manager? I need to change the SQL for my Execute SQL step and for my Data Transformation step.
The Execute SQL step is a yellow cylinder icon with red circular arrows in front of it. The Data Transformation step is just a arrow pointing from the source to the destination server.
Live to Throw
Throw to Live
Will Summers -
I changed line 27 to reference SQLStatement instead of SourceSQLStatement and it worked! Thanks for any replies to this post.
Live to Throw
Throw to Live
Will Summers -
Ok, here's the latest. I changed my ActiveX code to the following:
Function Main()
Dim DeleteStmt, SelectStmt, oPkg, oDataPump, sSQLStatement, PullDate
PullDate = "2005-05-05"
SelectStmt = "SELECT * FROM schedule WHERE status='A' and DATE= '"+PullDate+"' "
DeleteStmt = "delete FROM IEXSTAGE35..IEXSCHEDULE WHERE CONVERT(CHAR(10),DATE,21)= '"+PullDate+"' "
DTSGlobalVariables("SQLString").Value = SelectStmt
DTSGlobalVariables("DelString").Value = DeleteStmt
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
oDataPump.SQLStatement = DeleteStmt
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oDataPump.SourceSQLStatement = SelectStmt
Main = DTSTaskExecResult_Success
End Function
This works fine as written. What I want to be able to do is to dynamically change the value of the PullDate variable so that I can run this for whatever day I want, or maybe pull the days to run from another table. Any ideas?
Live to Throw
Throw to Live
Will Summers -
Create an ExecSQL task that selects the date you want from your date table. The date you capture should become an output parameter. Assign the output parameter to a global variable, myDate.
Replace PullDate = "2005-05-05"
with
PullDate = DTSGlobalVariables("myDate").Value
[font="Courier New"]ZenDada[/font]
-
BTW, I was wondering if this might be helpful - can you save your DTS package as a BAS module in SQL 7? If so, that would help you with the objects in your package. Open it in Visual Studio and you will see it as VB code.
[font="Courier New"]ZenDada[/font]
-
For SQL 7 there is a little executable called scriptpkg that saves the packages to VB files. It's usually located in the DTS samples folder.
If you can find it, can you send it to philcart@gmail.com? I've been trying to get hold of it for a couple of weeks, but no-one I know seems to be running SQL 7 anymore
--------------------
Colt 45 - the original point and click interface -
I kinda figured that I would do that step, but how do I set the global variable to a value that comes from the result of a query? I can do it in 2000, but not in 7. All of the online documentation assumes 2000.
Live to Throw
Throw to Live
Will Summers -
I replaced the following line :
PullDate = "2005-05-05"
with the following code :
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={SQL Server};Server=(local);Trusted_Connection=yes;"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "SELECT COL_DAT FROM PULLDATE", oConn
PullDate = oRS(0)
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
And it works! Now all I have to do it change the value of the row that I keep in the PULLDATE table and run the DTS job. I am going to scroll through a list of dates and just change the value in the table for each time that I run the job
Thanks to all that have posted on this thread.
Live to Throw
Throw to Live
Will Summers
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply