SSIS Package-Using Execute SQL Task,Data Flow Task and Flat file destination

In previous post we created a simple SSIS package using Data flow Task that pull data from a flat file source and load in to a flat file source. Today we create a package that pull data from a SQL Database and loading to other SQL server data base using Execute SQL Task. we also use Data flow task which pull data from SQL Database to a Flat file within same package

As we did before we have to create connection manager for source and destination.

Here we need two create three connections

1.Source connection for Source SQL database (Connection name –“SQLSource”)-From where we pull data

2.Destination connection for destination SQL Database(Connection Name “SQLDesti”- WhereIwe load data

3.Destination Flat file connection (Connection Name “FlatDesti”) -where we load data from destination SQL Data base

Data flow -Source SQL Database ->Destination SQL database ->Flat file

At first, we pull data from SQL Database then load into another SQL Database

After loading data into the SQL destination, we use this destination as a source and pull data from this to load into a flat file

We are going to use SQL query in the Execute SQL Task .

This query at first create a temporary table then insert data into this table by pulling data from source then a Data flow Task pull data from this table and load into destination. Later another Data Flow task pull data from this destination table to a flat file

SQL Query :

CREATE TABLE ProductsTmp(

[ProductID] [int] NULL,

[Name] [nvarchar](50) NULL,

[ProductNumber] [nvarchar](25) NULL,

[Color] [nvarchar](15) NULL,

[StandardCost] [money] NULL,

[ListPrice] [money] NULL,

[Size] [nvarchar](5) NULL,

[Weight] [numeric](8, 2) NULL,

[ProductCategoryID] [int] NULL,

[ProductModelID] [int] NULL,

[SellStartDate] [datetime] NULL,

[SellEndDate] [datetime] NULL,

[DiscontinuedDate] [datetime] NULL,

[ThumbNailPhoto] [varbinary](max) NULL,

[ThumbnailPhotoFileName] [nvarchar](50) NULL,

[rowguid] [uniqueidentifier] NULL,

[ModifiedDate] [datetime] NULL

)

Insert into ProductsTmp

SELECT [ProductID]

,[Name]

,[ProductNumber]

,[Color]

,[StandardCost]

,[ListPrice]

,[Size]

,[Weight]

,[ProductCategoryID]

,[ProductModelID]

,[SellStartDate]

,[SellEndDate]

,[DiscontinuedDate]

,[ThumbNailPhoto]

,[ThumbnailPhotoFileName]

,[rowguid]

,[ModifiedDate]

FROM [ExampleDB].[dbo].[Products]

1.Source Name - SQLSource

Server Name -KHAWJAPRO4

Database Name -ExampleDB

Table Name -Products

2. Destination Name - SQLDesti

Server Name -KHAWJAPRO4\MSSQLSERVERBK

Database Name -BlogDB

Table Name -Products

3. Flat file destination

Location: C:\Blog\Destination\FlatDestination

Let’s start works

Create a project as we did in previous post (Link). Name it “UsingExecuteSQLTask”

We are going to pull data from “product” table in “ExampleDB” database from “KHAWJAPRO4” server

Loading data into “BlogDB” Database to KHAWJAPRO4\MSSQLSERVERBK Server also load data into the folder as a flat file. Flat destination Path- C:\Blog\Destination\FlatDestination

Create SQLSource Connection

Fig-P2-1

Right click on connection manager pane(Bottom)
Select “New OLEDB Connection”

Configure OLEDB Connection window open

Fig-P2-2

Click New-Connection Manager editor open

Fig-P 2-3

Select source server from drop down or write the server name –“KHAWJAPRO4”
Select database name from drop down-“ExampleDB”
Test connection – see “Test connection successfully”
Click OK

Fig-P2-4

We can see the connection name “KHAWJAPRO4.ExamplDB”. Rename it by right click on the connection manager pane with “SQLSource”

Create SQL Destination connection (Following same step (1-4)

Fig-P2-5

Select source server from drop down or write the server name “ KHAWJAPRO4\MSSQLSERVERBK”
Select database name from drop down-“BlogDB”
Test connection – see “Test connection successfully”
Click OK

The connection name “KHAWJAPRO4.BlogDB”. Rename it by right click on the connection with “SQLDesti”

1. Drag and drop Execute SQL Task from SSIS Tool Box(left pane) to Control flow design page.

2. Double click on the task – Execute SQL task editor window will open

Fig-P 2-6

1. Give a name like “SQLSourceData”

2. Connection type –OLEDB(By default)

3. Select connection from drop down and select “SQLSource” ,we just created

4. SQL Source Type –“Direct Input” available source type is File connection and variable we will discuss about variable later post

5. SQL statement-click on the ellipse (….) – a blank query window will be open copy paste the select statement which we saw in SSMS

Click Ok then again OK

Execute SQL task configured.

Execute the package -Right click on the task then click on Execute Task. Task ran Successfully (See Task with green tick mark

Let’s get back to SSMS and see Data inserted into Temporary table.

Now we load data from temporary table to destination table. using Data Flow Task

Destination Location: -

Server: KHAWJAPRO4\MSSQLSERVERBK

Database: BlogDB

Table : Products

Drag and drop a Data Flow Task. Connect to Data Flow Task from Execute SQL task by Green connector (Precedence constraint)

Click On Data flow Task .

Data Flow Task design window open.

Drag a OLEDB source from SSIS Tool Box
Click on the source task to edit connection

Select connection manager from drop down (“SQL source”) we create temporary table in the same database(“ExampleDB”) so we can use the same source connection

Select the productTem table from drop down

Click on Destination Task. Destination Editor will open

Select SQLDesti connection and products table from drop down

Click mapping from left pane and mapped input and output column. Click OK

Data flow Task configured

Now we drag one more Data Flow Task to pull data from that table which we just loaded (“BlogDB” Database) and create a flat file with this product data. Click on the Data Flow Task .

Data Flow Task Editor open

Drag a source control and configure by using desti connection manager where we load product data .select product table from drop down.

Execute Dataflow Task

Ran Successfully

We can see in the destination server products table with loaded data

Drag one more data flow task then open design window as we did before. On design window drag aOLEDB source. Connect with destination connection as we said here destination connection used as source.

Drag a flat file destination. Click on the destination flat file connection and create destination flat file connection (where the flat file will be load (C:\Blog\Destination\FlatDestination)