cancel
Showing results for 
Search instead for 
Did you mean: 

BODS Error with IDENTITY INSERT and Transactional Data Loading

Former Member
0 Kudos

Hi All,

We are facing some issues while working with SAP BODS,

Scenario: We have 4 tables in Microsoft SQL Server that contains identity Columns and they bears an Parent-Child Relationship among themselves.

We need to insert values into the tables simultaneously.

Solution we are trying to implement:

a) Trying Transactional Options for each table in the data store – but for that the preloading and post loading SQL tabs are going  disabled

                    So error facing is enable identity_insert  is set to OFF. We have everything enabled in database the user which connects the BODS is the owner of the database.

b) Trying Bulk Loading Options

                         Bulk Loading is able to insert values into tables, but then where there is a parent child relationship we are facing the error of Foreign-Key.

Please Share your views.

Thanks a lot in advance.

Regards

Joy

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

In the target table, you enable identity insert ON in post load command.

Have you tried that.

Arun

Former Member
0 Kudos

Yes I have tried that, if we use Transaction Loading then both pre-load and post load options are disabled for that.

But if we dont use transaction loading we are facing the parent child table loading problem. Foreign Key constraints are violated,

I have also tried scripting i.e enabling the identity insert before the data-flow begins.

Thanks and Regards

Joy

Former Member
0 Kudos

Howz the key generated? Auto indexing in table or you do through Key gen transform?

If it is generated at DB level then you have to load with key deleted.

Former Member
0 Kudos

Hi Arun,

Thanks for the suggestion, In the Client database already Identity Insert is set On. So we cant delete that, we need to find an way around to load data without deleting the identity column or stopping the auto identity insert  but also at the same time maintain the parent child relationship. Please share your views. If I could find an way around I will let all know, by this post.

Thanks and Regards

Joy

former_member189153
Active Participant
0 Kudos

Hi,

Since Auto Identity as a key fields, why cannot have table wise sequence execution.

Former Member

Hi Joy,

When i say delete, i dont mean delete at DB level. You have to delete at DS level.

I assume your identity insert is a auto key generation by the DBA. If thats the case import the table in the datastore, double click to open the table structure and delete the auto generated key by right clicking.

Then load the data.

Arun

Former Member
0 Kudos

Hi All,

I found out an solution, I loaded the child tables in a flat-file, and the main parent files normally using a preload and post load sql and in a next data-flow i updated the tables based on the parent values and it works fine. Please let me know if this is good solution.

Thanks and Regards

Joy