The standard SSIS task components provide a robust array of services, but the loop task components (For Loop and Foreach Loop Containers) are unacceptably slow, iterating in the area of dozens of loops per second. VB.NET loops are much faster, capable of iterating at tens of thousands of loops per second. When an SSIS job requires repetitive processing through thousands of records, a custom script task can be very useful.
It is not necessary to know the VB.NET universe in order to write valuable VB.NET script tasks. Keep in mind that it is being used as a scripting language, not a full-fledged application development language. The ability to perform the following basic operations will allow you to do quite a lot.
- Create a VB.NET script task
- Create dialog boxes for data input and output
- Access SSIS variables
- Parse strings with the split function
- Create and loop through list arrays
- Read/write ASCII files
- Copy, move and delete files
- Capture a listing of specified files in a subdirectory
- Email a file
- Create a database connection to enable VB.NET to talk to SQL Server
- Execute T-SQL queries and stored procedures and capture any results
The purpose of this article is to provide easily-understood example VB.NET code of the above tasks for SSIS package developers who know a programming language, but are unfamiliar with VB.NET.
Lesson 1: Create an SSIS package with a VB.NET script task
Open Business Intelligence Development Studio, click the "Create:" link, select "Integration Services Project," type the name of the project in the "Name:" text box and click the "OK" button.
A default SSIS package named "Package" will have been created. Double-click on the "Script Task" control flow item to add a script task to the package.
Double-click on the Script Task and select "Microsoft Visual Basic 2008" from the Script Language drop-down list, then click on the "Edit Script" button.
At this point you have created a script task template ready to accept your VB.NET code.
This script task was created in SSIS 2008, but the following instructions will work with SSIS 2005.
Lesson 2: Create an output dialog box
Replace the "Add your code here" text in the Main procedure with the follwing three statements.
Dim message As String = "This variable holds the dialog box message." Dim caption As String = "This variable holds the dialog box title." Dim button As DialogResult = MessageBox.Show(message, caption, MessageBoxButtons.OK)("scriptmain",> (false)>
Save and execute the package to display the following dialog box.
Among other uses, the output dialog box provides the ability to display the contents of variables while developing a package. Development Studio provides means for doing the same thing, but a popup is a quick and easy way of doing it in your code.
Lesson 3: Create an input dialog box
Replace the VB.NET script task code in the Main procedure from the previous lesson with the
Dim data As String = InputBox("Please enter your data.", "Data Input")
Dim button As DialogResult = MessageBox.Show(data, "Data Output", MessageBoxButtons.OK)
Save and execute the package to display the following dialog box.
Enter some characters into the text box and press the "OK" button. The data entered will be displayed in the output dialog box.
A data input dialog box enables the user to interact with an SSIS package at run time.
Lesson 4: Access an SSIS global variable
Add a global variable to the package by clicking the "Add Variable" button of the variables panel.
Change the name of the variable to "TestVariable," its data type to "String," and it's value to "InitialValue."
Double-click on the Script Task component to bring up the script task editor.
Click on the "ReadWriteVariables" button to bring up the "Select Variables" window.
Check the box in the "User:TestVariable" line and click the "OK" button.
The script task now has read/write access to the TestVariable. Click the "OK" button to end the variable configuration session.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
Dim temp As String
temp = Dts.Variables("TestVariable").Value.ToString
Dim data As String = InputBox("The current value of TestVariable is " & temp & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Please enter a new value for TestVariable.", "Data Input")
Dts.Variables("TestVariable").Value = data.ToString
temp = Dts.Variables("TestVariable").Value.ToString
Dim button As DialogResult = MessageBox.Show(temp, "Final Value of TestVariable", MessageBoxButtons.OK)
The first dialog box displays the current value of TestVariable and prompts for a new value.
Enter a new value and click the "OK" button.
Being able to read and write global SSIS variables enables transfer of information between SSIS package components.
Lesson 5: Parse a string with the Split() function
In order to transfer data from an ASCII file to a table, each line of the file must be parsed into individual fields. A file that has the fields separated by commas (comma-separated values, or CSV) can be parsed with the VB.NET Split() function.
Here is an example of a line being parsed into individual values with the Split() function.
Dim line As String = "Blow,Joe,1234 Main Street,Anywhere,USA"
Dim value() As String = Split(line, ",")At this point, value(0) contains "Blow", value(1) contains "Joe", value(2) contains "1234 Main Street", value(3) contains "Anywhere", and value(4) contains "USA."
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
("scriptmain",> Dim line As String = "Blow,Joe,1234 Main Street,Anywhere,USA" Dim value() As String = Split(line, ",") Dim button As DialogResult = MessageBox.Show(value(0), "First Value", MessageBoxButtons.OK) button = MessageBox.Show(value(1), "Second Value", MessageBoxButtons.OK) button = MessageBox.Show(value(2), "Third Value", MessageBoxButtons.OK) button = MessageBox.Show(value(3), "Fourth Value", MessageBoxButtons.OK) button = MessageBox.Show(value(4), "Fifth Value", MessageBoxButtons.OK) (false)>
The output dialog boxes confirm the content of the value() array.
Lesson 6: Create and loop through an array list
An array list is an object that can store a list of values in order and release them in the same order.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
("scriptmain",> Dim MyList As New ArrayList MyList.Add("Apple") MyList.Add("Orange") MyList.Add("Banana") MyList.Add("Peach") MyList.Add("Cherry") Dim i As Integer Dim value As String For Each value In MyList (false)>("scriptmain",> Dim button As DialogResult = MessageBox.Show(value.ToString(), "ArrayList Value " + i.ToString, MessageBoxButtons.OK) (false)>("scriptmain",> i += 1 Next (false)>
This code loads five values into an ArrayList named MyList.
Dim MyList As New ArrayList
MyList.Add("Apple")
MyList.Add("Orange")
MyList.Add("Banana")
MyList.Add("Peach")
MyList.Add("Cherry")This code loops through the MyList ArrayList and displays the list values in sequence.
Dim value As String
For Each value In MyList
Dim button As DialogResult = MessageBox.Show(value.ToString(), "Fruit", MessageBoxButtons.OK)
NextThe output dialog boxes confirm the values contained by the array list.
Lesson 7: Write an ASCII file
This lesson uses a StreamWriter object to create an empty ASCII file with the CreateText method and write five lines of text to the file.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
("scriptmain",>Writer Dim (false)>("scriptmain",>Writer As System.IO.StreamWriter Writer = IO.File.CreateText("C:\MyFile.txt") (false)>("scriptmain",>Writer .WriteLine("Line 1") (false)>("scriptmain",>Writer .WriteLine("Line 2") (false)>("scriptmain",>Writer .WriteLine("Line 3") (false)>("scriptmain",>Writer .WriteLine("Line 4") (false)>("scriptmain",>Writer .WriteLine("Line 5") (false)>("scriptmain",> .Close() (false)>
This code creates the empty text file C:\MyFile.txt.
Writer = IO.File.CreateText("C:\MyFile.txt")This code writes a line of text to the file.
Writer.WriteLine("Line 1")Open a file manager window and find the "C:\MyFile.txt" ASCII file you just created.
Double-Click on it to open it in Notepad to confirm that the five lines of text inserted by the StreamWriter are there.
Lesson 8: Read an ASCII file
This lesson uses a StreamReader object to read the ASCII file created in the previous lesson.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
("scriptmain",> Dim Reader As New System.IO.StreamReader("C:\MyFile.txt") Dim line As String = "" Do (false)>("scriptmain",> line = Reader.ReadLine (false)>("scriptmain",> If Not line Is Nothing Then (false)>("scriptmain",> Dim button As DialogResult = MessageBox.Show(line, "This is Line # " + Right(line, 1), MessageBoxButtons.OK) (false)>("scriptmain",> End If Loop Until line Is Nothing (false)>
The resulting popups confirm that it is reading the file we created.
Lesson 9. Move, copy and delete files
Make sure that your C:\ drive has a C:\Temp\ subdirectory. Create one if it does not.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
("scriptmain",> Dim filename As String = "MyFile.txt" Dim file As New System.IO.FileInfo("C:\" & filename) (false)>("scriptmain",> Try (false)>("scriptmain",> file.MoveTo("C:\Temp\" & filename) (false)>("scriptmain",> Catch ex As Exception (false)>("scriptmain",> Dim button As DialogResult = MessageBox.Show("The file does not exist.", "ERROR MESSAGE", MessageBoxButtons.OK) (false)>("scriptmain",> End Try (false)>
MyFile.txt is gone from C:\, but is now in C:\Temp\.
To copy the file instead of moving it, change
file.MoveTo("C:\Temp\" & filename)to
file.CopyTo("C:\Temp\" & filename).To delete the file instead of moving it, change
file.MoveTo("C:\Temp\" & filename)to
file.Delete().Lesson 10: Capture the names of all the matching files in a subdirectory to an array list
Make four copies of the MyFile.txt file in C:\TEMP\ and rename the five files MyFile1.txt, MyFile2.txt, MyFile3.txt, MyFile4.txt and MyFile5.txt as shown below.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it.
Dim message As String = "The following files matching the pattern 'My*' were found in C:\Temp\" & Chr(13) & Chr(10) & Chr(13) & Chr(10)
Dim di As New System.IO.DirectoryInfo("C:\Temp\")
Dim aryFi As IO.FileInfo() = di.GetFiles("My*")
Dim fi As IO.FileInfo
For Each fi In aryFi
message += di.ToString & fi.ToString & Chr(13) & Chr(10)
Next
Dim button As DialogResult = MessageBox.Show(message, "Message", MessageBoxButtons.OK)
The code
Dim aryFi As IO.FileInfo() = di.GetFiles("My*")tells the DirectoryInfo object to look for files starting with the two characters "My."
The code
For Each fi In aryFi
message += di.ToString & fi.ToString & Chr(13) & Chr(10)
Nextloops through the values indexed by the loop index 'fi.'
The popup confirms that only files beginning with "My" were found.
Lesson 11: Email a file
Email requires the addition of two libraries to the Imports statements of the script task.
Imports System.Net.Mail
Imports System.NetReplace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it. (You can't actually run this code if you do not have access to an SMTP server and know its IP address.)
'Imports System.Net.Mail (Uncomment and add to the Imports section) 'Imports System.Net (Uncomment and add to the Imports section) Dim SenderEmail As String = "SenderAddress@domain.com" Dim RecipientEmail As String = "RecipientAddress@domain.com" Dim Subject As String = "Here is the data" Dim Message As String = "Attached is your data. Have fun." Dim AttachmentPath As String = "C:\TEMP\MyFile1.txt" Dim SmtpAddress As String = "123.123.123.123" Dim myHtmlMessage As MailMessage Dim mySmtpClient As SmtpClient myHtmlMessage = New MailMessage(SenderEmail,RecipientEmail,Subject,Message) myHtmlMessage.Attachments.Add(New Attachment(AttachmentPath)) mySmtpClient = New SmtpClient(SmtpAddress) mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(myHtmlMessage) myHtmlMessage.Dispose()
Replace the bogus values in the first six statements with real values, execute the code, and an email with attachment will be sent.
To add more attachments, add more myHtmlMessage.Attachments.Add statements and AttachmentPath variables.
Lesson 12: Create a database connection
Create the Customer table by executing the following T-SQL code in SQL Server Management Studio.
BEGIN TRY DROP TABLE Customer END TRY BEGIN CATCH END CATCH CREATE TABLE Customer ( CustomerId INT, LastName VARCHAR(20), FirstName VARCHAR(20), City VARCHAR(20), State VARCHAR(2), ZipCode VARCHAR(10), Phone VARCHAR(12) )
Browse the SQL Server Managment Studio Object Explorer to confirm that the table was created.
Next, we need an ADO.NET connection to the server and database containing the Customer table so that our SQL Statements can talk to it.
Create an ADO.NET connection to the server/database by right-clicking the "Connection Managers" panel and selecting "New ADO.NET Connection..." from the menu.
Click the "New" button of the "Configure ADO.NET Connection Manager" window.
Select the names of the server and database where you created the Customer table from the drop-down menus and click the "OK" button.
Click the "OK" button to conclude the connection manager configuration session.
You can see that a new ADO.NET connection manager has been added to the package.
Lesson 13: Execute an INSERT query
In order to execute T-SQL statements, the System.Data.SqlClient library needs to be added to the Imports section of the script task.
Imports System.Data.SqlClientThis statement will insert one record into the Customer table.
INSERT INTO TestDB.dbo.Customer
(CustomerId,LastName,FirstName,City,State,ZipCode,Phone)
VALUES
(7923,'Blow','Joe','Chicago','IL','12345-9876','555-555-5555')Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it. (Replace the connection manager 'XW4100-9.TestDB' with your connection manager.)
'Imports System.Data.SqlClient (Uncomment and add to the Imports section)
'Replace the connection manager 'XW4100-9.TestDB' with your connection manager.
Dim mySqlStatement As String = "INSERT INTO TestDB.dbo.Customer (CustomerId,LastName,FirstName,City,State,ZipCode,Phone) VALUES (7923,'Blow','Joe','Chicago','IL','12345-9876','555-555-5555'),(7924,'Antoinette','Marie','Chicago','IL','84356-8456','777-777-7777'),(7925,'Doe','Janet','Houston','TX','99354-9445','333-444-555'),(7926,'Alverez','Desmond','Des Moines','IA','79684-8473','222-222-2222'),(7927,'Contrary','Mary','Boston','MA','17545-4564','111-111-1111')"
Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("XW4100-9.TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
myCommand.ExecuteNonQuery()
myADONETConnection.Close()
Run a SELECT query on the Customer table to confirm that the five records were inserted.
Lesson 14: Execute a SELECT query
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it. (Replace the connection manager 'XW4100-9.TestDB' with your connection manager.)
'Replace the connection manager 'XW4100-9.TestDB' with your connection manager.
Dim mySqlStatement As String = "SELECT CustomerId,LastName,FirstName,City,State,ZipCode,Phone FROM TestDB.dbo.Customer"
Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("XW4100-9.TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Dim CustId As String
Dim LName As String
Dim FName As String
Dim Municipality As String
Dim Region As String
Dim Zip As String
Dim PhoneNumber As String
Dim message As String = ""
Do While reader.Read()
CustId = CInt(reader("CustomerId")).ToString
LName = reader("LastName").ToString
FName = reader("FirstName").ToString
Municipality = reader("City").ToString
Region = reader("State").ToString
Zip = reader("ZipCode").ToString
PhoneNumber = reader("Phone").ToString
message += "Customer ID: " & CustId & Chr(13) & Chr(10)
message += "Last Name: " & LName & Chr(13) & Chr(10)
message += "First Name: " & FName & Chr(13) & Chr(10)
message += "City: " & Municipality & Chr(13) & Chr(10)
message += "State: " & Region & Chr(13) & Chr(10)
message += "Zip Code: " & Zip & Chr(13) & Chr(10)
message += "Phone: " & PhoneNumber & Chr(13) & Chr(10) & Chr(13) & Chr(10)
Loop
reader.Close()
myADONETConnection.Close()
Dim button As DialogResult = MessageBox.Show(Message, "Record Detail", MessageBoxButtons.OK)
The output dialog box confirms that the data in the Customer table has been read.
Lesson 15: Execute an UPDATE query
The following code contains the SQL statement
UPDATE TestDB.dbo.Customer SET City = 'Seattle' WHERE City = 'Chicago'which will change the City value from Chicago to Seattle.
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it. (Replace the connection manager 'XW4100-9.TestDB' with your connection manager.)
'Replace the connection manager 'XW4100-9.TestDB' with your connection manager.
Dim mySqlStatement As String = "UPDATE TestDB.dbo.Customer SET City = 'Seattle' WHERE City = 'Chicago'"
Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("XW4100-9.TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
myCommand.ExecuteNonQuery()
myADONETConnection.Close()
Execute a query on the Customer table to confirm that Chicago was changed to Seattle.
Lesson 16: Execute a query as a stored procedure
Placing a VB.NET SQL query in a long string is OK for learning purposes, but not in production environments.
The solution is to place the query in a stored procedure and execute the stored procedure.
To create the previous select query as a stored procedure, copy this code to a SQL Server Mangament Study query window and execute it.
CREATE PROC SelectCustomer AS SELECT CustomerId, LastName, FirstName, City, State, ZipCode, Phone FROM TestDB.dbo.Customer
Confirm that the stored procedure was created in SQL Server Management Studio.
Following is the same code as Lesson 14, except that the SELECT query string has been replaced by "EXEC SelectCustomer."
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code, then save and execute it. (Replace the connection manager 'XW4100-9.TestDB' with your connection manager.)
'Replace the connection manager 'XW4100-9.TestDB' with your connection manager.
Dim mySqlStatement As String = "EXEC SelectCustomer"
Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("XW4100-9.TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Dim CustId As String
Dim LName As String
Dim FName As String
Dim Municipality As String
Dim Region As String
Dim Zip As String
Dim PhoneNumber As String
Dim message As String = ""
Do While reader.Read()
CustId = CInt(reader("CustomerId")).ToString
LName = reader("LastName").ToString
FName = reader("FirstName").ToString
Municipality = reader("City").ToString
Region = reader("State").ToString
Zip = reader("ZipCode").ToString
PhoneNumber = reader("Phone").ToString
message += "Customer ID: " & CustId & Chr(13) & Chr(10)
message += "Last Name: " & LName & Chr(13) & Chr(10)
message += "First Name: " & FName & Chr(13) & Chr(10)
message += "City: " & Municipality & Chr(13) & Chr(10)
message += "State: " & Region & Chr(13) & Chr(10)
message += "Zip Code: " & Zip & Chr(13) & Chr(10)
message += "Phone: " & PhoneNumber & Chr(13) & Chr(10) & Chr(13) & Chr(10)
Loop
reader.Close()
myADONETConnection.Close()
Dim button As DialogResult = MessageBox.Show(Message, "Record Detail", MessageBoxButtons.OK)
The result with the stored procedure is identical to the result with the query in a string.
Lesson 17: Putting it all together
Execute this T-SQL statement in Microsoft SQL Server Management Studio to create the data for the following demo, which uses most of the tools presented in the previous lessons.
CREATE TABLE [TestDB].[dbo].[SalesReps]( [RepId] [int] NULL, [LastName] [varchar](20) NULL, [FirstName] [varchar](20) NULL, [Email] [varchar](30) NULL ) ON [PRIMARY] GO INSERT INTO [TestDB].[dbo].[SalesReps] (RepId,LastName,FirstName,Email) VALUES (1234,'Smith','Will','smithw@domain.com'), (1235,'Jones','Indiana','jonesi@domain.com'), (1236,'Lange','Jessica','langj@domain.com'), (1237,'Flintstone','Fred','flintstonef@domain.com'), (1238,'Cowell','Simon','cowells@domain.com') CREATE TABLE [TestDB].[dbo].[Sales]( [RepId] [int] NULL, [ItemId] [int] NULL, [Amount] [numeric](10, 2) NULL ) ON [PRIMARY] GO INSERT INTO [TestDB].[dbo].[Sales] (RepId,ItemId,Amount) VALUES (1234,98765,1829.37), (1234,74829,7345.87), (1235,85893,9475.72), (1235,12938,2436.34), (1236,56465,9456.12), (1236,74865,3746.99), (1237,43526,6453.54), (1237,38464,4985.84), (1238,64345,8439.66), (1238,78534,7329.52)
Replace the VB.NET script task code in the Main procedure from the previous lesson with the following code. (Replace the connection manager 'XW4100-9.TestDB' with your connection manager.)
Dim SalesRepList As New ArrayList
Dim mySqlStatement As String = "SELECT RepId FROM TestDB.dbo.SalesReps"
Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("XW4100-9.TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Do While reader.Read()
SalesRepList.Add(reader("RepId").ToString)
Loop
reader.Close()
myADONETConnection.Close()
Dim Rep As String
Dim RepId As String = ""
For Each Rep In SalesRepList
RepId = Rep
Dim lines As New ArrayList
lines.Add("RepId Name ItemId Amount" & Chr(13) & Chr(10))
mySqlStatement = "SELECT a.RepId,a.FirstName + ' ' + a.LastName AS Name,a.Email,b.ItemId,b.Amount FROM TestDB.dbo.SalesReps a INNER JOIN TestDB.dbo.Sales b ON a.RepId = b.RepId WHERE a.RepId = " & Rep.Trim
myADONETConnection = DirectCast(Dts.Connections("XW4100-9.TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
myCommand = New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Dim email As String
Do While reader.Read()
lines.Add(reader("RepId").ToString & Chr(9) & reader("Name").ToString & Chr(9) & reader("ItemId").ToString & Chr(9) & reader("Amount").ToString)
email = reader("Email").ToString
Loop
Dim file_content As String = ""
Dim line As String
For Each line In lines
file_content += line & Chr(13) & Chr(10)
Next
Dim Writer As System.IO.StreamWriter
Writer = IO.File.CreateText("C:\SalesReport" & RepId & ".txt")
Writer.WriteLine(file_content)
Writer.Close()
Dim SenderEmail As String = "sender@email.com"
Dim RecipientEmail As String = Email
Dim Subject As String = "Sales Report"
Dim Message As String = "Attached is your sales report."
Dim AttachmentPath As String = "C:\SalesReport" & RepId & ".txt"
Dim SmtpAddress As String = "123.123.123.123"
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
myHtmlMessage = New MailMessage(SenderEmail, RecipientEmail, Subject, Message)
myHtmlMessage.Attachments.Add(New Attachment(AttachmentPath))
mySmtpClient = New SmtpClient(SmtpAddress)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
myHtmlMessage.Dispose()
Next
reader.Close()
myADONETConnection.Close()
For Each Rep In SalesRepList
Dim file As New System.IO.FileInfo("C:\SalesReport" & Rep & ".txt")
file.Delete()
Next
The demo code reads all the RepIds in the SalesReps table into an ArrayList. It then loops through the array list and joins the SalesReps table with the Sales table for each RepId. It creates an ASCII file report for each representative, then emails that report as an attachement to the representative. After the RepId ArrayList finishes, it is looped through again to delete the ASCII files created for each representative.
If you want to make the demo actually work, you will have to replace the sender email address and the email addresses in the SalesReps table with real email addresses that you can acces, and enter a valid SMTP IP address.
Conclusion
The basic programming tools presented in this article can enable SSIS package developers familiar with one or more programming languages but not with VB.NET to write useful SSIS script tasks almost immediately.
For another example of what can be done with these tools, see my previous SQL Server Central article Copy and Paste SSIS Execution Monitoring with Failure-Alert Email.