Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A VB.NET toolkit for writing SSIS Script Tasks

By Stan Kulp,

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)

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.

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)

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.

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

    Dim button As DialogResult = MessageBox.Show(value.ToString(), "ArrayList Value " + i.ToString, MessageBoxButtons.OK)
    i += 1

Next

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)
     Next

The 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.

Dim Writer As System.IO.StreamWriter
Writer = IO.File.CreateText("C:\MyFile.txt")

Writer.WriteLine("Line 1")
Writer.WriteLine("Line 2")
Writer.WriteLine("Line 3")
Writer.WriteLine("Line 4")
Writer.WriteLine("Line 5")

Writer.Close()

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.

Dim Reader As New System.IO.StreamReader("C:\MyFile.txt")
Dim line As String = ""

Do
    line = Reader.ReadLine
    If Not line Is Nothing Then
        Dim button As DialogResult = MessageBox.Show(line, "This is Line # " + Right(line, 1), MessageBoxButtons.OK)
    End If

Loop Until line Is Nothing

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.

Dim filename As String = "MyFile.txt"
Dim file As New System.IO.FileInfo("C:\" & filename)

Try
    file.MoveTo("C:\Temp\" & filename)
Catch ex As Exception
    Dim button As DialogResult = MessageBox.Show("The file does not exist.", "ERROR MESSAGE", MessageBoxButtons.OK)
End Try

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)
     Next

loops 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.Net

Replace 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.SqlClient

This 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.

Total article views: 9029 | Views in the last 30 days: 42
 
Related Articles
FORUM

How to receive a Message from a MSMQ using Message Queue task with out message label as "String Message"

Receive message from a MSMQ using message queue task with lable other than "String Message".

FORUM

can you please write some messages with this string "Timeout Expired" to sqlagent log

can you please write some messages with this string "Timeout Expired" to sqlagent log

BLOG

SQL University Lesson 3 Page Compression

Lesson 2: Internal Structures, Vardecimal, & Row Compression Welcome to Lesson 3 on Compress...

FORUM

Create Login Script

Error with Create Login Script

BLOG

MDX #23 – “Hello World!” Lesson in MDX

Almost every tool we learned has some sort of “Hello World!” tutorial lesson. So here comes the “He...

Tags
integration services (ssis)    
smtp    
t-sql    
vb.net    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones