Implementing audit dimension error

[ivan77]

Hello,

I am trying to follow along with implementing the audit dimension as per Kimball's MDWT_2008 ssis package, which is also outlined in The Microsoft Data Warehouse book.

I keep getting the error:

[Execute SQL Task] Error: Executing the query "INSERT INTO [DimAudit]
([ParentAuditKey..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

As a package variable, I defined it as In32 with the initial value of 0. In the execute SQL, I added the User:ParentAuditKey as a numeric data type since Int32 is not available as an option (I tried Large_Integer, and Varchar as well).

How could the variable name not be recognized?

I have been looking for an answer for hours. I am just thinking about not including an audit dimension if this keeps up.

Thanks,

Ivan

[ivan77]

Some more information.

I just noticed that parentauditkey is setup as a parent package as well, so I did that (as per what I saw in the Kimball SSIS package).  I also changed the logging settings for the table to be the same as the example package.  

I am now getting a new error:

Warning: Configuration from a parent variable "AuditKey" did not occur because there was no parent variable collection.

[Execute SQL Task] Error: Executing the query "INSERT INTO [DimAudit]
(ParentAuditKey
..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


The way that these variable are working is beyond me.  Does anyone know how the parent varialbe collection error would be fixed?  Also, a resource to understand how the variable and variable logging work in ssis would be great.  

Thanks,

Ivan

[Mike Honey]

Hi Ivan,

This is probably off-topic for this forum. I suggest you repost it here:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlintegrationservices

Mike

[ivan77]

Thanks for the great resource Mike.

Have a good day,

Ivan

[Sponsored content]