Data Management

Execute SQL statements in Integration Services

Executing SQL statements are one of the easiest and most common tasks that occur in SQL Server 2005 Integration Services (SSIS). This tutorial walks you through the process of adding SQL statements to your SSIS packages and bundling them into logical units of work if necessary.

Executing SQL statements is one of the easiest and most common tasks that occur in SQL Server 2005 Integration Services (SSIS). This tutorial walks you through the process of adding SQL statements to your SSIS packages and bundling them into logical units of work if necessary.

Add and bundle SQL statements to SISS packages

Before I can execute a SQL statement inside of an SSIS package, I need to create a Data Source to specify the SQL Server and database to which I want to connect. To do this, I right-click the Data Sources folder in my Solution Explorer window and select New Data Source (Figure A). If you cannot find your Solution Explorer window, go to the View menu and select Solution Explorer. Figure A

Figure A

Selecting the New Data Source option brings up the Data Source Wizard. Since I haven't defined a Data Source, I click the New button (Figure B). Figure B

Figure B

I am working on the machine that hosts the SQL Server instance to which I will connect, so I type (local) in the Server Name drop-down list. From there, I can select the database to which I want to connect (Figure C). Figure C

Figure C

I name my new Data Source (Figure D). I can add additional data sources if my project will need it. An SSIS package can connect to several different data sources -- even data sources of different types -- within the same package. For the purposes of this example, I will only need the connection I just created. Figure D

Figure D

Now that I have defined a connection, it is time for me to move on to the objects that I will use to execute my SQL statements. From the Control Flow menu, I want to drag three Execute SQL Task task options onto my Control Flow screen (Figure E). I am dragging three of these task items because I plan to create three tables that I will import data into in next week's article. I am adding three of these task items because I plan to create three tables that I will import data into in a future article. These tasks will be used to create the BigScreenProducts table, the ComputerProducts table, and the PoolTableProducts table. To rename these individual task items, right-click the item and select Rename. Figure E

Figure E

To specify the details of the task items, I double-click the first item, which brings up the Execute SQL Task Editor window (Figure F). Figure F

Figure F

In this window, I specify the data server to which I want to connect. I select the Data Source that I previously created and click OK (Figure G). Figure G

Figure G

Once I establish my Data Source, it is time to define the SQL statement that I want to execute. Here are my three options for executing the statement:

  • Direct Input, which means I will be tying in the SQL statement into a window.
  • Specify a file that contains the SQL statement.
  • Specify a variable that contains the SQL statement.
I will explore the last option more in future articles, but for now, I'll concentrate on the Direct Input option. Click the ellipsis symbol (...) in the Execute SQL Task Editor window and enter the SQL Script in Figure H. Figure H

Figure H

The code for the statement is below:

IF OBJECT_ID('BigScreenProducts') IS NOT NULL
 DROP TABLE BigScreenProducts
GO
CREATE TABLE BigScreenProducts 
(
    [SaleID] INT,
    [Product] VARCHAR(50),
    [SalePrice] MONEY
)

This script checks to see if the BigScreenProducts table exists and drops it if it does. I then create the BigScreenProducts table.

Repeat the following Execute SQL Task steps for the remaining two task items -- simply change the name of the table in the SQL script. You can replace BigScreenProducts with PoolTableProducts in the second task and replace it with ComputerProducts in the third task.

I like to group statements together that I feel execute as one logical unit of work. I feel like creating these tables is one logical unit of work, so I want to include them in a Sequence Container task item. From the Control Flow Items menu, I drag the Sequence Container task onto my Control Flow menu; I then drag my three Execute SQL Tasks into this container object. I like to do this to ensure that these items execute together and to abstract them from other parts of my Control Flow menu if it is going to be complex (Figure I). Figure I

Figure I

All that is left is to execute the package. Because I didn't define any precedence control inside of the package, the creation of the three tables happens at almost exactly the same time. Figure J depicts a successful package execution. Figure J

Figure J

Stay tuned

My next article will expand on the example in this SQL Server article to incorporate conditional handling of imported data.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

1 comments
gibbsbill
gibbsbill

we are transitioning from DTS to SSIS; it is great, but I'm stuck on what should be an obvious point - I simply need to query for a maxID in the target table, and use this value as the WHERE criteria for the MySql import query. Have not arrived at the solution...please advise 8^) I greatly appreciate it

Editor's Picks