Implementing a Script Task

Let's assume that you need to export all employees' information from your database to a text file. The format of the text file is a little tricky. One record is split into multiple lines, and each line contains a column name and the value concatenated by a colon. Each record should be separated by a dashed line. The Flat File destination in the Data Flow task doesn't support this requirement because it allows you to choose only character-delimited or fixed-length formats. Instead of using a Data Flow task, you will use a Script task to retrieve the data from the database and write out the data to the text file with a specified format. Because the Script task is so powerful and versatile, it often incorporates multiple tasks into a single one.

Creating a New Script Task and Initiating Code

First, you will learn how to write code in the Script task by using the VSA environment. In the following procedure, you will create a new package and a new Script task in the new package; then you will implement your code in the task, using Code Editor in VSA. Through this procedure, you will also review the basic features of VSA.

Create a New Project and Add a Script Task

1. Start SQL Server Business Intelligence Development Studio (BIDS).

2. On the File menu, point to New, and then select Project.

3. Select Integration Services Project and type Chapter06_01 in the Name text box.

4. In the Location text box, specify C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005SbS\Chap06. Make sure the Create directory for the solution option is checked and click OK.

5. In the Toolbox, in the Control Flow Items group, select Script Task and drag it to the design surface of the Control Flow tab.

6. Double-click the new Script task.

7. In the Script Task Editor dialog box, change Name to Script Task - Export Employees.

8. On the Script page, click Design Script. Your screen looks like this:

   

   The VSA Integrated Development Environment (IDE) will be launched, and it shows the skeleton code of the ScriptMain class and Main method. By default, the Main method has just one statement: Dts.TaskResult = Dts.Results.Success. This statement returns the overall success result to the SSIS engine. You need to add your custom code before this statement.

9. In Project Explorer, right-click the References folder and select Add Reference.

10. In the Add Reference dialog box, select Sytem.Xml.dll in the .NET list box, and then click Add. System.Xml.dll appears in the Selected Projects And Components list box on the bottom.

11. Click OK.

    Ensure that System.Xml was added under the References folder. This allows you to use objects defined in System.Xml.dll. This process is necessary to use DataSet in your code because DataSet refers to one of the objects defined in the System.Xml.dll.

