Join Ron Davis for an in-depth discussion in this video Coding the Script task, part of SQL Server Integration Services (2015).
- The Script task. I have a new package open, a new project open and we're going to take a look at the Script task. The Script task is fairly important. When you have something that you need to do and one of the existing tasks will not fit your purpose, then you can code your own using the Script task. The Script task may be misnamed because it's not really scripting in the traditional sense. Instead it's object-oriented programming. So let's create a Script task, and I'm just gonna double-click to add it down here.
And now we'll go in and right-click, and we're gonna call Edit. Now that we're into the Script task, you see you can pick the language of your choice. So I've got C # up here right now, but I could switch on over to VB, but we'll stick with C # right now, and I can come in and I can add variables etcetera, read-only variables or read-write variables. Now to actually do the scripting, if I come down here to Edit, I will open the scripting environment. This is not a course on coding, although you should look at the lynda.com online library, and particularly if you're going to work in C #, look for courses on that.
Or if you're going to work in VB.NET, then look in courses on that. I will also tell you that pretty much if you learn one of those languages, you kinda learn the other one. Now we're going to do a very, very simple little thing down here, we're just gonna add a little script that pops up a message box. So I'm gonna start typing Mess, and there we go. There's the message box, and I'll click Tab and now when I click Tab, I'm gonna hit a dot which shows me the methods and Show is what I want, and now I'll open parentheses and I put a space, and I put in a double quote, and Hello from Lynda.com.
This is C # so I need to put a semicolon to end it, now always, always build after you're done scripting, else, if you don't build, and you see in the lower left I got Build succeeded. If you don't build then you go through and execute it, it'll throw an error and then you got to go back in anyway. So let's close our Editor window, and say OK here which will save it. Let's execute the task and see if we get a popup. So I'm gonna right-click down here and call Execute Task. Hello from Lynda.com! You notice the message box is modal, meaning you have to acknowledge it before it will move on, so if we say OK, our task completes correctly, and we can come back over and there's your Script task.
Here's something that's important to differentiate. Let's add a Data Flow Task, and now we'll go to the Data Flow tab. Within the Data Flow tab, we have not the Script task, but the Script Component. Now if I double-click the Script Component, you'll see it changes. Now I can either use it as a Source or a Destination, remember, "extract, transform, and load", this is the extract and here's the load, and then, or I do a transformation, and I do that in a Script component which is different than a Script task.
We'll say OK, and there's your Script task.
- Creating SSIS packages
- Coding and looping tasks
- Managing and administering SQL with SSIS tasks
- Optimizing data for extraction
- Making packages dynamic with variables and parameters
- Using sequences in a container
- Performing joins
- Handling errors
- Working with XML