Problem with Slowly Changing Dimension-transformation

Hi,

I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.

This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.

I only want to update the batchid for row that have changed in the current batch.

Is it possible to do this in any way without adding the Derived Column after the SCD transformation?

Thank for any help!!

Patrick

I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.

From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.

let me know if I do not answer your question.

wenyang

|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.

Problem with Slowly Changing Dimension-transformation

Hi,

I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.

This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.

I only want to update the batchid for row that have changed in the current batch.

Is it possible to do this in any way without adding the Derived Column after the SCD transformation?

Thank for any help!!

Patrick

I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.

From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.

let me know if I do not answer your question.

wenyang

|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.sql

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

sql

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!