12. In the Code Editor, remove all comment lines that start with an apostrophe (').

13. Insert a new line before the Dts.TaskResult = Dts.Results.Success line.

14. Type the following code in the new line:

     Dim fileName as String = "C:\Employees.txt" 

    Notice that a small window pops up with a list of candidates for the keyword when you press the space key after the AS statement. This is called IntelliSense. When you type some of the characters you want to write, IntelliSense suggests candidates that start with the character you have typed. If you find a word you want to type, select the word from the list with the up or down arrow keys and press the Tab key to complete the word. This will reduce typing and help cut down on mistakes.

15. Place the cursor on the word Class in the editor and press F1.

    Note 

    If the Online Help Setting dialog box displays, select "Use local help as primary source" and click OK.

    Microsoft Document Explorer appears on the screen with the appropriate help content for the CLASS statement. When the F1 key is pressed, the Code Editor detects the word under the cursor and launches Microsoft Document Explorer, passing the word as a parameter.

16. Append the following lines under the last line of IMPORTS statements. (It should be Imports Microsoft.SqlServer.Dts.Runtime.)

     Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Imports System.Data.SqlClient Public Class ScriptMain 

    These IMPORTS statements allow you to use classes defined under the specified namespaces without specifying a fully qualified name. This statement makes your script code shorter and easier to read. A namespace is a context for naming items such as classes, modules, and interfaces. For example, you will use the DataSet class in the following procedure; DataSet is defined under the System.Data namespace. If you declare Imports System.Data at the beginning of your script, you can simply write your code as:

     Dim ds As DataSet 

    It is not mandatory to have IMPORTS statements in your script. If you don't have an IMPORT SYSTEM.DATA statement, you have to write as follows:

     Dim ds As System.Data.DataSet 

17. Insert the following code in the Main method before the Dts.TaskResult = Dts.Results.Success line:

     Public Sub Main()     Dim fileName as String = "C:\Employees.txt"     Const CONNECTION_STRING As String = _         "Data Source=localhost;" & _         "Initial Catalog=is2005sbsDW;Integrated Security=SSPI;"     Const SQL_SELECT_EMPLOYEE As String = _         "SELECT" & _         "  FirstName, LastName, MiddleName, Gender, Title, " & _         "  DepartmentName, HireDate, LoginID, EmailAddress, Phone " & _         "FROM DimEmployee " & _         "ORDER BY LastName"     Const RECORD_SEPARATOR As String = _         "----------------------------------------------------------"     Dim writer As StreamWriter     Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)     Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)     Dim ds As New DataSet     adapter.Fill(ds)     writer = New StreamWriter (fileName, False)     writer.AutoFlush = True     With ds.Tables(0)         For row As Integer = 0 To .Rows.Count - 1            For col As Integer = 0 To .Columns.Count - 1                 writer.WriteLine("{0}: {1}", _                     .Columns(col).ColumnName, .Rows(row)(col))             Next col             writer.WriteLine(RECORD_SEPARATOR)         Next row     End With     writer.Close()     Dts.TaskResult = Dts.Results.Success End Sub 

    Note 

    This script is also available in the text file saved under the C:\Documents and Settings\<username>\Microsoft Press\is2005SbS\Chap06\Chapter06_01_Script.txt.

    The following is a pseudocode rendering of the preceding script code. Refer to the MSDN Web site if you would like to know the details of each statement.

    • Declare resources, which will be used later in the method (e.g. filename).

    • Retrieve all data from DimEmployees table in is2005sbsDW database and store it in the DataSet.

    • Open a text stream for the result file.

    • Look into each column in each row and write out all column name and value to the text stream.

    • Write out the separator (dash line) at the end of each record.

    • Return the overall result to SSIS engine.

18. Close VSA by clicking the Close box in the window title bar.

    Tip 

    You don't need to select Save on the File menu. VSA will automatically save all updates when it is closed.

19. Click OK to close the Script Task Editor dialog box.

20. On the Control Flow tab, press F5 to execute the package. Ensure that the Script Task - Export Employees turns green.

21. Start Windows Explorer and open the root folder for drive C.

    Note 

    Throughout this chapter, you will copy files to the root folder of your drive C. To open an Explorer window to this location, choose Start, Run, and then type drive C, and click OK. You may delete these files at the end of the exercise.

    Notice that Employees.txt has been generated. Double-click to open the text file in Notepad and confirm that the employee data has been successfully exported with the expected format.

22. On the Debug menu, click the Stop Debugging button.

Handling Errors

When you implement the Script Task, you might need to incorporate some error-handling behaviors in your script code. For example, if you're expected to register the result of the script in the database, you need to update the database when the script displays an unexpected error, such as File Not Found or Destination Database Is Not Available. If you don't implement error-handling code, the execution will be terminated when it encounters an unexpected error, and you will not register the result in the database.

You can use a TRY…CATCH…FINALLY statement to trap errors and implement alternative behaviors in your script code. A basic TRY…CATCH…FINALLY statement looks like this:

 Try     (regular code) Catch ex As Exception     (error handling code) End Try 

If the code encounters an error somewhere inside the Try block (between the TRY and CATCH statements), the execution enters the Catch block (between the CATCH and END TRY statements) and executes the alternative code implemented in the block. If you would like to know the details of the error, you can refer to the Exception object (defined as ex in the preceding example) inside the Catch block.

You can also implement extra behaviors inside the Finally block. The code in the Finally block will be executed regardless of whether an error has occurred in the Try block. It will be executed after the code in the Try or Catch block has been executed. The statement in the Finally block looks like this:

 Try     (regular code) Catch ex As Exception     (error handling code) Finally     (finalization code) End Try 

When you implement code in the Finally block, you need to be careful to handle the error that might occur inside the block. If an error occurs in the Finally block and you don't have a TRY…CATCH…FINALLY statement inside the Finally block, the execution will be terminated, and the rest of the code in the Finally block won't be executed.

Whether the TRY…CATCH…FINALLY statement enables you to switch the behavior depends on what kind of error has occurred. You can create multiple Catch blocks with different types of exceptions in their parameters. For example:

 Try     (regular code) Catch ioex As IOException     (error handling code for I/O related errors) Catch sqlex As SqlException     (error handling code for database related errors) Catch ex As Exception     (error handling code for all other errors) End Try 

In the following procedure, you will add the TRY…CATCH…FINALLY statement inside your code and add a simple behavior in the Finally block to close the StreamWriter object before the script returns a failure as the overall result. It is common and always good to have this kind of resource-release code in your script.

Note 

You can use the TRY…CATCH…FINALLY statement when you implement the Script component as well.

Add Error-Handling Code in the Script Task

1. On the Control Flow tab, double-click the Script Task - Export Employees.

2. In the Script Task Editor dialog box, on the Script page, click Design Script.

3. In the Code Editor, insert a new line before the Dim Adapter As New SqlDataAdapter line.

4. Type Try in the new line and press Enter.

   Note 

   VSA will automatically add a CATCH…END TRY statement after a TRY statement. This is another user-aid functionality of the Code Editor.

5. Cut all existing code that follows the END TRY statement and paste it in the Try block.

6. Type the following code in the Catch block.

    Dts.TaskResult = Dts.Results.Failure 

   This code will signal the failure of the procedure to the containing package when an unexpected error occurs inside the Try block.

7. Remove the following lines in the Try block:

    writer.Close() 

8. Insert a new line right before the END TRY statement and type Finally in the new line.

9. Insert the following code in the Finally block:

    If writer IsNot Nothing Then     writer.Close() End If 

   This code closes the StreamWriter object used in the Try block, regardless of whether an error has occurred in the Try block. The purpose of having an IF statement before you close the StreamWriter object is to avoid an unexpected error in the Finally block. If the writer variable is null for some reason, the WRITER.CLOSE() statement fails, and the execution will be terminated.

   Now your code looks like this:

        Dim writer As StreamWriter     Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)     Try         Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)         Dim ds As New DataSet         adapter.Fill(ds)         writer = New StreamWriter (fileName, False)         writer.AutoFlush = True         With ds.Tables(0)             For row As Integer = 0 To .Rows.Count - 1                 For col As Integer = 0 To .Columns.Count - 1                     writer.WriteLine("{0}: {1}", _                         .Columns(col).ColumnName, .Rows(row)(col))                 Next col                 writer.WriteLine(RECORD_SEPARATOR)             Next row         End With         Dts.TaskResult = Dts.Results.Success     Catch ex As Exception         Dts.TaskResult = Dts.Results.Failure     Finally         If writer IsNot Nothing Then                 writer.Close()         End If     End Try End Sub 

10. Close VSA and the Script Task Editor dialog box.

11. Press F5 to execute the package. Make sure the updated code still works.

12. On the Debug menu, click Stop Debugging.

13. Open Microsoft SQL Server Management Studio (SSMS) and connect to localhost.

14. In the Object Explorer, expand Databases and right-click is2005sbsDW, point to Tasks, and select Take Offline. A progress dialog box appears, and the database will soon go offline.

    

15. Go back to BIDS and press F5 to execute the package again. Because you took the source database offline, the Script Task - Export Employees fails and turns red.

16. Click the Progress tab.

    Notice that an error message has been logged with a white and red exclamation icon next to the message Error: The Script returned a failure result.

17. On the Debug menu, select Stop Debugging.

    Note 

    Please keep the database offline for the next section.

Providing a Message to the Progress Tab

In the previous section, you successfully handled the error in the Try block. You could see that the error occurred in the Progress tab as you expected. However, you did not have enough information to know exactly what happened in the script.

The messages on the Progress tab are derived through each component's event. The Dts.Events object enables you to raise various kinds of events such as Information, Progress, and Error. In the following procedure, you will add code to provide the following events to the Progress tab:

• Open a destination file as an Information event (with file name).

• Process employee data as a Progress event.

• Detect errors as Error events (with detail error messages).

More Info 

Messages on the Progress tab will be discussed in more detail in Chapter 7, "Debugging Packages."

Modify the Script to Fire an Event

1. Double-click Script Task - Export Employees.

2. In the Script Task Editor dialog box, click Design Script on the Script page.

3. In the Code Editor, append the following code in the Main method:

    Public Sub Main()     Dim fileName As String = "C:\Employees.txt"     Const COMPONENT_NAME As String = "Export Employees"     Const CONNECTION_STRING As String = _         "Data Source=localhost;" & _         "Initial Catalog=is2005sbsDW;Integrated Security=SSPI;" … Try         …     Catch ex As Exception         Dts.Events.FireError(0, COMPONENT_NAME, ex.Message, "", 0)         Dts.TaskResult = Dts.Results.Failure     Finally         If writer IsNot Nothing Then             writer.Close()         End If     End Try End Sub 

   The Dts.Events.FireError method requires five parameters. The first parameter is an arbitrary error code by which you can identify this error, which is not necessary to specify this time. The second parameter is the name of the source component, which should be the name of this script task. The third parameter is a message text that will be displayed on the Progress tab. You specify the error message derived by the Exception object here. The fourth and fifth parameters are the information to incorporate help, which is also not necessary this time.

   Important 

   If Dts.Events.FireError has been executed in the script, the final result of the Script Task will always be failure even if you are specifying Dts.Results.Success to Dts.TaskResult.

4. Close VSA and the Script Task Editor dialog box.

5. Press F5 to execute the package. Your package fails again because the is2005sbsDW database is offline.

6. Click the Progress tab. Your screen looks like this:

   

   Notice that an event message with a red exclamation mark appears with a verbose error message on the Progress tab. This event message was derived by the Dts.Events.FireError method, which you implemented in the previous step.

7. On the Debug menu, click Stop Debugging.

8. On the Control Flow tab, double-click Script Task - Export Employees. In the Script Task Editor dialog box, click Design Script on the Script page.

9. In the Code Editor, append the following code in the Main method:

        Dim writer As StreamWriter     Dim con As OleDbConnection = New OleDbConnection(CONNECTION_STRING)     Try         Dim adapter As New OleDbDataAdapter(SQL_SELECT_EMPLOYEE, con)         Dim ds As New DataSet         adapter.Fill(ds)         Dts.Events.FireInformation(0, COMPONENT_NAME, _             String.Format("Opening output file '{0}'", fileName), _             "", 0, True)         writer = New StreamWriter (fileName, False)         writer.AutoFlush = True         With ds.Tables(0)             For row As Integer = 0 To .Rows.Count - 1                 For col As Integer = 0 To .Columns.Count - 1                     writer.WriteLine("{0}: {1}", _                         .Columns(col).ColumnName, .Rows(row)(col))                 Next col                 writer.WriteLine(RECORD_SEPARATOR)                 Dts.Events.FireProgress("Exporting Employee", _                     CInt((row + 1) / .Rows.Count * 100), row + 1, _                     .Rows.Count, COMPONENT_NAME, True)             Next row         End With         Dts.TaskResult = Dts.Results.Success     Catch ex As Exception         Dts.Events.FireError (0, COMPONENT_NAME, ex.Message, "", 0)         Dts.TaskResult = Dts.Results.Failure     Finally         …     End Try End Sub 

   The parameters for the Dts.Events.FireInformation method are almost the same as for the FireError method. The last parameter of FireInformation determines whether the SSIS engine suppresses the event when the second execution comes to the same statement. If the value is False, the SSIS engine doesn't raise the event for the rest of the execution. This sometimes increases the performance cost of package execution because firing an event is expensive.

   The FireProgress method requires six parameters. The first parameter is the description of the Progress event. The second through fourth parameters determine the percentage of the progress. The fifth parameter is the name of the component, and the last parameter is the same as the one in the FireInformation method.

10. Close VSA and the Script Task Editor dialog box.

11. In SSMS, right-click is2005sbsDW, point to Tasks, and select Bring Online. A small progress dialog box appears, and the database goes online.

12. Go back to BIDS and press F5 to execute the package. This time, the package is complete without errors.

13. Click the Progress tab. Your screen now looks like this:

    

    Notice that messages starting with [Export Employees] have been added to the Progress tab. The first message beginning with [Export Employees] Information is generated by the Dts.Events.FireInformation method, and following messages beginning with [Export Employees] Progress are generated by the Dts.Events.FireProgress method. The percentage shown in the message is calculated by the parameters of the Dts.Events.FireProgress method.

14. On the Debug menu, click Stop Debugging.

Providing Verbose Information to the Log File

If your Script Task has a complicated process, you might want to write out verbose information to a log file for debugging purposes. It is especially useful when the package has been deployed to the production environment, and you cannot use BIDS and VSA to debug the package.

SSIS enables you to implement logging code through the Dts.Log method. When the Dts.Log method is called in the script, the SSIS engine will route the message to the log providers that are configured in the containing package. You can set up different kinds of providers such as text file, SQL Server, and Windows Event Log. This logging capability will be discussed in more detail in Chapter 8, "Managing Package Execution."

In the following procedure, you will add script code to provide verbose information to the SSIS engine and configure the log settings of the containing package to route the message to a text file.

Configure Package Log Settings

1. On the Control Flow tab, select Script Task - Export Employees.

2. In the Properties pane, change the LoggingMode property to Enabled.

   By default, the Script Task is designed to inherit the log settings of the containing package. This step enables you to configure the task's log setting apart from the settings of the containing package.

3. Right-click anywhere on the design surface of the Control Flow tab and select Logging.

4. In the Containers pane, select the check box next to Package to enable logging.

5. On the Providers and Logs tab, select SSIS Log Provider For Text Files from the Provider Type drop-down list and click Add. A new entity appears in the list box.

6. Click the Configuration field in the new entity. Click the drop-down list box that appears in the Configuration field and select <New connection…>. The File Connection Manager Editor dialog box appears.

7. In the Connection Manager Editor dialog box, select Create File from the Usage Type drop-down list.

8. Click Browse, navigate to the root folder of the drive C, type Script Task Log.txt, and click Open. Your screen looks like this:

   

9. Click OK.

10. Select the check box next to SSIS Log Provider For Text Files. This will configure the package to use this log file.

    Important 

    Make sure that you are still selecting Package in the rightmost tree view. If you have selected Export Employee when you select the check box, select Package in the Containers pane tree view and check the SSIS Log Provider For Text Files entity again.

11. In the tree-view on the left side of the Configure SSIS Logs: Package dialog box, select the Script Task - Export Employees node. If the check box next to the node is not checked, click the check box to select this item.

12. Select the check box next to the SSIS Log Provider For Text Files entity again. Your screen now looks like this:

    

13. Click the Details tab and select the check box in the ScriptTaskLogEntry row. This allows SSIS to listen and log the message coming up from the Dts.Log method in Script Task - Export Employees. Your screen looks like this:

    

14. Click OK.

    Notice that the connection named Script Task Log.txt has been created in the Connection Managers pane. This object contains a path to the log file that you specified in the previous steps. If you double-click it, you can see the dialog box that you saw in the previous step.

    The package log configuration is all set. In the following procedure, you will add custom code to invoke the Dts.Log method.

