SolvedPrivate

vb scripts for ssis

Posted on 2014-09-24
4
29 Views
Last Modified: 2016-02-10
Guys,
As I'm started to working with SSIS sql2005 - sql2012 heavily. I'm interested to get more knowledge with how to running and excute vb scripts within a package. I means more how and at what situation should I use vb scripts instead of T-sql writing.

I really need to speed up on this task, if any one know any great resource for learning or any commercial tools for developing vbscripts for ssis packages, let me know .....
0
Comment
Question by:motioneye
  • 2
  • 2
4 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 500 total points
Comment Utility
Hi motioneye,
Typically if you wish to write 'Visual Basic(VB)' in SSIS, you would use a 'Script Task'.
The Script Task component is available in the Toolbox when designing an SSIS package in Business Intelligence Development Studio(BIDS) and you can drag it onto the 'Control Flow' pane just like any other component in SSIS.

With the Script Task component, you actually have a choice: you can code the script task in either VB.net or C#.

Also you can pass SSIS variables to the script task.

The Script Editor within BIDS is a good tool itself and I have always found it adequate when coding Script Tasks.


To give you some example of use cases:
I worked on an ETL project in the last year and some of the source data flat files contained a 'trailer' row which was totally different from all the main data rows in the file.
Also for some reason, sometimes this 'trailer' row could appear in the middle of the data rows(I think that this was a bug in the source systems).
So therefore, in SSIS, we coded a C# Script task which cleansed these source files, removing the 'trailer' row, before passing the cleansed file to a data flow task.
This custom C# script task used the standard C# file StreamReader and Streamwriter objects to do the work.
We dynamically passed each file name to be processed, to the Script Task, via a variable.


Also I am working on another mini-project right now, developing an SSIS solution that takes data from report flat files produced by Microstrategy and transfers this data into SQL Server database tables.
The flat files produced by Microstrategy contain some 'header' records with the report name, etc., so therefore as part of the SSIS solution, we are coding a Script Task which first removes these 'header' records before the files are processed by a Data Flow task.



A further reason for sometimes using a Script Task as part of an SSIS solution is performance:
If source files are extremely large, and some cleansing or checking needs to be undertaken on these source files, an SSIS Script Task which processes the raw files on disk, may be quicker than doing similar actions in T-SQL when the data is an database table.



There actually may already be some Articles or Webinars on Experts Exchange discussing SSIS and Script Tasks and if not, there is an option in Experts Exchange to request an article on a particular topic.


Please let me know if my points above are helpful and come back to me with further questions.
0
 

Author Comment

by:motioneye
Comment Utility
Hi Barry Cunney,
Thanks for the some explanation how and when to use vbscripts with SSIS, what trouble me now is I dont have or very little knowledge with vbscripts or C#, that  make some limit to me while doing some SSIS stuff for ETL.
However I'm willing to learn but I feel its hard for me to understands the programming languages. Do you have any recommendation how I can start up using vb within SSIS packages.
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
Comment Utility
Hi motioneye,
There is a very good article on Experts Exchange titled "SSIS debugging your variable's value"

This would be a good starting point, as it uses a Script Task with VB.net code to track and display the value of an SSIS variable in a message box.
I would advise trying to re-create similar Script Task in your environment and get it working to display message boxes with the value of a variable.
Try and go through this article and please come back with any specific questions.
0
 

Author Closing Comment

by:motioneye
Comment Utility
Tanks Barry Cunney.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now