BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!
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!