Post reply

Interesting problem needs interesting solution

  • January 9, 2009 at 6:44 am

    #198115

    I am building a relational model and process in SS2k5 T-Sql that will "map" data that is stored in SS2k. The data that is being loaded that needs the "mappings" are run through DTS packages and stored on a 2K box. Without adding any new packages how would I be able to have the process on a 2K5 box work syncronously with the 2K process.

    Here is a quasi explanation of my issue:

    1. Data is loaded via DTS to a 2K machine.

    2. During this process, I would need to run my "new" process that I am building without the user knowing that this piece is being done. I cant impact the current process (no new steps). This involves taking distinct values from the data sets and inserting them in to tables that have keys etc etc.

    3. Once edits etc are done, then data is uploaded to a different 2k machine (data warehouse).

    I need to know how I can get a 2K5 process to run inside of the DTS package without physically transfering data by adding additonal manual steps.

    If anyone out there can help me out that would be greatly appreciated.

    Thanks

  • January 9, 2009 at 7:23 am

    #924148

    Hi samartel

    Let me rephrase to validate my understanding

    1- Data is imported into a staging database (SQL Server 2000) using DTS. Let's call this server STAGING

    2- After the data is loaded onto STAGING users edit the data.

    3- When editing is completed another DTS exports the data from STAGING to a data warehouse.

    You want the same data that is imported at Step 1 to be loaded onto another database on your SQL 2005 server.

    Is this what you are trying to accomplish?

  • January 9, 2009 at 7:34 am

    #924164

    Hey Maxim,

    Thanks for your reply

    Yes, that is exactly what is being done. With one exception. The DTS package is performing the edits automatically (no user intervention at this point). After the data is loaded, the user, through an app, will be able to go and modify my new systems data (stored in a 2K5 box) which is relational to the data warehouse (2K box) via a key.

  • January 9, 2009 at 8:16 am

    #924214

    Alright now that I understand what is being done let's see what we are trying to do. Bear with me I need very clear specifications before I can compute anything! :hehe:

    After the data has been loaded on STAGING and DW (DataWarehouse), a user will use your new application, connected to your SQL2K5 database, to modify the data in DW?

    I probably got this wrong because your application would connect directly to DW...

    I am unsure if you're trying to duplicate the data on DW and SQL2K5 or you want to have a smaller subset of the data on SQL2K5 for a user to work with or maybe I got this totally wrong?

    In both case you are looking at creating an SSIS package on your SQL2K5 server to import the data you need but we'll need a little more detail on this process before we do anything.

    Here are a few questions that come to mind:

    - What data do you have/want on SQL2K5?

    - How much data needs to be processed? (size)

    - Is there change tracking involved (modification to existing data) or is it an incremental load (appending data)?

    Anything else you can share is sure to help define a better solution!

  • January 9, 2009 at 9:57 am

    #924350

    Hey Maxim,

    Thanks again for your reply.

    - What data do you have/want on SQL2K5? - I have/want all of the system that will be used to process the "rules" for a particular value. These tables will be updateable through an application.

    - How much data needs to be processed? (size) - Approx 5 mil/week in data, the system will handle about 500 agg values/week

    - Is there change tracking involved (modification to existing data) or is it an incremental load (appending data)? Incremental as a result of the system I am building. I need my system to attach an int to Staging data that is the key in "The master table" lets call it. The master table will then get updated through the application. The user can then use the warehouse data joined to the Master table to get his desired result.

    I am just wondering if there is a way 2000 can interact with 2005 without linked servers as it violates security. And, this is the biggie, without adding extra processes (packages) namely.

    I appreciate all of the time you put into reading and helping with this thread. I just dont want to cop out and not use the advanced 2005 TSQL features that easily.

    Thanks

  • January 9, 2009 at 12:45 pm

    #924508

    To run a distributed query on 2000 and/or 2005 I am fairly sure using a linked server is your only option.

    I could be wrong, maybe someone more knowledgeable could help here? I know that is how I have been doing it.

    What is the security issue you have with using a linked server?

    The other option is either a DTS to export from 2000 to 2005 or a SSIS to import from 2005 to 2000 but you have also said this could not be done? Is this because you can't put any extra load on the 2000 server or something else?

  • January 12, 2009 at 5:30 am

    #925195

    Hey Maxim,

    Linked servers are not our policy here in our 2005 framework.

    Our processes are so ingrained here that adding new DTS packages would be rediculous as we are investigating how we can convert all of them to 2k5. Adding new SSIS packages would be difficult because this process i want to build needs to ensure that data doesnt get to the warehouse prior to the process being run.

    Maybe I am trying too hard to find the easy solution

    Thanks

  • January 12, 2009 at 8:18 am

    #925292

    Hi samartel,

    - no linked server

    - no modification to existing DTS

    - no new package (DTS, SSIS)...

    - process must be run prior to sending data from STAGING to DW

    To me that last requirement appears to be the most important and it contradicts the requirement not to modify existing DTS since from my understanding the data is loaded on STAGING and sent to DW in the same DTS package.

    This process needs to be split somehow to let you add a new step and if I understand the relation you're trying to create with your data (key link between records on SQL2k5 and DW) the new step should encapsulate both the loading on SQL2k5 and on DW to make sure data is consistent at all time.

    hth...

  • January 12, 2009 at 8:43 am

    #925316

    Sorry Maxim,

    I think i led you astray somewhere. I can modify existing DTS packages only, just no creation of new DTS processes.

  • January 12, 2009 at 9:01 am

    #925333

    Hi again!

    I read over again and I'm not why I made that assumption... :hehe:

    Now that I finally understand the specifications...

    In the DTS wouldn't you just need two OLE connection and a Transform Data Task to export data from STAGING to SQL2k5?

  • January 12, 2009 at 9:07 am

    #925338

    I think that is how I am going to have to go about it. I think I may have been trying to find a difficult solution to a simpler problem.

    I was worried more about locking of tables in this solution but I think now that I have a starting point, I can worry about those details as I cross them.

    Thanks for your assistance on this Maxim.

    Steve

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply