Split one unnormalized table into a few normalized tables. Handle Insert/Update/Delete.

• FordMom

  SSCommitted

  Points: 1795

  More actions

  March 20, 2009 at 8:14 am

  #128817

  This is my first SSIS package since taking a class a month or so ago, and I'm stumped on how to proceed with the basic design of this package. Any suggestions or directing me to a good article, would be greatly appreciated.

  I have this staging table that is truncated at the beginning of the package, then populated from a flat file. (I know, I know...I don't really need a staging table, but I know there is going to be a ton of garbage in this table and I want to give the developers access to this table so they can query against it to help them figure out why their data isn't clean).

  CREATE TABLE dbo.ImportPendPolicy (

  AgentId1CHAR(6)NULL

  ,ProducerId1CHAR(4)NULL

  ,AgentId2CHAR(6)NULL

  ,ProducerId2CHAR(4)NULL

  ,PolicyNbrCHAR(7)NULL

  ,InsuredPidNbr1CHAR(6)NULL

  ,PolicyTypeCHAR(1)NULL

  ,StatusMsgCHAR(24) NULL

  ,EnteredDate CHAR(8)NULL

  ,StatusCodeCHAR(1)NULL

  ,ReleaseDateCHAR(8)NULL

  ,FaceAmtCHAR(7)NULL

  ,CashWithAppMONEYNULL

  ,InsertDateTimeDATETIMENULL)

  I have a couple of Lookups and am redirecting "bad" rows to an error table. Then I need to Insert, Update or Delete the data into the normalized tables (below). If it's a new PolicyNbr, the rows need to be inserted. If it's an existing PolicyNbr, the rows need to be updated. If it's a PolicyNbr not in ImportPendPolicy but is in WebPendPolicy, then the rows need to be deleted from the normalized tables.

  This is where I am stumped. I know I can write a stored proc to do all of this and execute it in an Execute SQL Task. But isn't there a better way? I played around with the Slowly Changing Dimension but I don't think this is just what I need. I can't use a Conditional Split or Multicast because they handle the row as a whole rather than normalizing them. I don't know VB but can stumble through it if I had some basic code to copy. I know I will be having lots of similiar requests like this one and want to make sure I'm going down the right path. Any help is greatly appreciated!

  Thanks.

  CREATE TABLE dbo.WebPendPolicy (

  PolicyNbrCHAR(7)NOT NULL

  ,PolicyTypeCHAR(1)NOT NULL

  ,StatusMsgVARCHAR(24)NULL

  ,EnteredDate DATETIMENOT NULL

  ,StatusCodeCHAR(1)NOT NULL

  ,ReleaseDate DATETIMENULL

  ,FaceAmtINTNOT NULL

  ,CashWithAppDEC(10,2)NOT NULL

  ,InsertDateTimeDATETIMENOT NULL)

  CREATE TABLE dbo.WebPendProducer (

  PolicyNbrCHAR(7)NOT NULL,

  OrderNbrSMALLINTNOT NULL,

  AgentIdCHAR(6)NOT NULL,

  ProducerIdCHAR(4)NOT NULL)

  CREATE TABLE dbo.WebPendInsured (

  PolicyNbrCHAR(7)NOT NULL,

  OrderNbrSMALLINTNOT NULL,

  PidNbrCHAR(6)NOT NULL)

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  March 20, 2009 at 6:37 pm

  #963879

  Once you have the data imported to your staging area in SQL Server, going down the sp route is the way I would go - for performance reasons.

  SSIS is great when you have things to do (imports/exports etc) which fall outside of the SQL Server realm, but as soon as you are wholly inside, sp's are the way to go, in my opinion.

  Phil

  ---