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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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]

GO

-- 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]

GO

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! 

This blog has 0 threads Add post