December 22, 2008 at 12:13 pm
Hi Gurus,
I'm a newbie to ActiveX and trying to convert my DTS packages to SSIS that use ActiveX Script. I thought I can copy and paste ActiveX code from DTS to ActiveX Script Task in SSIS and then create other tasks but seems like I was clearly wrong .
DTS packages only contains 2 Execute Tasks, 1 ActiveX Script Task and one connection.
Here is what the DTS is doing;
It truncates table, Generates SQL using ActiveX script Task, and Runs Extract.
When I copy and paste the ActiveX script in SSIS ActiveX Script Task and run the SSIS package, I get an error "Function not found"....
Can anyone please help me in solving this mystery.... I will greatly appreciate it. I have included the ActiveX Script below.
Thanks in advance for all your help.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Change the value of the file name on the connection
Set oPackage = DTSGlobalVariables.parent
Dim sSQLString
Dim sDate
Dim dDate
Dim strResult
dDate = GetRevalDate
sDate = DateToName(dDate)
'SQL String
sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _
"@RevalDate = '" & sDate & "'"
DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = sSQLString
Main = DTSTaskExecResult_Success
End Function
Function GetRevalDate()
Dim dDate
dDate = date
If Weekday(dDate) = 1 Then
GetRevalDate = dDate + 1
Else If Weekday(dDate) = 7 Then
GetRevalDate = dDate + 2
Else
GetRevalDate = dDate
End If
End If
End Function
Function DateToName(dDate)
'Create a name based on a date
Dim sYear
Dim sMonth
Dim sDay
sYear = Year(dDate)
If Month(dDate) < 10 Then
sMonth = "0" & Month(dDate)
Else
sMonth = Month(dDate)
End If
If Day(dDate) < 10 Then
sDay = "0" & Day(dDate)
Else
sDay = Day(dDate)
End If
DateToName = sYear & sMonth & sDay
End Function
December 22, 2008 at 12:22 pm
ActiveX is one of the things that went "Bye-bye" when SSIS came around. Scripts cannot be migrated to SSIS.
There is a backward-compatible tool that allows you to run DTS packages in 2005, but ActiveX is no longer supported.
Hope this helps.
Dan
December 22, 2008 at 12:28 pm
Thanks Dan for your prompt response.
But can you please let me know what are the alternatives do I have since I can't use ActiveX anymore?
Is there any Tasks I can usee in SSIS to accomplish the same thing what script is doing?
Thanks
December 22, 2008 at 12:46 pm
Boy, there are so many ways that can be done...
It's a pretty basic set of tasks for SSIS to perform. I can point you in a direction, but you can find the specific "how tos" in any mumber of places.
Here's how I would start (I'm being generic on purpose - I believe in "teaching to fish"...) 😀 :
All of this would be in the Control Flow
1. Create a package variable to store your SQL - you can use expression logic to make it dynamic with the date in the string
2. Execute SQL task to Truncate the table
3. Execute SQL task to run the SQL stored in the variable
It's as simple as it gets. Everything you are doing can be found in BOL and any number of "How To" sites on the web.
The book written by Brian Knight, et. al. is a great reference to have. Very much worth the investment.
Good luck!
Dan
December 22, 2008 at 1:10 pm
Thanks so much again Dan.
To me it is like finding a needle in the dark :crying:.
It is a very valuable information you have provided me with. Atleast now I have a starting point.
I will certainly get the book you have mentioned.
Boy
December 22, 2008 at 1:13 pm
Glad to provide some help. It's definitely a different world moving from DTS to SSIS.
Another place worth looking is http://www.jumpstarttv.com. There are some videos that show some basics in SSIS.
December 22, 2008 at 1:31 pm
Yes Dan it certainly has been a problem moving from DTS to SSIS.
I will certainly visit the site you have listed.
Thanks
Boy
December 23, 2008 at 7:47 am
Hi Dan,
I tried converting the above ActiveX script using Variables and Expressions, but unfortunately not luck :crying:. I have reviewed the juspstarttv.com but the demos there are for simple tasks. Can you please show me how I can convert the above ActiveX script to some SSIS tasks? I can create the Truncate table task but just can't convert the ActiveX script to SSIS.
At the moment I kind of have a dead end situation in converting ActiveX :(.
Thanks
Boy
December 23, 2008 at 8:03 am
1. Create a package variable named "ExecuteSQL" as a type of "String" (http://technet.microsoft.com/en-us/library/ms141085.aspx)
2. You can set the value using an expression (when the variable is selected in the variables list, expressions are accessible on the properties list)
3. BOL has an expression reference you can use - it's pretty straight-forward
http://msdn.microsoft.com/en-us/library/ms141232.aspx
4. Create a second Execute SQL task (connected to the TRUNCATE task with a "Success" constraint) that runs the SQL to which the variable is set (you can reference a variable in an Execute SQL task)
The original purpose for the ActiveX script in DTS was to dynamically set the SQL statement, right? With that being the case, there is no need for a script at all. The value of the ExecuteSQL variable is dynamic since it is set through an expression.
This should get you going.
December 23, 2008 at 8:41 am
Thanks Dan.
Yes. What the DTS doing is, before inserting data, it's Execute SQL Task that is used to truncate table, then ActiveX script task runs some SQLs and depending on the dates, it processes data. Third Execute SQL Task then runs and executes an SP with parameters.
Below is the original ActiveX script and a DTS process overview:
Execute SQL Task-------> ActiveX Script run -----> Execute SQL Task
Truncate Table Run SP with parameters
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Change the value of the file name on the connection
Set oPackage = DTSGlobalVariables.parent
Dim sSQLString
Dim sDate
Dim dDate
Dim strResult
dDate = GetRevalDate
sDate = DateToName(dDate)
'SQL String
sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _
"@RevalDate = '" & sDate & "'"
DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = sSQLString
Main = DTSTaskExecResult_Success
End Function
Function GetRevalDate()
Dim dDate
dDate = date
If Weekday(dDate) = 1 Then
GetRevalDate = dDate + 1
Else If Weekday(dDate) = 7 Then
GetRevalDate = dDate + 2
Else
GetRevalDate = dDate
End If
End If
End Function
Function DateToName(dDate)
'Create a name based on a date
Dim sYear
Dim sMonth
Dim sDay
sYear = Year(dDate)
If Month(dDate) < 10 Then
sMonth = "0" & Month(dDate)
Else
sMonth = Month(dDate)
End If
If Day(dDate) < 10 Then
sDay = "0" & Day(dDate)
Else
sDay = Day(dDate)
End If
DateToName = sYear & sMonth & sDay
End Function
Thanks again
Boy
December 23, 2008 at 8:55 am
Hi Dan,
I have attached a snapshot of DTS package.
Thanks
Boy
December 23, 2008 at 12:13 pm
I've helped as much as I can at this point.
The two replies I posted give you all the direction you need. All you have left to do is reading BOL to figure out how to get it done.
Good luck.
December 27, 2008 at 4:19 am
Hi,
I have had to do a lot of work to upgrade DTS packages to SSIS and have had to develop a number of workarounds - especially as the Microsoft information about upgrading doesn't work. I think the idea is a deliberate strategy to force you away from using ActiveX by making it so ackward that the quickest way is to learn the SSIS way!
The fundamental problem with your script is the call to the dtsglobalvariables.parent object - there is a different object model in SSIS and this one no longer exists. I suspect that this is likely to be what is generating the error message.
One way to continue using a dts package is by importing it into your sql server 2005 db or saving the dts package as a file and running it by calling it from a run DTS package task. This needs a SSIS dts component extension -details are about in BOL and other places.
However the SSIS replacement for the ActiveX is the Script task which uses vb.net. To convert the script you show to vb.net would not be difficult. The way I would tackle this would be to create a global variable for your sql string, make your script update that with the generated SQL string then follow the script task with an execute sql task with the property set to use the global variable for its sql source, but there are other possibilities. The difference between DTS and SSIS is subtanstial and while methods that were simple in DTS may seem to be quite convoluted in SSIS there are many new possibilities as well - a quick upgrade to keep thinks working may be necessary but its a good point to look at the whole package design to take advantage of new features rather than try to make an SSIS package emulate a DTS one.
Bill
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply