This article is intended to help the people who are beginners to SSIS. In this article we will see what is SSIS? Why we use SSIS ? and how to create a package using BIDS (Business Intelligence Development Studio). The article describes this through an example which will show us how a SQL Server query or Store Procedure can be executed SSIS.

What is SSIS

SSIS is an ETL (Extract, Transform and Load) process and a component of Microsoft which can be used to perform a broad range of data migration tasks. SSIS lets the user to move data from one data source to another data source. We can also perform numerous other tasks with the help of SSIS.

How To Create Your First SSIS Package?

We will see How to Create Your First SSIS Package by looking into an example which will walk through us from the opening BIDS (Business Intelligence Development Studio) to Execute SQL from SSIS.

Creating a SSIS Project

For creating a SSIS package you must have SSIS (SQL Server Integration Services) and BIDS (Business Intelligence Development Studio) installed on your machine. After installing SSIS and BIDS you can open BIDS. To open BIDS go to the option SQL Server Business Intelligence Development Studio under Start>>>All Programs>>>Microsoft SQL Server (Edition).

Go to File>>>New>>>Project from SQL Server Business Intelligence Development Studio (as shown in the below image) to open a new project.

Once you see the New Project window, select the project type name as Integration Services Project  and name the project as MyFirstSSIS. Select the location where you want to save the project by clicking on browse button or just type in the path.Once all information is filled up click on the OK button to save the project.

Adding a Package to your Project

When the project is saved, the project file will be saved under the specified path. The project will be saved with a default package which will be blank.

To add a package in your project right click on the SSISPackages node in the solution explorer at the left hand side of window or go to project option from the menu and select Add SSIS Package. Name the package you add as MyFirstPackage.

Adding Execute SQL Task in Your Package

Now you have a package added to your project and it is time to add the control flow item Execute SQL Task. Go to view menu and select Tool Box option to open the Tool Box (See the image below).

To add the control flow item Execute SQL Task to the package, double click on the item or simply drag and drop the item to your package window. Once the item is added the item will show up on the package (see the below image).

Now your package with raw Execute SQL Task will look like this now next step is to configure this Execute SQL Task.

Configuring Execute SQL Task

Now we have to configure the Execute SQL Task control to execute a SQL Query or SQL Store Procedure from SSIS. To configure Execute SQL Task right click on the control and choose Edit option to configure the control.

First of all we have to set up the database connection which will be used by this control, to set up the DB connection go to general tab of the properties window and set the connection type as OLE DB. Click on Connection and select New Connection  to set up a new connection ( see the below image).

Setting up a Connection

When you select  the New Connection the Connection Manager window will open up, where you have to set up the connection information.  The following are the information need to be set up.

Provider : Select the one which is default.
Server Name : The SQL server name on which the query should be executed.
Authentication Type : The type authentication through which the SQL server needs to be connected, which can be  windows or SQL Server.
Database name :  Select the database  to which we will query.  If you do not find the database to which you were supposed to make the query on clicking the drop down, check your connection information.

To insure that the connection information you entered are correct , you can click on Test Connection button .

After clicking ok you are done with creating a connection and your New Connection is ready to use you can see your connection at the bottom of the window in the Connection Manager (See below image). To edit the connection, right click on the connection choose edit or just double click on the connection.

Now its time to write the SQL statement to be executed, to write the statement to be executed choose the option SQL Source Type as Direct Input and write the SQL statement in the column against the SQL Statement option on the  Execute SQL Task Editor window. For an example we are taking an insert script which will insert a row when we execute the statement. Run the script provided below in your test Database to create a new table. Enter the insert script below against the SQL Statement in the Execute SQL Task Editor.

-- Create Table Run In your Database
CREATE TABLE [dbo].[DateInfo]( 
    [Date] [datetime] NULL, 
    [Year] [int] NULL, 
    [Month] [smallint] NULL, 
    [DayOfYear] [smallint] NULL, 
    [DayOfMonth] [smallint] NULL, 
    [DayOfWeek] [smallint] NULL, 
    [MonthName] [varchar](10) NULL, 
    [DayName] [varchar](10) NULL 
) ON [PRIMARY]

--Script to Insert Into table put it at the place of SQLStatement
INSERT INTO DateInfo 
SELECT 
    GETDATE(), 
    DATEPART(YEAR,GETDATE()), 
    DATEPART(MONTH,GETDATE()), 
    DATEPART(DAYOFYEAR,GETDATE()), 
    DATEPART(DAY,GETDATE()), 
    DATEPART(WEEKDAY,GETDATE()), 
    DATENAME(MONTH,GETDATE()), 
    DATENAME(DW,GETDATE())

Once you enter the statement, click the OK button on the bottom of Execute SQL Task Editor window and you are done with Configuring Execute SQL Task. Now Save the package and the package is ready to execute. To execute the package go to Solution Explorer on the right hand side of window Right Click on the package you created in the example it is MyFirstPackage and click Execute Package as shown in the below image.

If the package successfully runs, you can see that the control turns into green color ( as shown in the below image).

To insure that the package executed successfully you can query the table in your database by executing the query below.

SELECT * FROM DateInfo

And here is the result set for the query. You will find a record in your table after the successful execution of package.

You can also create a procedure of this insert query as stated below

CREATE PROC Sp_InsertInfo
AS
	INSERT INTO DateInfo 
	SELECT 
		GETDATE(), 
		DATEPART(YEAR,GETDATE()), 
		DATEPART(MONTH,GETDATE()), 
		DATEPART(DAYOFYEAR,GETDATE()), 
		DATEPART(DAY,GETDATE()), 
		DATEPART(WEEKDAY,GETDATE()), 
		DATENAME(MONTH,GETDATE()), 
		DATENAME(DW,GETDATE())

After creating the package you can write Exec Sp_InsertInfo instead of Insert Statement in the Execute SQL Task Editor and click OK to save the editor. Now you are done with your Execute SQL Task with the help of Store Procedure now again repeat the package and check data in the table you will get another row in your table.

So in this way you can create a simple package using Execute SQL Task. You can execute any other SQL Query or Procedure with the help of Execute SQL Task. We will see many other examples and controls of SSIS in future posts.