none
ODBC to SQLClient strategy RRS feed

  • Question

  • I am in the middle of attempting to obtain data using an ODBC driver (DSN) and then writing data to a SQL server using SQLClient object set.

    Would it be advisable to use a DataSet object as the intermediate handler of the information to the DataAdapters for each side?


    Nothing but something
    • Moved by Cookie Luo Thursday, February 10, 2011 9:30 AM (From:.NET Base Class Library)
    Tuesday, February 8, 2011 8:53 PM

Answers

All replies

  • Try the ADO.Net Dataset forum at http://social.msdn.microsoft.com/forums/en-US/category/dataplatformdev/

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Tuesday, February 8, 2011 9:28 PM
  • Hi Goldbishop,

    Welcome to ado.net providers forum.

    Base you description, would you like to migrate data from odbc datasource to ms sql.

    Use the sqlcommand to write data to the database is a good idea. But it may run out of your memory when handle mass data.

    I sugges you to consider the SSIS(Miscrsoft Sql Server Intergration Services.)

    SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL).

    ODBC connection in SSIS

    http://social.technet.microsoft.com/Forums/zh-CN/sqlintegrationservices/thread/6d5026a8-334d-47d4-ba5b-d4701de1c790

    SQL Server Integration Server

    http://msdn.microsoft.com/en-us/library/ms141026.aspx

    I hope that would be help you, and if I have a misunderstood or other issues please let me know.

    Have a good day.


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jackie-SunModerator Tuesday, February 15, 2011 5:16 AM
    • Unmarked as answer by goldbishop Wednesday, February 16, 2011 4:22 PM
    • Marked as answer by goldbishop Thursday, March 3, 2011 5:43 PM
    Friday, February 11, 2011 2:21 AM
    Moderator
  • Yeah the SSIS strategy is not viable, or at least reliable, for the operation we are doing.  I am creating a small application that i am able to get an ODBC connection created, information collected, and then passed on to the SQL Server.

    Problem is i have a .sql file (irrelevant to the discussion) that i am reading from for the ODBC source database tables to extract.  Problem is i want to dynamically construct the Select statement, collect the data, and then transition the data the SQL database table.  Currently i have explicitely structured XSD framework for DataSets, TableAdapters and DataAdapters.  I want to remove this overhead and make it generic and store information temporarily  in a DataSet, as a holding pin of sorts, before moving it to the SQL server database.

    Source Database: Sage Timberline (uses Pervasive)

    Destination Database: SQL Server 2005.

    SSIS uses ADO.Net which does not reliably create a connection to the Timberline database and the two databases are not on the same server so i have use a DSN reference to the database for SSIS, which seems to be the issue with the ADO.Net connector.

    In my little application i bypass the DSN registration with a ODBC connector and get everything i need that way.  But there is not clear cut migration between the ODBC namespace and SQLClient namespace, that i have seen.


    Nothing but something
    Wednesday, February 16, 2011 4:21 PM