Home > SQL Server > ODBC Source – SSIS

ODBC Source – SSIS


I have just installed the new ODBC driver 11 for SQL Server in my machine.

odbc In this blog Post, I am going to implement a simple data transfer SSIS package using ODBC Source task. This task is available in SQL Server 2012. It helps us to integrate data from different platform and make simplified connectivity to SQL Azure.

Implementation

Step 1: Create a package and Add a data flow task

pic1Step 2: Go to data flow task designer and add ODBC Source connection

pic1ODBC Configuration

Edit this task and click new button on the dialog box

pic1pic2Build the connection string in the connection manager

pic3Create new ODBC data source

pic4Select user data source and click next button

pic5Select ODBC driver 11 for SQL Server from the drivers list and click next

pic6Click finish now

pic7We have created the Data source configuration with ODBC 11 driver. Now, we need to connect it with SQL Server. So, next steps are for creating data source connection to SQL Server.

Data source connection to SQL Server

Give Data source name and SQL Server name.

pic8Click finish now or you can move to next steps in the wizard to set up connection string properties. But, I am using the default settings for this connection.

pic9Test the data source and make sure you are able to connect to SQL Server and Click OK

pic10We have created a new ODBC data source name. We should use this name to connect to source or destination connection in the package.

pic11We have got two options now. We can use connection string or select the newly created data source name “AdventureWorks” in the data source selection option.

Click OK. OK.

Select a table for the data source

pic12Click OK to complete the data source configuration.

Step 3: Add a Data Reader Destination task and connect ODBC source task.

datadestStep 4: Execute package now.

datadestPackage executed successfully.

We have learned to configure ODBC Source task and connect with SQL Server in this blog post. I will explain how to use ODBC Destination task in my next blog post.

Advertisements
  1. No comments yet.
  1. January 26, 2013 at 11:45 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: