Add New keys to a table with no identityAdd New keys to a table with no identity

Question

Add New keys to a table with no identityAdd New keys to a table with no identity

rmdichos_29

SSC Rookie

Points: 27
More actions
September 27, 2010 at 6:32 am

#227080

The Case:
Transfer Employee data from Access Database to HealthCareVendor(HCV) DB Employee table.
- Create New Keys for the EmployeeId
- Transfer FirstName, LastName to Employee table.
Table1: MS Access
EmployeeId (PK, AutoNumber)
FirstName (text)
LastName (text)
Table2: MS SQL 2005
EmployeeId (PK, varchar,not null) < not IDENTITY
FirstName (varchar)
LastName (varchar)
The Problem:
I have to insert the FirstName and LastName ONLY from Table1 to Table2 and then assign New Keys to the inserted Names.
My solution:
(Data Flow) Add Source > Data Conversion (since there are some issues about datatypes) Add Destination.
Error:
[OLE DB Destination [73]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'EmployeeID', table 'HealthCareVendor.dbo.Employee'; column does not allow nulls. INSERT fails.".
Question:
I know what the problem is but being a newbie, I don't know how to solve or what to add in my data flow so I can add new keys for the inserted Names.
Please Help. Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

rmdichos_29 (9/27/2010)
The Case:
Error:
[OLE DB Destination [73]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'EmployeeID', table 'HealthCareVendor.dbo.Employee'; column does not allow nulls. INSERT fails.".

this is becasue you have null value in your primary key column at source.

check your source data

Raunak J

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 2

Raunak Jhawar (9/27/2010)
rmdichos_29 (9/27/2010)
The Case:
Error:
[OLE DB Destination [73]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'EmployeeID', table 'HealthCareVendor.dbo.Employee'; column does not allow nulls. INSERT fails.".
this is becasue you have null value in your primary key column at source.
check your source data

Unfortunately the OP said that he must generate new keys for the destination.

What you can do is the following:

at the start of the package, read the maximum key value of your destination table (in your first run this is nothing of course, so you should set it to 0). Store this value in a variable.

Read your data and store it in a temp/staging table. Then read from this temp/staging table with a SQL statement and add a column that contains the row_number function. Add your variable value to each row number. Then store the results in your final destination table.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rmdichos_29 SSC Rookie Points: 27 More actions · Answer 3

the thing is....

I don't know what to use... i tried script component based on what i reseached in some sites but i don't know how to get the MAX ID of the destination table...

can someone give me a step by step or sample procedure...?

NOTE: please don't tell me to download ROW Transformation... in our case study, we are not allowed to do that...

thanks anyway, for you suggestions... 🙂

rmdichos_29 SSC Rookie Points: 27 More actions · Answer 4

Problem solved.

For the sake of those who encounter the same problem as mine, here's what we did:

Control Flow tab Steps:

- We create a 2 Data Source Connections. Then use it in the Connection Manager.

- To get the MaxID of the EmployeeID in Table2, create an SQL statement (using Execute SQL Task). We declare a variable that will hold the MAXID.

- We create a 'Data Flow Task' that will get the data in table1, convert datatypes, copy it in the destination table and create new EmployeeIDs for the inserted data.

Data Flow tab Steps:

- Add OLE DB Source and configure it to get the data in Table1

- We use "Data Conversion" to convert Access Datatypes to datatypes that are acceptable in the destination table.

- Since the EmployeeID in Table Destination doesn't have Identity, it won't automatically increment once we insert the Names. We use Script Component (Transformation Type) to code the increment.

- Configure Script Component and add the necessary codes for increment. The count will start based on the MAXID that we got from the earlier SQL in the Control Flow.

- Add the 'OLE DB Destination' and configure.

UMG Developer SSChampion Points: 13482 More actions · Answer 5

That sounds like it should work as long as nobody else INSERTs rows while your package is running.