Monday, October 20, 2008

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.

6 comments:

Rahul Kumar said...

Additional Note

Well in Script Task Using VB.Net we can directly use InputBox for prompting but if you have to do the same thing using C#, you are out of luck as C# doesnt have anything like input box. But still you can find a way around.

1. Add reference to Miscrosoft.VisualBasic.dll in the project

2. Use the below code in Script task
public void Main()
{

string Name = null;

//Prompt
Name = Microsoft.VisualBasic.Interaction.InputBox("Enter Name", "Name Dialog","Enter a String",50,50);

//Prepare SQL query
Dts.Variables["SqlStmt"].Value = "SELECT Email FROM tblSSISPrompt WHERE Name = '" + Name + "'";

//msgbox
MessageBox.Show(Dts.Variables["SqlStmt"].Value.ToString() );

Dts.TaskResult = (int)ScriptResults.Success;
}

Thanks to David Nahmais for asking the Question!!.

Suneetha Akula said...

where did you use the email variable and how can we pass sqlquery to dataflow "olddbcommand"

Anonymous said...

Hi

I'm following the code.

Whayt is the data connection I need to create and use in c)

Thank you

Rahul Kumar said...

@LastComment: Data Connection in C) step should be the database on which you want to run the query.

Thanks

Albert Andrada said...

Really Nice Information,Thank You Very Much For Sharing.
Wordpress Development Company

lee woo said...

Try not to become a man of success, but rather try to become a man of value. See the link below for more info.


#value
www.ufgop.org

Post a Comment