none
How can I copy a Sybase table into SqlServerr 2005 using SSIS?

    Question

  • I have used SSIS successfully to copy Oracle tables using Microsoft OLE DB provider for Oracle but I do not see any direct driver references to Sybase in the drop-down list.  How do I use the ODBC driver (from Oracle or from Microsoft) I have available on the same computer? Thank you.
    Monday, July 02, 2007 8:00 PM

Answers

  • You will need to install the OLE DB drivers for sybase,  as far as I know it does not get installed by default.

    If you have Sybase open client 12 or higher, you should be able to find the OLE-DB drivers installation in that.

    Please remember, this installation will need to be done in the deployment enviornment also.

    Tuesday, July 03, 2007 3:30 AM

All replies

  • First, Oracle has nothing to do with Sybase.  The fact that you have Oracle means nothing in the Sybase world.  You'll have to search for Sybase drivers on the Internet (if you have a Sybase client installed, you're probably already setup).

    Sybase has an OLE DB driver, I know.  In my quick search, I couldn't find it though.
    Monday, July 02, 2007 8:04 PM
    Moderator
  • Thanks for the quick response. I know Oracle has nothing to do with Sybase. The point I was making is to do with SSIS packages. The db engine and the integration services are running fine and I was able to use them (albiet in the Oracle context) from the SQ Mgmt. Studio. I have Sybase client and ODBC drivers installed on my client workstation but I do not see any references to Sybase on the "Data Source" drop-down list when I use right click on Management ==> Import Data.  Your reference to Sybase OLE DB Driver is interesting but I can't find it either.

     

    Jay

    Monday, July 02, 2007 8:26 PM
  • Setup an ODBC connection on your machine using the Sybase drivers.

    Then inside SSIS, you can use a DataReader Source in a data flow to use that ODBC driver.
    Monday, July 02, 2007 8:34 PM
    Moderator
  • You will need to install the OLE DB drivers for sybase,  as far as I know it does not get installed by default.

    If you have Sybase open client 12 or higher, you should be able to find the OLE-DB drivers installation in that.

    Please remember, this installation will need to be done in the deployment enviornment also.

    Tuesday, July 03, 2007 3:30 AM
  • Thank you for that direction. I have installed Sybase ASE client 12.5.4 and custom installed the Sybase OLEDB driver. You are correct - both the typical and full install did not install the OLEDB driver automatically - I had to select the custom install and choose the Sybase OLEDB driver explicitly.

     

    With the Sybase ASE client installed, I am able to connect to the target Sybase database using my userid/pwd and select from the tables I have access to.  Then I tried to configure the Sybase OLEDB provider to import data from Sybase into SqlServer.

     

    From the main panel of SqlSerrver,  I right-clicked on 'Management' and selected 'Import Data' to launch SqlServer Import and Export Vizard. This takes me to the "Choose A Data Source" dialogue where I selected the Sybase OLEDB Provider and clicked on 'Properties'. I entered 'Data Source', 'Location', 'Userid' and 'Password' (same properties I used to make my direct connection to Sybase) but have no clue what to enter for 'Enter the Initial Catalog to Use'.  Without this catalog name I can't test the connection. The HELP panel is not very helpful either. I am basically an Oracle/SqlServe DBA trying to live with Sybase databases and have no idea what my initial catalogue will be.

     

    Please write back with your advice. Sorry about getting back after several days - it took that long to get the Sybase Open client from Sybase through my support channels!

     

    Jay

    Thursday, July 19, 2007 3:05 PM
  • I have marked in blue which I follow, let me  know if you need more info.

     Jay87 wrote:

    From the main panel of SqlSerrver,  I right-clicked on 'Management' and selected 'Import Data' to launch SqlServer Import and Export Vizard.

    This takes me to the "Choose A Data Source" dialogue where I selected the Sybase OLEDB Provider 
    (I select Sybase ASE OLEDB Provider here)

    and clicked on 'Properties'.

    I entered 'Data Source', 'Location', 'Userid' and 'Password' (same properties I used to make my direct connection to Sybase)  (I only enter userid and password here, Go to ALL Tab,  If you scroll down, there will 2 entries SERVER NAME, SERVER PORT ADDRESS, give your server name and port address here)

    but have no clue what to enter for 'Enter the Initial Catalog to Use' (Even if you dont give this, it will connect to the default database assigned to that user, so this should not be a problem). 
    Without this catalog name I can't test the connection. The HELP panel is not very helpful either. I am basically an Oracle/SqlServe DBA trying to live with Sybase databases and have no idea what my initial catalogue will be.

     

    Please write back with your advice. Sorry about getting back after several days - it took that long to get the Sybase Open client from Sybase through my support channels! 

    Jay



    By the way, if you are using an alias for Sybase Servers, then you will need to give the actual names in the server name field.

    Below steps are just in case, if you need:

    To get the actual name go to start menu->Programs->Sybase-> DSEDIT or DSEDIT Utitility - > Select Interface Drivers, The left panel will be aliases, right panel will have attributes and values. Check for server address here

    Hope this helps.

    Thanks

    Friday, July 20, 2007 12:10 AM
  • On the second thought, if you want to use ODBC connection for sybase source,

    Here "Choose A Data Source" dialogue, select .NET Providers For ODBC, in the connection string place this one with your values.

    Driver={SYBASE ASE ODBC Driver};na=servername,serverport;uid=userid;pwd=password

    And, there are different connection string for sybase based on version I guess. check with
    www.connectionstrings.com, if there are any issues with connection string.

    Thanks

    Friday, July 20, 2007 12:33 AM
  • Thanks for your response.

    No luck. This client does not use aliases for servernames. There is no Server Port Address property - there is a Location property instead and the HELP text expects me to enter the database name within the servername. BTW, entering the serrvername and location either in the property sheet or in the 'all' tab dialogue did not matter. I tried both.

     

    With my same userid/pwd combination, I have access to more than 1 sybase database servers hence there is no 'default' server assigned to me. Without the catalog name, I cannot move further.  The HELP text says 'Enter the catalog name' - duh!

     

     

     

    Friday, July 20, 2007 2:25 PM
  • Jay87,

    I have a dataflow that reads data from SYBASE and writes in SQL Server 2005.

    If you need help I can give you more details

    regards!

    Friday, July 20, 2007 2:55 PM
  • Can't you just setup an ODBC connection and then use a DataReader source in a data flow?????

    Control Panel -> Administrative Tools -> Data Sources (ODBC)

    Set up a user or system data source, and then when you build your data flow in SSIS using the DataReader Source, you should be able to pick from the ODBC entry you created.
    Friday, July 20, 2007 3:56 PM
    Moderator
  • yes Phil... I made as you described!

    Regards!!

    Friday, July 20, 2007 4:00 PM
  • I dont know how I can post a attachment here. I have a working copy for both OLE DB provider and ODBC provider.
    If some one can tell me how to post an attachment I can do that, else I'll try to write down a step by step instruction for this.

    Thanks

    Friday, July 20, 2007 4:05 PM

  • I 'll try with ODBC first.

    1. Open Import Data Wizard From SSMS
    2. Select .NET Framework Data Provider for ODBC
    3. In the connectionstring enter this Driver={SYBASE ASE ODBC Driver};na=servername,serverport;uid=userid;pwd=password(replace your values.)
    4. Replace your server name, give server port as 5000 if sybase server is in a windows box, if its a unix box give 4100
    5. If everything is ok, then it will go to the next screen else you will get an error box.


    This is based on the assumption that you have Sybase ASE ODBC Driver installed on your system, if you have any other sybase odbc driver let me know. I can try and see, only just the connection string will differ.

    Just curious, Did you try with DSEdit to get the server names and ports, any luck with that?

    Thanks

    Friday, July 20, 2007 4:27 PM

  • Now the OLE-DB Provider in BIDS / Import / Export Wizard

    In BIDS,

    Add New OLE-DB Connection.
    In the "Connection Manager" Dialog box, select "Sybase ASE OLEDB Provider" as Provider
    Click on DATALINKS Button


    In Import / Export Wizard, Select DataSource as "Sybase ASE OLEDB Provider"

    The below steps are common for BIDS / Import / Export Wizard.

    The Data Links Properties Dialog box will have 3 TABS, Connection, Advanced, ALL

    Go to ALL TAB, it will have the list of properties.

    1. select user id, click on edit value, this will open another small pop up, enter your sybase user id there.
    2. select password,click on edit value, this will open another small pop up, enter your sybase password there.
    3. select server name,click on edit value, this will open another small pop up, enter your sybase server name there.
    4. If your sybase server running on unix box, select server port,click on edit value, this will open another small pop up, enter your sybase port address there, usuall I think its 4100.
    5. Come to Connection Tab, You will see the user id and password populated there, click on Test Connection to test the connection.
    6. If connection is success full, click on the initial catalog to use drop down, it will show all the databases in that server.

    This is what I do, and it always works for me. Sorry for all that bold stuff, I just want to highlight it.

    Let me know, if you need more info.

    Thanks

    Friday, July 20, 2007 4:51 PM
  • Thank you for quick response. I appreciate that step-by-step instruction as well. Actually, this is what exactly I did. I created the data flow step, defined both the source and target using the Sybase OLEDB provider. I selected 1 table for my testing and did not store it in SSIS to avoid any SSIS related issues. I am able to preview the contents of the seleced table as well. Still,the execution of the data flow failed. Please see the following report:

     

    - Initializing Data Flow Task (Success)

    - Initializing Connections (Success)

    - Setting SQL Command (Success)

    - Setting Source Connection (Error)

    Messages

    Error 0xc0202009: Source - Collateral_mfrpt [1]: An OLE DB error has occurred. Error code: 0x80004001.
    (SQL Server Import and Export Wizard)


     

    Error 0xc020204a: Source - Collateral_mfrpt [1]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
    (SQL Server Import and Export Wizard)

     

    Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)

     

    - Setting Destination Connection (Stopped)

     

    Unbelievable!

     

     

    Friday, July 20, 2007 6:23 PM
  • Did you got a chance to try odbc?

    Also, Can you let me know the ODBC Drivers / OLE DB Drivers you are using?

    Thanks

    Friday, July 20, 2007 6:31 PM
  • Sybase ASE OLEDB Provider 12.5.1.510

     

    Haven't used the ODBC driver. Still looking for the version number for it.

    Friday, July 20, 2007 6:47 PM
  • >> created the data flow step, defined both the source and target using the Sybase OLEDB provider. I selected 1 table for my testing and did not store it in SSIS to avoid any SSIS related issues. I am able to preview the contents of the seleced table as well.

    If you are able to preview the contents from the selected table, I dont think there was any problems with your connection . Can you tell us what you mean by the highlighted statement?

    Thanks

    Friday, July 20, 2007 7:04 PM
  • I selected 1 table for copying and clicked on Next. IN addition to the 'Execute Immediately', the systems asks if you want to store this as a SSIS package and gives you 2 choices: MSDB or File. I Unchecked both so that there would not be any SSIS package created and stored. I did this to take any SSIS related issues out of the picture.

     

    Ok. Here are the driver information.

    Sybase ASE OLEDB Provider 12.5.1.510
    Sybase ASE ODBC Driver 03.50.0010

     

    When I use the ODBC connection using your suggested connection string I get the following error:

    ERROR[HY000][SYBASE][ODBC Sybase Driver]Insufficient information to connect to the data source
    ERROR[01S00][SYBASE][ODBC Sybase Driver]Invalid Attribute in connection string: na

    Friday, July 20, 2007 7:08 PM
  •  Jay87 wrote:


    Ok. Here are the driver information.
    Sybase ASE OLEDB Provider 12.5.1.510
    Sybase ASE ODBC Driver 03.50.0010

    When I use the ODBC connection using your suggested connection string I get the following error:
    ERROR[HY000][SYBASE][ODBC Sybase Driver]Insufficient information to connect to the data source
    ERROR[01S00][SYBASE][ODBC Sybase Driver]Invalid Attribute in connection string: na


    I'm asking this question, very late I guess. What is the version of Sybase Server you are trying to connect?

    Thanks


    Friday, July 20, 2007 7:26 PM
  • 12.5.3 Adaptive Server Enterrprise running on Sun OS 5.8

     

    I tried the ODBC route as well.  I had a feeling my Sybase ASE ODBC driver was not installed properly so I did the reinstall. Using ControlPanel - ADmin Panel, created a new DSN called SybODBC and tested its connectivity. With my userid and password, I got the message 'Login Succeeded'. I thought I solved the problem. But, no no. When I tried this DSN in my SqlServer Import Export Wizard using .Net Framework Data Provider for ODBC, it did not work. I got:Cannot get the supported data types from the database connection "DSN=SybODBC"

    Friday, July 20, 2007 7:39 PM
  •  Jay87 wrote:

    12.5.3 Adaptive Server Enterrprise running on Sun OS 5.8



    Can you check what is the port its using?, if the port used by Sybase and port specified in the connection are same?
    Did you try DSEDIT suggested earlier to find the port?

    Thanks
    Friday, July 20, 2007 8:09 PM
  • this solved the sybase Data Source issue for me, many thanks

    Thursday, September 11, 2008 6:54 AM
  • Can you please describe how you deal with. I have the same problem.
     
    Thank you
    Wednesday, October 15, 2008 3:42 PM
  • @Jay: I am working on one project which needs migrate data from sybase 12.5 to SQL server 2005. I need some assistance for it. I looking for OLE DB driver for ASE 12.5.

    You can reach me at chirag.patel2121@gmail.com

    Thanks,


    ckp


    • Edited by CHIRAG PATEL2121 Thursday, November 29, 2012 7:38 PM version changed
    Thursday, November 29, 2012 7:09 PM