SQL Server Integration Services Script

Introduction

Continuing this introductory series about SSIS, we will start getting into some uncommon components. The Script component is often used for sanitizing the data when one of the existing components does not do the job. A common use for this component is checking for null for data that should not be null in the database. In Visual Studio 2005, the Script Component uses Visual Basic as the programming language to get the job done. You can import dlls just you can when doing a regular development project.

Try Server Intellect for Windows Server Hosting. Quality and Quantity!

Scenario

Since the company is collecting a processing charge now, it needs to be a bit more accurate in regards to partial cents. So the processing charge needs to be rounded using standard rounding techniques. This will be accomplished by using the Script component.

Implementation

Again, create a new package in the SSIS project. Copy the Data Flow component from the Derived package into the Control of the new project. Change the name of the project to Script then go to the data flow tab and delete the connection between the Derived output and the Multicast component. Rebuild the Vendor A and Vendor B connections then configure their respective Flat File Sources to use those connections. Also rebuild the connection for the Flat File Destination component and the OLE DB Destination component.

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.

Drag a Script component from the toolbox and put it under the Derived Column component. The moment you place it into the work area a dialog box will open to ask what type of Script component that is needed. A Script component can be used as a data source where you can bring data in from code instead of the offered data input components. It can also be used as a destination component where the data can be ported to another destination not found in the toolbox. For the purpose of this article, the option used will be a transformation script.

Connect the output of the Derived Column component to the Script component.

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

Double click the Script component to configure it. The first tab shown will be the Input Column tab. Here you can select which columns you would like to use inside the script. Click the ProcessingCharge check box since we will be working with that data. The unselected columns will be passed through unaffected.

Click the Inputs and Outputs tab. Here we can see what data is available for the script. Expand the inputs and a list of the checked columns can be viewed. Since we want to change the data for the ProcessingCharge column, we need an output column to stick the modified information in. So expand the Output tree and then the Output Columns tree. When that has been achieved, the Add Column button is enabled. Click the Add Column button and give it the name ProcessingChargeAdjusted to indicate that this information has been modified from the original value. On the right hand side are the available properties that can be set. Change the data type to float since we would like to retain the decimal aspect of the data.

We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.

Now go to the Script tab and the script properties will be displayed. Click the Design Script and a separate Visual Studio window will open up with standard Visual Basic code to be used inside the package. So let’s add some code to round the processing charge to the nearest cent and stick the result in the output column. To access the input and output columns simple type: row. and use intellisense to select the proper column that is needed.

Save the code and close the code window. Click OK on the Script component window. Now connect the output of the Script component to the Multicast component. Since we have a new column that has data we are interested in, the Flat File Destination component needs to be re-configured to use the new column instead of the original column. Open up the Flat File Destination component and go to the Mappings tab. Click on the line connecting the ProcessingCharge columns and press the delete button. Then connect the ProcessingChargeAdjust column on the list on the left to the ProcessingCharge column on the right.

If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.

Repeat the action for the OLE DB Destination to use the new column.

Testing the Package

For testing purposes, add a data viewer, which was covered in a previous article, to the link between the Script component and the Multicast component. Go ahead and run the package to see the result of using the Script component.

You can see that the data is showing up the way it is needed. Once satisfied, click the green arrow button to resume processing the package. Make sure that the package passes and then check the database and flat file to view the updated information.

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

What have we learned?

How to configure a Script component.
How to use the Script component to manipulate data.
How to use the Script component add a new column to the data set being processed.

Attachments