ODBC Source in ssis

-

ODBC Source in ssis 

have just installed the new ODBC driver 11 for SQL Server in my machine.
odbcIn 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.

Comments

Post a Comment

Popular posts from this blog

SharePoint 2007 - Simple Task Dashboard

-
Image
example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
Read more

MERGE transformation in SSIS

-
Image
In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
Read more