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.
Cheers!
5 comments:
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....
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.
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
I installed the 64 bit Sql anywhere 12 to connect to sysbase and created System DSN , but in SSIS package i cannot see the DSN , should i install 32 bit ?/
I think there is a dire need to learn and gather more information about Postgresql.
SSIS Postgresql Read
Post a Comment