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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply