SQL Server 2012 SSIS - SCD & Sequences

Question

Post reply

SQL Server 2012 SSIS - SCD & Sequences

DuncEduardo

SSCarpal Tunnel

Points: 4410
More actions
February 2, 2015 at 4:06 am

#303017

Hi,
I wonder if anyone can help?
I've set up a simple SSIS package to populate a Dimension for Data Warehouse POC project.
I am using the Slowly Changing Dimension transformation to handle this Type 2 dimension.
I have created the Dimension table at the destination and I have applied a PK using the new Sequence object available in SQL 2012.
To also let you know the source data for this dimension has a varchar PK as the primary reference column and is a mixture of alphanumeric characters (for invoicing). So, I am stuck with a varchar PK for now, unfortunately.
As the table loads I'm obviously expecting the PK values to be populated/"incremented" using the Sequence object (NEXT VALUE ...) but the SSIS package doesn't like this and errors immediately.
One forum mentions changing the OLE DB Destination properties for "Data Access Mode" to "Table or View - fast load" but I have tried this and doesn't work - still same error.
Are we saying that the Sequence object and the SCD transformation are incompatible or is there a workaround?
Many thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Deepak Sharma-2311 SSC Eights! Points: 993 More actions · Answer 1

Hi,

I am still unable to get your error... But, I think below link will help you for sure:

https://msdn.microsoft.com/en-us/library/ms188439.aspx

Please read "Keep identity".

Deepak Kumar Sharma

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 2

Hi,

I have just run the package again to extract the error.

I made a simple change on one column at source to demonstrate the problem.

Here is part of the error:

Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'ACCDWKey', table '_Core.dbo.DimACC'; column does not allow nulls. INSERT fails.".

The SCD flows through ok but fails just at the "Insert Destination" part with above error.

So, the task does find the matching record in dimension and updates the old record "CurrentFlag" to 0.

However, it cannot insert the new row as the destination dimension demands that the PK contains a value (Sequence) and the task is not inserting a value because it is not fetching next value from sequence.

There doesn't appear to be an option for this with the SCD Wizard.

When I use the wizard, the screen "Select a Dimension Table and keys" the Dimension columns are detected and listed, however the only options are : original "business key" and "Not a key column" only. The PK of dimension (ACCDWKey) sits at the top with a space box on either side so I'm not sure how SQL Server is categorising this column.

I wish I could provide screenshots - it would be easier to explain.

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 3

Here is the SQL to create Sequence, destination Dimension and initial Dimension population:

Here is the screenshot (combined) showing the set up of SCD wizard:

Here is package error screen:

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 4

... sorry I was unable to create URL link to screenshots

Basically, I need advice in how best to use SCD transform in SSIS 2012 in conjunction with a PK using new 2012 Sequence object.

At the moment the SCD transform errors when trying to insert new values into PK

Lowell SSC Guru Points: 323504 More actions · Answer 5

those files are on your local C drive, and not on the internet...we can't see them.

DuncEduardo (2/2/2015)
Here is the SQL to create Sequence, destination Dimension and initial Dimension population:
C:\Users\dunsmi\Pictures\VGroup\SCD.png[/ img]
Here is the screenshot (combined) showing the set up of SCD wizard:
C:\Users\dunsmi\Pictures\VGroup\SCD_SQL1_3[/ img]
Here is package error screen:
C:\Users\dunsmi\Pictures\VGroup\SCD_Error[/ img]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 6

Lowell,

Yes I know this - an error on my part and realised it as soon as I posted.

Here is the basic SQL to create sequence, create dimension and populate dimension with initial bulk data.

IF OBJECT_ID('dbo.SeqACCDwKey','SO') IS NOT NULL

DROP SEQUENCE dbo.SeqACCDwKey;

GO

CREATE SEQUENCE dbo.SeqACCDwKey AS INT

START WITH 1

INCREMENT BY 1;

GO

CREATE TABLE dbo.DimACC --TYPE2 SCD --contains all records (4k)

(

ACCDWKey VARCHAR(4) NOT NULL, --surrogate key, assign values with a sequence

COY_ID VARCHAR(4) NOT NULL,--business key from dbo.ACCCompany

COY_Name VARCHAR(30) NULL,

COY_SourceID VARCHAR(12) NOT NULL,

CHH_ID VARCHAR(12) NULL,

COY_CoyType VARCHAR(1) NOT NULL,

COY_Curr VARCHAR(3) NULL,

CLH_ID VARCHAR(12) NULL,

COY_OwnerID VARCHAR(12) NULL,

COY_EntityAPValid TINYINT NULL,

CurrentFlag BIT NOT NULL DEFAULT 1,

CONSTRAINT PK_ACC PRIMARY KEY (ACCDWKey)

);

GO

--Initial Bulk Load into DimACC

USE _Core

INSERT INTO [dbo].[DimACC]

([ACCDWKey]

,[COY_ID]

,[COY_Name]

,[COY_SourceID]

,[CHH_ID]

,[COY_CoyType]

,[COY_Curr]

,[CLH_ID]

,[COY_OwnerID]

,[COY_EntityAPValid]

)

SELECT

NEXT VALUE FOR [dbo].[SeqACCDwKey] AS [ACCDWKey]

,Coy_ID

,Coy_Name

,COY_SourceID

,CHH_ID

,COY_CoyType

,COY_Curr

,CLH_ID

,COY_OwnerID

,COY_EntityAPValid

FROM xxx

In SSIS, in SCD wizard, the PK column (ACCDWKey) neither displays as "business key" or "not a key column" nor can you select these options against this column from the drop-down. So, it looks like SSIS has detected this column as PK, which is fine (white space in cells to right and left)

However, when the package runs and it tries to insert the "changed" row into destination dimension, it errors because there is some kind of disconnect between SSIS and the sequence generator.

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 7

I honestly thought with all the expertise out there that someone would know the answer to this fundamental question:

"How does SSIS (SQL Server 2012) handle Sequence objects in Slowly Changing Dimension transformation for loading data to a data warehouse?"

The target table uses a Sequence object for primary key, and yet the SCD task fails at point "Insert to Destination" saying that that the PK column cannot be NULL.

It is a simple scenario and yet very difficult to get good feedback and advice.

Hoping someone can help.

Thanks.

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 8

Update on this seeing as nobody has a clue:

Am creating a "Primary Key Mapping" table which takes the original "varchar" key and maps to new INT key.

Any new inserts to table are handled via existing audit trail trigger.

I can then use a view to query the "lookup" table to grab the new INT key together with any other column I require.

This then forms part of the surrogate process in processing SCD via SSIS 2012.

Works quite nicely.

ali81_van SSC Rookie Points: 47 More actions · Answer 9

** I know this is a super late reply which might be maybe useful for other readers.

Looking at your original description, you are either not explaining it right or you have a problem in your design/DW concepts. The key for a dimension is supposed to be a surrogate key and has nothing to do with the source PK. So, when you say you have to make the dim key a varchar I am not sure what you actually mean by that.

As for the issue in SSIS, I haven't tested this, but I believe you will need to create a Default constraint on your dimension key which will basically call the NEXT VALUE FOR function on your sequence object. This way SSIS will not insert into that column and the default will take care of it (similar to how an identity column would work).