Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Exploring SSIS - Execute SQL task with simple parameter

Dec 18 2012 10:06AM by Madhivanan   

I have started a series on Exploring SSIS where I will explain various features available. In this post, we will see the usage of Execute SQL task in Control flow

Execute SQL task is primarily used to execute SQL statements and stored procedures with or without parameters. Let us see how we can execute simple select statement by passing a parameter

Open Business Intelligence Development Studio (BIDS). Navigate to File-->New-->Project. Under templates choose Integration Services Project. Give project and choose the location to save the project

In the solution explorer click on Pacakge.dtsx and you will see Tabs for Control Flow, data flow etc. Drag Execute SQL task item into the Control flow area. Name the task as Simple select

Double click on this task, you will see Execute SQL Task editor, under general section, choose connection type as OLE DB, choose the datasource for connection, choose the result set as Full Result set; choose SQLSourceType as Direct Input and write the following query in SQLStatement

select * from INFORMATION_SCHEMA.COLUMNS
where table_name=?

Now we need to create the parameter to supply the value for column table_name. Now right click on Control Flow area and choose variables. Create a new variable named table_name, choose datatype as string and type test as value. The purpose is to return all rows from information_schema.columns for the table named test. We need to assign the result to another variable. Create another variable named result_set, choose datatype as object and type 0 as value

We need to map these variables in the Execute SQL task. Double click on it and choose parameter mapping. Click the button Add and choose the variable named User::table_name, Direction -->Input, Datatype-->varchar; parameter_name-->0; parameter_size to -1. Goto resultset and choose the user::result_set for variable name and type 0 as result name

Now right click on Execute SQL task and choose Execute task. You see that everything gets executed well and you can see green color around the Execute SQL task.

Please refer these screenshots for better understanding

ssis1

ssis2

 

ssis3

ssis4

 

ssis6

ssis7

ssis8

You may be also interested to read this

Exploring SSIS - Understanding the basics

Tags: 


Madhivanan
3 · 40% · 12912
3
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

5  Comments  

  • Hi Madhivan,

    Nice post, just a minor correction, when we create object variable we donot set its value in the variable window. It is set by default and we cannot edit that.

    It would be nice if people referring to the post can actually see what's going into the object variable. Why not use the object variable in foreach loop with a script task showing messagebox with values, or use the watch to check the values. I know this would mean a jump for the beginners, but you could take it up as the next topic. :)

    commented on Dec 18 2012 8:22AM
    Sudeep Raj
    12 · 13% · 4303
  • Sudeep Raj, thats good point. I will make seperate post for for each loop

    commented on Dec 18 2012 11:23PM
    Madhivanan
    3 · 40% · 12912
  • Hi Sudeep/Madhivanan,

    There is one quick question regarding this post. I have a Query: Select count(*) from dimcustomer

    and Result:200 Records(just assume)

    i have used one ExecuteSQL Task and set ResultSet as Single Row.(Also i have created one variable)

    How to see that record count by setting WATCH WINDOW with out using Script Task.

    commented on Feb 4 2013 8:04AM
    Bala Krishna
    83 · 2% · 676
  • Hi Bala,

    You need to set up a break point at the task and then look at the watch window. Here Jamie explains it in details.

    http://consultingblogs.emc.com/jamiethomson/archive/2005/12/05/2462.aspx

    commented on Feb 5 2013 2:20AM
    Sudeep Raj
    12 · 13% · 4303
  • HI Sudeep,

    Thank you soo much for sharing such valuable Link.

    Thanks again.

    commented on Feb 5 2013 3:26AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Exploring SSIS - Execute SQL task with simple parameter" rated 5 out of 5 by 3 readers
Exploring SSIS - Execute SQL task with simple parameter , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]