This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

A blog to accompany our YouTube tutorial on script components in SSIS
This blog provides the SQL, C# and VB script needed for the Integration Services script components tutorial.

Posted by Andy Brown on 19 February 2014

Files needed for YouTube tutorial on SSIS script components

If you are viewing our YouTube tutorial on using script components in SSIS, this blog will save you typing!

The dodgy data file

You can copy this text into a new text file called Contestants.txt to create your dodgy data file!

Shayne Ward,2,1,Louis Walsh
Matt Cardle,7,1,Dannii Minogue
Wally Owl, 10,1,Basil Brush
Joe McElderry
Rebecca Ferguson, 7,2,Cheryl Cole
2,1
Alexandra Burke, 5,1, Cheryl Cole

Alternatively, download a zipped version of the file here.

Creating the SQL Server tables

To create the good and bad contestant tables, run this script in Management Studio:

-- make sure you're using an existing database

USE [X-Factor]

-- create the two tables

CREATE TABLE tblGoodContestant(

[ContestantName] [varchar](255) NULL,

[SeriesNumber] [int] NULL,

[Position] [int] NULL,

[Mentor] [varchar](255) NULL

) ON [PRIMARY]

CREATE TABLE tblBadContestant(

[ContestantName] [varchar](255) NULL,

[SeriesNumber] [int] NULL,

[Position] [int] NULL,

[Mentor] [varchar](255) NULL,

[Problem] [varchar](max) NULL

) ON [PRIMARY]

Deleting initial data from these tables

Attached to the initial Execute SQL task, write these commands:

TRUNCATE TABLE tblGoodContestant

TRUNCATE TABLE tblBadContestant

Script to validate data - C#

To save you typing, here's the C# script to validate the data:

// if any of the fields are null, say so and exit

if (Row.ContestantName_IsNull ||

Row.Position_IsNull || Row.SeriesNumber_IsNull ||

Row.Mentor_IsNull)

{

Row.Problem = "At least one column is not filled in";

Row.IfGood = false;

return;

}

// try to convert series/position numbers to integers

int s = 0;

int p = 0;

try

{

s = Convert.ToInt32(Row.SeriesNumber);

}

catch

{

s = 0;

}

try

{

p = Convert.ToInt32(Row.Position);

}

catch

{

p = 0;

}

// if either still 0, we couldn't convert

if (s == 0 || p == 0)

{

Row.Problem = "The series and position numbers aren't both integers";

Row.IfGood = false;

return;

}

// store the correct numbers for position and series

Row.intSeries = s;

Row.intPosition = p;

// trim the mentor and contestant fields

Row.Mentor = Row.Mentor.Trim();

Row.ContestantName = Row.ContestantName.Trim();

// record fact all worked OK

Row.Problem = "";

Row.IfGood = true;

Script to validate data - Visual Basic

Here's the Visual Basic script to do the same thing:

'if any of the fields are null, say so and exit

If Row.ContestantName_IsNull Or Row.Position_IsNull Or

Row.SeriesNumber_IsNull Or Row.Mentor_IsNull Then

Row.Problem = "At least one column is not filled in"

Row.IfGood = False

Return

End If

'try to convert series/position numbers to integers

Dim s As Integer = 0

Dim p As Integer = 0

Try

s = Convert.ToInt32(Row.SeriesNumber)

Catch

s = 0

End Try

Try

p = Convert.ToInt32(Row.Position)

Catch

p = 0

End Try

'if either still 0, we couldn't convert

If s = 0 Or p = 0 Then

Row.Problem = "The series and position numbers aren't both integers"

Row.IfGood = False

Return

End If

'store the correct numbers for position and series

Row.intSeries = s

Row.intPosition = p

'trim the mentor and contestant fields

Row.Mentor = Row.Mentor.Trim

Row.ContestantName = Row.ContestantName.Trim

'record fact all worked OK

Row.Problem = ""

Row.IfGood = True

You should now be good to go with the tutorial!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR