Conditional DTS Package Execution

• throbinson

  SSC Enthusiast

  Points: 104

  More actions

  January 7, 2002 at 4:06 pm

  #170406

  I need to update a dimension during Business hours while minimizing the impact on my Users. I'm thinking the best way to do this is to define two dimension tables with a single view on top. While one physical table is loading during the day the other is being used by the view. When the load is done, I redefine the view using the updated table. The dimension is unavailable only for the time it takes to execute the SQL redefining the view. Every day I alternate physical tables while redefining the view daily. I would like to define some kind of conditional logic that would automatically alternate the execution of a package possibly one package calling two others depending on the value of a global variable? I would greatly appreciate any suggestions on how I could accomplish the conditional execution of a package or any other ideas. Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737892

  More actions

  January 7, 2002 at 4:16 pm

  #421855

  You could let one package call another conditionally based on a global variable. The dynamic properties task can handle this and change the name of hte package to execute based on a global variable, query, etc.

  Steve Jones

  steve@dkranch.net

• Robert W Marda

  SSChampion

  Points: 13413

  More actions

  January 7, 2002 at 8:43 pm

  #421868

  This sound similar to something we do that could work for you. We have DTS packages that create a table with _new on the end and then pump the new data into that table. Upon completion we use sp_rename within a begin and commit transaction to rename the current table with _old and rename the table with _new to the normal table name. This way access to data is interupted for only about a second or less. And during the import access to the data is only slightly slower than normal.

  Robert Marda

  Robert W. Marda
  Billing and OSS Specialist - SQL Programmer
  MCL Systems