Challengeload919

The Script task uses Microsoft Visual Studio Tools for Applications (VSTA) as the development environment for the script itself. Script code is written in Microsoft Visual Basic or Microsoft Visual C#. You specify the script language by setting the ScriptLanguage property in the Script Task Editor. The Script Task was used heavily to demonstrate how the SSIS scripting environment works with Visual Studio and during the execution of a package. Generally, anything that you can script in the.NET managed environment that should run once per package or code loop belongs in the Script Task.

• Script Task Editor Ssis Variables
• Ssis Script Task Editor Read Only Variables
• Script Task Editor Ssis
• Ssis Script Task Transformation

By: Koen Verbeeck

Overview

It’s time to start building an SSIS package. In this chapter, we’lladd tasks to the control flow and learn how you can start the debugger to executethe package. We’ll also look how the execution of different tasks can be relatedto each other.

Adding Tasks to the SSIS Control Flow

Let’s start by adding an Execute SQL Task to the control flow. You caneither drag it from the SSIS toolbox to the control flow, or you can double clickit.

You can see there’s a red error icon on the task. That’s becausewe haven’t defined a database connection yet.

Double click the task to open it. In the editor, open the connectiondropdown and click on <New Connection…>.

If you have already created connection managers, you can pick one from the listin the next window. However, you can also create a new one by clicking the New…button at the bottom.

This will open a connection manager editor. You need to enter the server nameand select a database from the dropdown list. You can also optionally specify ausername and password if you don’t want to use Windows Authentication.

Click OK two times to go back to the Execute SQL Task editor. You can eitherdirectly type a SQL statement in the SQLStatement property or you can clickon the ellipsis to open up the editor. This editor is basically a notepad editorand it has no additional functionality. You are most likely better off writingSQL statements in Management Studio and copy pasting them into the editor. Let’senter a very basic statement: SELECT 1.

Click OK to close the editor.

Executing SSIS Packages

Script Task Editor Ssis Variables

We can now run the package to test our Execute SQL Task. You can click on thegreen arrow or just hit F5. This will start the debugger which will run the package.

When the task has finished, you will see a green icon in the corner of the task.You can click on the stop icon in the task bar to stop the debugger or you can clickon the sentence below the connection manager window.

When the package is running, an extra tab is added called Progress.Here you can see all of the informational messages, errors and warnings generatedby the SSIS package as well as timing information.

Ssis Script Task Editor Read Only Variables

When the debugger stops, the Progress tab is renamed to Execution Results.

SSIS Precedence Constraints

With precedence constraints, we can influence how different tasks impact eachother. Let’s start by creating a copy of our Execute SQL Task. Now when we execute the package, both tasks will be executed in parallel.

You can create a precedence constraint by selecting the first task and draggingthe green arrow to the other task. Now when we execute the package, the first taskswill be executed and then the other.

The green arrow signifies a “Success” precedence constraint, whichmeans the second task will only be executed if the first task is successful. Youcan change the behavior of the precedence constraint by double clicking on the arrow:

You can change the precedence constraint to “Failure”, which meansthe second task will only be executed if the first task fails. With “Completion”,the second tasks will execute once the first task has finished, but it doesn’tmatter if the first task was successful or not. When you have multiple arrows goinginto one single task, you can change the constraint to AND or OR. With AND, alltasks need to be successful before the task starts. With OR, only one task needsto be successful. In the following screenshot, only one of the two top tasks mustfinish successfully so the last task can start.

Script Task Editor Ssis

With precedence constraints and containers, you can create complex workflows:

Additional Information

• For more details on precedence constraints, check out the tipDefining Workflow in SSIS using Precedence Constraints.

Ssis Script Task Transformation