February 1, 2016 at 2:02 pm
I recently finished the SSIS training from MS (it was actually entitled "...Data Warehousing..."), but when I left the class, I had the impression that the only place to actually write C# code was in a Script Task. Before the class, I was expecting to be able to override methods on some of the items provided in the SSIS Toolbox.
Then, today, I encountered a project which has only 2 Execute SQL Tasks, and 2 Send Email Tasks. There's a reference to a variable for the email body in both Email Tasks, but I can't find where those variables' values are being populated. So, I opened the *.dtsx file in Notepad++ and found:
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports System.String
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
Public Sub Main()
'
' Add your code here
'
Dim filename As String
filename = Dts.Variables.Item("User::BackupFileName").Value
If IsNullOrEmpty(filename) Then
Dts.Variables.Item("User::BackupFileName").Value = "FullBackup"
End If
Dts.Variables.Item("User::EmailmessageFail").Value = CStr(Dts.Variables.Item("User::BackupFileName").Value) & " Failed to Complete"
Dts.Variables.Item("User::EmailmessageSuccess").Value = CStr(Dts.Variables.Item("User::BackupFileName").Value) & " Completed Succesfully."
Dts.TaskResult = ScriptResults.Success
End Sub
So now it looks like you can override a method, but I don't know how. Could someone help me get some traction on this concept, because I'm missing a fundamental skill here. Where is this code being stored if there are no Script Tasks in this project?
Thanks,
--=Chuck
February 1, 2016 at 2:13 pm
I'm not sure I follow you 100%, but I'll give it a try. For starters, you cannot override an existing toolbox component as far as I'm aware. You can create your own custom components, but I've never had the need to do that so I'm not sure how to go about it. As far as the script task entry in the XML, that looks like the default template for a scrip task. So when you add a script task and open up the code, there is always a template for you to start with. From what I see in your xml, that's what that is. I'm not sure why it's there if you have not added any script tasks to your package. Did you by chance add one, then remove it? Maybe it was not removed from the XML correctly.
Anyways, is there a specific task or operation that you are trying to accomplish that you think cannot be done with the existing toolbox components?
February 1, 2016 at 2:33 pm
OK, good to know that I still can't modify the code behind a typical object from the toolbox. And in this case, I'm not trying to alter the code, just find where some variables are being set.
The design looks like this:
Execute SQL Task
|
|
Execute SQL Task 1
|
_______|_________
| |
| |
Send Email Task Send Email Task 1
In looking over this project that another Developer (since retired) created, I just noticed that the both Email Tasks have their MessageSourceType property set to "Variable", and their MessageSource set to, for example, "User::EmailmessageFail". So then I started hunting around for where these variable's values are being set, because there's nothing listed in the Variables Menu under the Value column. I do see the variables declared, however.
--=Chuck
February 1, 2016 at 2:35 pm
This is really embarrassing....
Never mind, there was a Script Task off of the screen at the top of the control flow.
Ugh,
--=Chuck
February 1, 2016 at 3:03 pm
No worries, it's all part of learning. Just a suggestion, keep in mind that variables are also set quite often via expressions. I know this one was a script, but make sure you check expressions in the future too. It's easy to overlook expressions on variables and it's a technique that I use all of the time that sometimes baffles junior developers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply