Using Integration Services to Insert Data

In the real world, things are not always as easy as expected. Try to migrate an application based on an Access database or on .dbf files created with dBase or Fox and you will see how difficult it can be. Occasionally, you may need to obtain information from other sources, such as mainframes or other database or legacy systems. You may also need to create a new application based on an existing one and enhance the database while users continue to use the legacy application during your development process.

These three examples demonstrate the need to understand the features of Integration Services. You will initially address Integration Services by creating a simple export package using a wizard.

Creating an Export Package

1. In SQL Server Management Studio, right-click the AdventureWorks database in Object Explorer and select Tasks Export Data. This will start the SQL Server Import And Export Data Wizard.

   

2. After the welcome page appears, your server and database will already be selected in the Choose A Data Source page.

   

3. On the Choose A Destination page, select Flat File Destination from the Destination drop-down list and enter a filename.

4. Check the Column Names In The First Data Row checkbox to add column names at the beginning of the text file.

   

5. On the Specify Table Copy Or Query page, keep the Copy Data From One Or More Tables Or Views option selected.

   

6. On the Configure Flat File Destination page, select [AdventureWorks].[Sales].[CurrencyRate].

   

7. On the Save And Execute Package page, uncheck the Execute Immediately checkbox. Check the Save SSIS Package option and select the File System option.

   

8. In the Package Protection Level dialog box, select Do Not Save Sensitive Data from the drop-down list.

   

9. Finally, in the Save SSIS Package dialog box, assign a name and filename to your package.

   

Editing the Package

1. Open Visual Studio 2005.

2. On the File menu, point to Open and then click File

3. Navigate to your package file (with the .dtsx extension) and open it. You will see something similar to the following figure.

   

   The Package editor displays four tabs.

   • Control Flow

   • Data Flow

   • Event Handlers

   • Package Explorer

• The Data Flow tab displays the exporting process from a database connection to a flat file.

  

  4. Right-click each element, the connections, and the green arrow to view how the package stores all of the information needed to execute the task. Moreover, move your mouse pointer over the Toolbox tab to view a variety of tasks and elements that are available to use in a package.

Creating Your Own Package

Consider the following scenario: You need to update the CurrencyRates table with new information provided in an Excel worksheet. The requirements state that you must assign the datetime fields with the dates of the insert process. To accomplish this, you must intercept the import process to assign the date and time.

Creating a New Project

1. Open Visual Studio 2005.

2. From the File menu, select New Project, then select the Business Intelligence Projects type and the Integration Services Project template.

   

3. Enter a name for your project. When you click OK in the dialog box, you will view the same environment that is seen when you open the package created by the wizard (as in Step 3 in the previous procedure).

Defining the Source

1. Select the Data Flow tab. From the Data Flow Sources section in the Toolbox, drag an Excel Source to the designer.

   

2. Right-click it (at the bottom of the designer) and select Edit.

3. Click the New button to the right of the OLE DB Connection Manager drop-down list.

   

4. Browse to the \Ch10\SSIS\CurrencyRate200602.xls file in the sample files, click the Open button, and then click OK.

5. From the Name Of The Excel Sheet drop-down list, select CurrencyRate$ and click OK. This completes the source configuration.

   

Defining the Destination

1. From the Data Flow Destinations section in the Toolbox, drag a SQL Server Destination.

2. Right-click it and select Edit. You will receive a message that there is no connection.

3. To the right of the OLE DB Connection Manager drop-down list, click the New button and define the connection to AdventureWorks in your database.

4. Select [Sales].[CurrencyRate] from the Use A Table Or View drop-down list and click Cancel. You must follow additional steps to complete the definition of this destination, which you will perform later.

   

Defining the Transformation

1. From the Data Flow Transformations section in the Toolbox, drag a Script Component. When you do this, a dialog box will appear.

2. Select Transformation and click OK.

Defining the Process

1. Right-click the Script Component and select Add Path.

2. In the dialog box, select Excel Source in the From drop-down list and Script Component in the To drop-down list. When you click the OK button, a new dialog box appears. Select Excel Source Output from the Output drop-down list and click OK.

3. Right-click and again select Add Path on the Script Component. Choose SQL Server Destination from the To drop-down list.

4. Right-click the Script Component and select Edit.

5. In the Input Columns step, check the following fields:

   • FromCurrencyCode

   • ToCurrencyCode

   • AverageRate

   • EndOfDayRate

6. Click the Inputs And Outputs step, expand the Output node, click the Output Columns folder, and then click the Add Column button.

7. Enter ThisDate as the name of the column, and select Date [DT_DATE] as the datatype.

8. Using the same datatype, add another column with ThisDate2 as the name. (You need one output column for each destination column.)

9. Add two additional columns named AvgRate and EODRate , and select Currency [DT_CY] as the datatype.

10. In the Script step, click the Design Script button.

11. Add the following code in the Input0_ProcessInputRow method. You can find the code at \Ch10\SSIS\ScriptMain.vb in the sample files.

       Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)     'Get the date and time from the system     Dim ActualDate As Date = System.DateTime.Now     'Assign the same value to both columns     With Row         .ThisDate = ActualDate         .ThisDate2 = ActualDate         'Convert types to currency destination         .AvgRate = CDec(.AverageRate)         .EODRate = CDec(.EndOfDayRate)     End With End Sub   

12. Close the Script Editor and click OK in the Edit Script Component dialog box.

13. Right-click the SQL Server Destination and select Edit.

14. Select [Sales].[CurrencyRate] from the Use A Table Or View drop-down list.

15. In the Mapping step, assign <ignore> to the CurrencyRateID column (it is an identity column). Assign ThisDate to CurrencyRateDate and ThisDate2 to ModifiedDate. Assign AvgRate to AverageRate and EODRate to EndOfDayRate.

16. Save the package.

Note 

You will see an alert icon in the SQL Server Destination component. This alert appears because the Script Component manages a string without any length control. Since the destination field has a specific size for the From and To Currency codes, the alert informs you that some data may be truncated.