Post reply

Failover in SSIS

  • March 24, 2012 at 8:16 am

    #253088

    Hi,

    My SSIS package needs to pull data from a table (say table1 in Database1 in Server1). When data is not available in table 1 it should pull data from another table(say table2 in Database2 in Server2).

    Please suggest how to approach/implement this.

    This tables are oracle tables.

    Thanks

    Regards

    Jim

  • March 25, 2012 at 12:19 pm

    #1464019

    What is your criteria for whether the data is available?

    CEWII

  • March 26, 2012 at 5:05 am

    #1464161

    We are checking based on count(*) >0 i.e. if table1 has atleast 1 row, pull that data. If table1 has no rows then pull data from table2. If table2 also has no rows then Stop.

    Please let me know if you need more details.

    Thanks

    Jim

  • March 28, 2012 at 2:59 pm

    #1466024

    I'd use an Execute SQL task to query table 1. if it has rows, set SomeVariable = table1. if rowcount = 0, query table2...

    then use tablename stored in the variable as source.

  • March 29, 2012 at 2:26 pm

    #1466622

    I will add a little detail to the previous post:

    EXECUTE SQL TASK - POPULATE three variables based on counts:

    @NbrRecords_in_TableA = 0 (or the count)

    @NbrRecords_in_TableB = 0 (or the count)

    @Records_Exist = 0 or 1 (based on if previous counts are > 0)

    The create three processes setting a Precedence Constraint Editor

    @Records_Exist=="0" then stop processing no records in either table (maybe send an email stating no records processed)

    @NbrRecords_in_TableA>0 import those records (using data flow task)

    @NbrRecords_in_TableB>0 import those records (using data flow task)

    Really simple, but keep in mind everyone approaches these things differently.

    Hope this helps.

  • April 1, 2012 at 12:09 am

    #1467379

    Thanks a lot. I will update after implementing the same. Thanks again.

    Also, need to log only those rows which are getting truncated.

    For e.g. if the source table is having a column value of 6 characters and destination column size is 4 characters. The insert is a success and no warning is raised by SSIS, but 2 characters will be truncated. Such rows need to be logged. SSIS raises a common warning as the source column size is more than the destination.

    Any suggestions will be of great help.

    I will update the post after implementing the failover and share my learnings

    Thanks

    Regards

    Jim

  • May 22, 2012 at 1:10 am

    #1490868

    Hi,

    As promised, I'm updating the post after implementing :). I have handled the requirement at the Precedent contrainst level. I created a variable to store the count of rows and in the Precedent constraint, I'm checking if the no of rows is >0 execute dataflow which is pointing to primary server else execute dataflow which is pointing to failover server.

    It worked in Int environment and hope it works in UAT and Prod as well 🙂

    I thank all of you for spending your time and helping me out.

    Thanks

    Regards

    Jim

Viewing 7 posts - 1 through 6 (of 6 total)

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