SQL 2005: SQL Server Integration Services
The Script Component
By: Zack Turnbow
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.
[Click to see full-size]
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.
[Click to see full-size]
Connect the output of the Derived Column component to the Script component.
[Click to see full-size]
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 to see full-size]
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.
[Click to see full-size]
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.
[Click to see full-size]
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.
[Click to see full-size]
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.
[Click to see full-size]
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.
[Click to see full-size]
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
Leave a Comment
#1 narayana
Posted By: narayana | 3.05.2009 at 12:55 AM
very good
Explained elabarately
#2 Pankaj Sharma
Posted By: Pankaj Sharma | 5.19.2009 at 12:02 PM
I am creating a calculation routine in the form of a script component. I am re-creating this script component in 4 dataflows across 3 different ssis packages. How can I create a single script component and re-use it?
#3 server racks
Posted By: server racks | 12.23.2009 at 1:28 AM
This is great!
#4 Casino Führer
Posted By: Casino Führer | 2.19.2010 at 4:18 AM
SQL Server enables you to store data from structured, semi-structured and unstructured documents, such as images and music, directly within the database. And it includes integrated services that enable you to query, search, synchronize, report and analyse your data.
Very interesting post you have there and i find this site very interesting, i think i will Bookmark it and start doing following. Continue with this great job i will always stop by to read your articles
#5 movie watch online
Posted By: movie watch online | 3.07.2010 at 3:37 AM
hi webmaster, I found this website from altavista and read a few of your other articles. They are gorgeous. Please keep it up. Sincerely, Cem.
#6 220-702
Posted By: 220-702 | 3.19.2010 at 4:35 AM
Great code, I hope, that it will help my in my project, but any way, you have done great work.
#7 mosquito trap
Posted By: mosquito trap | 4.14.2010 at 4:30 PM
I've already bookmark this post and will definitely refer this to all my close friends who are IT professionals.very helpful to all the working professionals as well as students working on SQL
#8 testinside 70-291
Posted By: testinside 70-291 | 4.25.2010 at 1:23 PM
Excellent explained. I like this idea.
#9 wholesale laptop adapter
Posted By: wholesale laptop adapter | 5.21.2010 at 1:57 AM
d 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
#10 air max 2009
Posted By: air max 2009 | 5.23.2010 at 4:58 AM
nike air max sale
#11 wholesale shoes
Posted By: wholesale shoes | 5.24.2010 at 3:28 AM
Spring summer pocket perfect supporting role (figure) except heart shape brooch, earring, necklace,
#12 BPO Services
Posted By: BPO Services | 5.26.2010 at 2:44 AM
Script Transcription refers to the creation of a textual representation of movies and television shows, documentaries and news programmes etc. It also includes the formatting and retyping of existing scripts in various formats into a single consolidated digital format............................ bposervicesagileinformatics.blogspot.com
#13 Family Health
Posted By: Family Health | 5.26.2010 at 11:49 PM
Patient safety incidents can have devastating emotional and physical consequences for patients and their families and can be distressing for the professionals involved............................... http://www.familyhealthcare.in
#14 Business Process Outsourcing
Posted By: Business Process Outsourcing | 5.27.2010 at 1:33 AM
e 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........... bpoagileinfo.blogspot.com
#15 coach purses
Posted By: coach purses | 6.01.2010 at 5:46 AM
Whether you shop for Coach purses on sale or Coach bags at eBay or an Outlet Store, each place comes with its share of pros and cons. However, I feel the pros of shopping at eBay heavily outweigh the cons. Sure; you have to spend a little time verifying that your Coach Purse is authentic, but in the end you will be rewarded generously by receiving a great handbag for a cheap price. With more choices and deeper discounts than any Coach Outlet store, eBay is definitely worth your consideration the next time you are in need of coach purses on sale.
#16 5.11 pants
Posted By: 5.11 pants | 6.01.2010 at 6:00 AM
Wish I have the luxury of time to consider using the benefits these site can offer.....
#17 discount coach purses
Posted By: discount coach purses | 6.11.2010 at 3:36 AM
Great!This article is creative，there are a lot of new idea，it gives me inspiration.I think I will also inspired by you and think about more new ideas.
#18 moncler down jackets
Posted By: moncler down jackets | 6.13.2010 at 2:05 AM
Here elaborates the matter not only extensively but also detailly .I support the write's unique point.It is useful and benefit to your daily life.You can go those <a href=" conditions-encountered.com " >wslmart.net </a> sits to know more relate things.They are strongly recommended by friends.Personally
#19 van rental service
Posted By: van rental service | 6.14.2010 at 2:59 AM
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.
#20 michael jordan shoes
Posted By: michael jordan shoes | 6.18.2010 at 8:50 PM
good post!!thank you
#21 dizi izle
Posted By: dizi izle | 6.22.2010 at 7:16 AM
Super-Duper site! I am loving it!! Will come back again - taking you feeds also, Thanks.
#22 coach outlet
Posted By: coach outlet | 7.06.2010 at 9:59 PM
thank you!I learned a lot from this article.
#23 sql queries
Posted By: sql queries | 7.15.2010 at 3:50 PM
Interesting post and thanks for sharing. Some things in here I have not thought about before.Thanks for making such a cool post which is really very well written.will be referring a lot of friends about this.Keep blogging.
#24 tiffany jewelry
Posted By: tiffany jewelry | 7.18.2010 at 11:29 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#25 tiffany necklace
Posted By: tiffany necklace | 7.18.2010 at 11:29 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#26 cheap ugg boots
Posted By: cheap ugg boots | 7.18.2010 at 11:29 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#27 ugg short
Posted By: ugg short | 7.18.2010 at 11:30 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#28 ghd flat iron
Posted By: ghd flat iron | 7.18.2010 at 11:30 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#29 ghd hair straightener
Posted By: ghd hair straightener | 7.18.2010 at 11:30 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#30 tiffany co
Posted By: tiffany co | 7.21.2010 at 3:02 AM
For classic and quality genuine silver jewelry, many people choose Tiffany & Co brand.Now,we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#31 tiffany jewelry
Posted By: tiffany jewelry | 7.21.2010 at 3:02 AM
For classic and quality genuine silver jewelry, many people choose Tiffany & Co brand.Now,we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#32 tiffany jewellery
Posted By: tiffany jewellery | 7.21.2010 at 3:02 AM
For classic and quality genuine silver jewelry, many people choose Tiffany & Co brand.Now,we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#33 tiffany necklace
Posted By: tiffany necklace | 7.26.2010 at 6:59 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with
fantastic discount.more http://www.tiffanyonsale.com/
#34 tiffany bracelet
Posted By: tiffany bracelet | 7.26.2010 at 7:00 AM
This is exactly cheap tiffany jewelry you can get cheap Tiffany Rings, Necklaces, Errings, Bracelets and other Tiffany Jewellery.get more http://www.tiffanyonsale.com/
#35 tiffany bracelet
Posted By: tiffany bracelet | 7.26.2010 at 7:00 AM
This is exactly cheap tiffany jewelry you can get cheap Tiffany Rings, Necklaces, Errings, Bracelets and other Tiffany Jewellery.get more http://www.tiffanyonsale.com/
#36 tiffany necklace
Posted By: tiffany necklace | 7.26.2010 at 7:00 AM
This is exactly cheap tiffany jewelry you can get cheap Tiffany Rings, Necklaces, Errings, Bracelets and other Tiffany Jewellery.get more http://www.tiffanyonsale.com/