Execute SQL statements in Integration Services

Takeaway: 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

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

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

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

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

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

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

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

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

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 Jdepicts a successful package execution.

Figure J