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

In this blog

The dodgy data file
Creating the SQL Server tables
Deleting initial data from these tables
Script to validate data - C#
Script to validate data - Visual Basic

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

BLOGS BY TOPIC

BLOGS BY AUTHOR

Files needed for YouTube tutorial on SSIS script components

The dodgy data file

Creating the SQL Server tables

Deleting initial data from these tables

Script to validate data - C#

Script to validate data - Visual Basic

Head office

London

Manchester