Monday, November 15, 2010

Extracting data from Sybase SQL Anywhere using SSIS through ODBC

Recently I had to evaluate a few products for a data migration from Sybase SQL Anywhere 10 database to PostgreSQL database. SSIS was one of the tools. This post explains what I did to extract data from the Sybase database.

Step1: Create DSN

The first thing I did was to install SQL Anywhere on my computer so that it installs all the ODBC and OLE DB drivers. In my case I used the ODBC driver for SQL Anywhere 10. I created a system DSN using the odbc driver. I used the following values for the connection properties,

In ODBC Configuration for SQL Anywhere 10,
Under ODBC Tab,
I have given a name for my Connection in "Data Source Name" field.

Under Login Tab,
I have selected the "Supply User ID and Password" option and entered the credentials there.

Under the Database Tab,
Server name: The Name of the Sybase Server you are connecting to
Database name: The Name of the database.

And finally under Network Tab,
I have selected TCP/IP checkbox and entered the following details in the textbox next to it,
HOST=IP Address of the server;PORT=port number sybase server listens to(default 2638)

Go back to the ODBC tab and click on the "Test Connection" button which should give a test successful message box if everything went well.

Step2: Create Connection in SSIS

In SSIS, right click on the connection managers secsion and select ADO.NET connection. On the Connection Manager window, you need to select "odbc data provider" for the Provider. Then select the System DSN you created in the Use User or System data source name combo box. Test the connection to ensure that the connection is working.

Step3: Configure Data Reader Source

Inside a Data Flow Component, drag and drop a Data Reader Source and open its properties window.

Under the Connection Managers Tab,
Select the ADO.NET connection manager we just created from the Connection Manager combo box.

Under the Component Properties Tab,
Under Custom Properties, type in the Select Query in the text box next to "SQL Command" field.

In the Columns Mapping Tab,
Check whether the columns selected in the Select Query are correctly mapped with the out put columns. Generally these two should be identical. Press ok to close the window.

Ok now you are ready to extract data from a SQL Anywhere table. You can connect the dataflow with other transformations/Destinations.



Extracting Data From Web said...

Hi Friends,

Data extraction is the act or process of retrieving data out of data sources for further data processing or data storage. It is applied when data is first imported into a computer from primary sources like measuring or recording devices. Thanks a lot....

Web Ripper said...

Data Extraction software are ready-made packages for data extraction purposes which can be permanently installed in your system for long term use. It gives you fast relief from database extraction & warehousing problems. It takes your business one step ahead in maintaining updated databases. Thanks a lot.

Carly Fiorina said...

Hello Friends,

Data Extraction software will crawl the data from your targeted sites , extract the data and will store the extracted information in the customized format. Web Data Extraction is a type of information retrieval that can automatically extract structured information from unstructured or semi-structured web data sources. Extract data with web data extractor to get great speed, accuracy and exactness. This is 24*7 non stop data extraction facility which will enable you to grab and store information from various different targeted web sites.With web data extractor you can automatically get lists of meta-tags, e-mails, phone and fax numbers, etc and store them in different formats for future use. Thanks.....
Data Extraction

Prologic Corporation said...

This is a good article & good site.Thank you for sharing this article. It is help us following categorize:
healthcare, e commerce, programming, multi platform,inventory management, cloud-based solutions, it consulting, retail, manufacturing, CRM, technology means, digital supply chain management, Delivering high-quality service for your business applications,
Solutions for all Industries,
Getting your applications talking is the key to better business processes,
Rapid web services solutions for real business problems,
Web-based Corporate Document Management System,
Outsourcing Solution,
Financial and Operations Business Intelligence Solution,

Our address:
2002 Timberloch Place, Suite 200
The Woodlands, TX 77380