Prompting for parameter in SSIS

A question in MSDN forum drew my attention towards the missing functionality of

Prompting for parameters in SSIS

A question might struck in your mind that if we have it in SSRS than why not in SSIS. Well I am a great fan of Microsoft as they provide alternative for any thing under the sun (Sometimes SUN itself). In this post I would like to demonstrate how we can implement prompt for variable values in SSIS..

For the implemention, Let me consider a simple table having Name and Email Id.
Through SSIS package we will prompt for Name and accept Name from User.

CREATE TABLE [tblSSISPrompt]
(
[Name] varchar(15),
[Email] varchar(50)
)
GO
--Fill table with some data

INSERT INTO [tblSSISPrompt] VALUES ('Rahul', 'Rahul.Kumar@sqlsvrsol.com')
INSERT INTO [tblSSISPrompt] VALUES ('Prashant','Prashant.M@sqlsvrsol.com')
INSERT INTO [tblSSISPrompt] VALUES ('Mark','Mark.W@sqlsvrsol.com')
GO

SELECT * FROM [tblSSISPrompt]

Now we start with SSIS
A) Declare two variable
a) SqlStmt to store SQL query
b) Email to get Email from the resultset from Sql query


B) Use Script Task to prompt and prepare SQL query and store in SqlStmt variable
a) Specify SqlStmt as ReadWriteVariables


b) Code for prompt and prepare SQL query

Public Sub Main()
Dim Name As String

'Prompt
Name = InputBox("Enter Name", "Name Dialog")

'Prepare SQL query
Dts.Variables("SqlStmt").Value = _
"SELECT Email FROM tblSSISPrompt WHERE Name = '" + Name + "'"

'msgbox
MsgBox(Dts.Variables("SqlStmt").Value, , "Query")

End Sub

C) Use Execute SQL Task to execute the query



D) Execute the Package
a) As you execute the package, it will prompt for Name


b) Script will display query in Message box


c) Finally Execute SQL Task will execute the query

Congrats!!! thats Prompting for parameters in SSIS

If you want to do the same thing in C#, read first comment.