March 20, 2009 at 8:14 am
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)
March 20, 2009 at 6:37 pm
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
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply