SolvedPrivate

vb scripts for ssis

Posted on 2014-09-24
4
33 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
ID: 40343435
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
ID: 40349263
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
ID: 40370621
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
ID: 40427518
Tanks Barry Cunney.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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