Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmdichos
Starting Member
4 Posts |
 Posted - 2010-09-27 : 08:32:47
|
| 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 AccessEmployeeId (PK, AutoNumber)FirstName (text)LastName (text)Table2: MS SQL 2005EmployeeId (PK, varchar,not null) < not IDENTITYFirstName (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. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-27 : 18:14:50
|
| What is the definition of your EmployeeId in the SQL Table? What do you mean by assining new keys? How are you handling that for regular inserts? Adding new keys would be either auto generating via identity columns or you writing some logic to create new keys such as a concatenation or some business logic.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
rmdichos
Starting Member
4 Posts |
Posted - 2010-09-27 : 19:15:41
|
| it means...I will only get the firstName and LastName in Table1 then copy it to the Table2. I have to assign new keys to the inserted data.See below:EmployeeID FirstName LastName1 Rei Smith2 Dianne Lyns Andrea Marks < data from table 1 William Antrim < data from table 1Mark should have an employeeID 3 while William should have 4 and so on...Please don't ask me to download Row Transformation. We are not allowed for this case study.I tried Script Component but I just don't understand how to use it...Please help. Thanks |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-27 : 20:01:29
|
| cant you use an identity column for EmployeeId and let SQL Server handle the ID's? then you can just get the names and the IDs will be generated for you automatically...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rmdichos
Starting Member
4 Posts |
Posted - 2010-09-27 : 20:53:30
|
| nope... this is a case study... and I can't change the constraints to Identity...they purposely made it NOT IDENTITY so we can solve it in some other ways without changing the table constraints... |
|
|
|
rmdichos
Starting Member
4 Posts |
Posted - 2010-09-27 : 22:32:00
|
| 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. |
|
|
|
|
|
|
|
|
Add New keys to a table with no identity