Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
If you are viewing our YouTube tutorial on using script components in SSIS, this blog will save you typing!
You can copy this text into a new text file called Contestants.txt to create your dodgy data file!
Alternatively, download a zipped version of the file here.
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
Attached to the initial Execute SQL task, write these commands:
TRUNCATE TABLE tblGoodContestant
TRUNCATE TABLE tblBadContestant
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;
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.