15. Double-click the Script Task - Export Employees. In the Script Task Editor dialog box, click Design Script on the Script page.

16. In the Code Editor, insert the following code in the Main method:

     Try          Dts.Log("Opening database connection: " & _               con.ConnectionString, 0, Nothing)          Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)          Dim ds As New DataSet          adapter.Fill(ds)          Dts.Log(String.Format(_              "Retrieving {0} data, opening output file '{1}'", _              ds.Tables(0).Rows.Count, _              fileName), _              0, Nothing)         Dts.Events.FireInformation(0, COMPONENT_NAME, _              String.Format("Opening output file '{0}'", fileName), _              "", 0, True)         writer = New StreamWriter (fileName, False)         writer.AutoFlush = True         With ds.Tables(0)             For row As Integer = 0 To .Rows.Count - 1                 For col As Integer = 0 To .Columns.Count - 1                     writer.WriteLine("{0}: {1}", _                         .Columns(col).ColumnName, .Rows(row)(col))                 Next col                 writer.WriteLine(RECORD_SEPARATOR)                 Dts.Events.FireProgress("Exporting Employee", _                     CInt((row + 1) / .Rows.Count * 100), row + 1, _                     .Rows.Count, COMPONENT_NAME, True)             Next row         End With         Dts.TaskResult = Dts.Results.Success     Catch ex As Exception         Dts.Events.FireError (0, COMPONENT_NAME, ex.Message, "", 0)         Dts.Log("Exception detected: " & ex.ToString(), 0, Nothing)         Dts.TaskResult = Dts.Results.Failure     Finally         If writer IsNot Nothing Then             Dts.Log("Closing output file", 0, Nothing)             writer.Close()         End If     End Try End Sub 

17. Close VSA and the Script Task Editor dialog box.

18. Press F5 to execute the package. Confirm that the task turns green.

19. Start Windows Explorer and open the root folder of the drive C.

    The Script Task Log.txt should be created in the root folder of the drive C. Double-click the text file to open it in Notepad and confirm that the verbose messages are logged in the file.

20. Go back to BIDS and click Stop Debugging on the Debug menu.

Using Variables

Passing information from one task to the other is a common necessity. SSIS provides a temporary data storage called Variables; it is the most common way to pass information between tasks. Variables will be discussed in more detail in Chapter 8.

In the following procedure, you will add a variable to the package that holds the name of the result file and then modify the existing script to incorporate it. It is common to use a variable to output file names because you might need to change an output file name when you deploy the package to the production environment. This also applies to the database connection string settings.

Configure Variable Settings

1. Right-click anywhere on the design surface of the Control Flow tab and select Variables.

2. In the Variables pane, click the Add Variable button (in the top-left corner of the window). A new variable appears in the Name list.

   Note 

   Make sure the scope of the variable is displayed as Package. If it is Script Task - Export Employees, you selected the Script Task - Export Employees task when you clicked the Add Variables button. Delete the new variable (the delete button is the second one from the left), deselect the Script task, and then create the variable again.

3. Change the name of the new variable to ExportFileName.

4. Change the data type from Int32 to String.

5. Click the Value field and type C:\Employees_Default.txt. Your Variables window looks like this:

   

   The value you specified in the Value field is going to be a default value of the variable. This default value is valid until this variable is updated by package configurations or other tasks.

   Now the package variable is ready to use. To access this variable from the Script Task, you need to configure the ReadOnlyVariables property in the task by performing the following steps.

6. Double-click the Script Task - Export Employees. The Script Task Editor dialog box appears.

7. On the Script page, click the field next to the ReadOnlyVariables property and type ExportFileName.

   This allows you to access the ExportFileName variable from your script code. Your screen looks like this:

   

   Now the ExportFileName variable is ready to use. In the following procedure, you will modify the script code to look up this variable as a file name for the output file.

Modify the Script to Read Variables

1. Open VSA. If necessary, open the Script Task Editor dialog box and click Design Script on the Script page. Otherwise, double-click Script Task - Export Employee and click Design Script on the Script page in the Script Task Editor dialog box.

2. Replace the first line of the Main method, starting from Dim fileName As String …, with the following code:

    Dim fileName As String = Dts.Variables("ExportFileName").Value 

   Notice that your script code now has a blue wavy underline at Dts.Variables(ExportFile - Name).Value. This is the other user-aid function of the Code Editor. The underline indicates that something is wrong with the code. Place your mouse pointer over the blue wavy underline. A smart tag with a red exclamation mark appears. If you click the smart tag, the Code Editor shows the details of the error in the pop-up window, as shown below:

   

   The message in the pop-up window shows that SSIS cannot convert Dts.Variables(Export-FileName).Value to the String type implicitly. Because the type of variable is System.Object, you have to cast it explicitly into the expected type.

   The pop-up window also suggests a resolution if one is available. Click the Replace the Dts.Variables("ExportFileName").Value with … hyperlink that appears in the pop-up window. The Code Editor automatically replaces the target code with the appropriate code.

   Now your code looks like this:

    Public Sub Main()     Dim fileName As String = CStr(Dts.Variables("ExportFileName").Value)     Const COMPONENT_NAME As String = "Export Employees"     …     Try         …     Catch ex As Exception         …     Finally         …     End Try End Sub 

   The replaced code is casting Dts.Variables("ExportFileName").Value into the String type, using the CStr function. There are plenty of functions for data type conversion. Refer to MSDN for details.

3. Close VSA and the Script Task Editor dialog box.

4. Start Windows Explorer, open the root folder of the drive C, and delete the Employees.txt file.

5. Go back to BIDS and press F5 to execute the package. Confirm that the Script Task - Export Employees turns green.

6. Go back to Windows Explorer and ensure that Employees_Default.txt has been created in the root folder of the drive C. This means the new script in Script Task - Export Employees looked up the ExportFileName variable to obtain the export file name. Because no other tasks updated the variable before Script Task - Export Employees was executed, the variable remained the default value.

7. Go back to BIDS and click Stop Debugging on the Debug menu.

Modifying a Variable at Run Time

A run-time variable is often changed to a designated value from outside of the package during execution. There are several ways to change package variables at run time; using Configurations is the most common way to do it. Configurations will be discussed in Chapter 8. In the following procedure, you will add another Script task to update the ExportFileName variable before Script Task - Export Employees is executed.

Add a New Script Task and Update Variables at Run Time

1. In the Toolbox, in the Control Flow Items group, select Script Task and drag it to the design surface of the Control Flow tab.

2. Select the new Script task in Control Flow and drag the green arrow to Script Task - Export Employees. Your screen should look like this:

   

3. Double-click the new Script task. In the Script Task Editor dialog box, change the name to Script Task - Export Employees.

4. On the Script page, type ExportFileName in ReadWriteVariables. This allows you to update variables in your code.

5. Click Design Script and open VSA.

6. In the Code Editor, add the following row in the Main method:

    Public Sub Main()     '     ' Add your code here     '     Dts.Variables("ExportFileName").Value = "C:\Employees_Runtime.txt"     Dts.TaskResult = Dts.Results.Success End Sub Close VSA and the Script Task Editor dialog box. 

7. Press F5 to execute the package. Observe that both Script Task - Set ExportFileName and Script Task - Export Employees turn green.

8. Open the root folder of the drive C in Windows Explorer and confirm that the Employees_Runtime.txt file has been created instead of Employees_Default.txt. This means your package used a file path updated by Script Task - Set ExportFileName instead of using the variable's default value. Double-click to open C:\Employees_RunTime.txt in Notepad and verify that the result was exported correctly.