none
Combining Data From Disparate Sources RRS feed

  • Question

  • I am collecting data from two database sources - MS SQL (2005) and Pervasice SQL (9.5). The data i am retrieving from each source has a relatable field, but i don't know the best way to combine the data to fill by business objects.

    Currently i am pulling the MS SQL data into a dataset, looping through the rows to fill a list of objects - works great and is fairly speedy. How can i do this with two datasets?
    Thursday, January 24, 2008 5:46 AM

Answers

  •  

    If you are pulling data together from two different databases/sources you should consider SSIS in SQL2005 and feed the data from the 2 sources into a common buffer table(s).  From there process/check/filter your data appropriately at the back end.

     

    Gerry

    Thursday, January 24, 2008 12:53 PM
  • There's no reason you can't include data from multiple sources in a single DataSet.  The trick is building a DataAdapter that can fill and update it.  It's not crazy hard to build a class that implements IDataAdapter and that can interoperate with both SQL Server and Pervasive SQL, but you need to have a pretty good handle on all of the pieces of the puzzle to attempt it.

    Thursday, January 24, 2008 11:48 PM
  • I've need to do this in the past and I'd built a nice dataset based on the SQL Server database and then manually added the tables and relationships for data that came from some external vendors CSV files.

    When it came to load time, I did the sql stuff as per normal, then had a special load operation for the other data. 

    If you can't get a dataadaptor to work directly for the hand-crafted tables then you can always load it whichever way is easiest for you before manually creating rows in your additional tables....

    Alternative have a totally XSD derived dataset in a format that best suits your coding requirements and import manually into it from both datasources....that way the final dataset is completely divorced from whatever datasources you have....
    Friday, January 25, 2008 4:00 AM

All replies

  •  

    If you are pulling data together from two different databases/sources you should consider SSIS in SQL2005 and feed the data from the 2 sources into a common buffer table(s).  From there process/check/filter your data appropriately at the back end.

     

    Gerry

    Thursday, January 24, 2008 12:53 PM
  • The data is updated regularly, so the job would have to run frequently. I considered that but wasn't sure if it was the best way to go.
    Thursday, January 24, 2008 10:01 PM
  • There's no reason you can't include data from multiple sources in a single DataSet.  The trick is building a DataAdapter that can fill and update it.  It's not crazy hard to build a class that implements IDataAdapter and that can interoperate with both SQL Server and Pervasive SQL, but you need to have a pretty good handle on all of the pieces of the puzzle to attempt it.

    Thursday, January 24, 2008 11:48 PM
  • I've need to do this in the past and I'd built a nice dataset based on the SQL Server database and then manually added the tables and relationships for data that came from some external vendors CSV files.

    When it came to load time, I did the sql stuff as per normal, then had a special load operation for the other data. 

    If you can't get a dataadaptor to work directly for the hand-crafted tables then you can always load it whichever way is easiest for you before manually creating rows in your additional tables....

    Alternative have a totally XSD derived dataset in a format that best suits your coding requirements and import manually into it from both datasources....that way the final dataset is completely divorced from whatever datasources you have....
    Friday, January 25, 2008 4:00 AM
  • I've gathered a bunch of documentation from MSDN on creating custom data adapters. I haven't had a chance to dig deeply into it, but it's definitely a more elaborate solution than i was hoping for. I might look into what you've proposed, Liam. That is more along the direction i had expected...

    Thanks all - Josh

    Monday, January 28, 2008 8:02 PM