SQL Database Design for SSIS

OK my first question so here goes.

Currently users are using a huge Access Application. They wanted a web application with some functionality based off of the Access data and with some modifications.

Ok no problem. I开发者_StackOverflow社区 used the Access to SQL migration assistant to convert the data over and then wrote some SSIS packages which are executed from the web end to allow the application to be updated as needed. All here is good.

Here is where I am kind of stumped. There are 2 types of imports, quarterly and yearly. The quarterly is fine but the yearly import is causing issues. The yearly import can be for an adopted budget or for a proposed budget (each is held in a separate Access db). I have one SSIS package for each type of yearly import. The table where the information goes is as follows..

CREATE TABLE Budget
(
    BudgetID           uniqueidentifier NOT NULL,
    ProjectNumber      int NOT NULL,
    SubProjectNumber   varchar(6) NOT NULL,
    FiscalYearBegin    int NOT NULL,
    FiscalYearEnd      int NOT NULL,
    Sequence           int NULL,
    QuarterImportDate  datetime NULL,
    ProposedBudget     money NULL,
    AdoptedBudget      money NULL,

    CONSTRAINT PK_Budget PRIMARY KEY CLUSTERED 
    (
        BudgetID ASC
    ),

    CONSTRAINT uc_Budget UNIQUE NONCLUSTERED 
    (
        ProjectNumber ASC,
        SubProjectNumber ASC,
        FiscalYearBegin ASC,
        FiscalYearEnd ASC,
        Sequence ASC
    )
)

Also, there can be multiple versions of the budget for the specific year in terms of Project, SubProject, FiscalYearBegin, and FiscalYearEnd. Thats is why there is a sequence number.

So the problem becomes, since I have 2 different SSIS packages, each of which is an update statement on 1 specific column (either ProposedBudget or AdoptedBudget), I have no effective way of keeping track of the correct sequence.

Please let me know if I can make this any clearer, and any advice would be great!

Thanks.

Something like this will get you the next item with an empty AdoptedBudget, but I think you will need a cursor when there are multiple AdoptedBudgets. I was thinking of doing a nested subquery with an update, but that won't work when there are multiple AdoptedBudgets. It sounds like in the source application they should be selecting a ProposedBudget whenever they add the AdoptedBudget so that a relationship can be created. This way it is clear which AdoptedBudget goes with which ProposedBudget, and it would be a simple join. I have almost the same scenario, but the difference is that I don't keep all the versions. I only have to keep the most current "ProposedBudget" and most current "AdoptedBudget". It's a little bit more difficult trying to sequence them all.

  --get the smallest SequenceId with an unfilled AdoptedBudget
  Select min(SequenceID), 
    ProjectNumber, 
    FiscalYearBegin, 
    SubProjectNumber --any other fields needed for the join
  From Budgets b
  Where AdoptedBudget is null
  Group By 
    ProjectNumber, 
    FiscalYearBegin, 
    SubProjectNumber --any other fields needed for the join


--This won't work I don't believe
Update Budgets
Set AdoptedBudget = BudgetAmount
From Budgets b
Inner Join SourceAdoptedBudgets ab on 
  b.ProjectNumber = ab.ProjectNumber
  b.FiscalYearBegin = ab.FiscalYearBegin
  b.FiscalYearEnd = ab.FiscalYearEnd
Inner Join
(
  --get the smallest SequenceId with an unfilled AdoptedBudget
  Select min(SequenceID), 
    ProjectNumber, 
    FiscalYearBegin, 
    SubProjectNumber --any other fields needed for the join
  From Budgets b
  Where AdoptedBudget is null
  Group By 
    ProjectNumber, 
    FiscalYearBegin, 
    SubProjectNumber --any other fields needed for the join
) as nextBudgets
on --the join fields again

Something like this using the BudgetType. Of course you'd probably create a code table for these or a IsAdopted bit field. But you get the idea.

Select 
budgets.*
,row_number() over(partition by 
   ProjectNumber
  ,SubProjectNumber
  ,FiscalYearBegin
  ,FiscalYearEnd 
   order by QuarterImportDate) as SequenceNumber
From
(
  Select
  ProjectNumber
  ,SubProjectNumber
  ,FiscalYearBegin
  ,FiscalYearEnd
  ,QuarterImportDate
  ,'Proposed' as BudgetType
  ,ProposedBudget as Budget
  From sourceProposed

  Union

  Select
  ProjectNumber
  ,SubProjectNumber
  ,FiscalYearBegin
  ,FiscalYearEnd
  ,QuarterImportDate
  ,'Adopted' as BudgetType
  ,AdoptedBudget as Budget
  From sourceAdopted
) as budgets