CloverETL Forum

CloverETL Engine, Designer & Server related discussion forums
It is currently Thu Jul 24, 2014 6:05 pm



Post new topic Reply to topic  [ 4 posts ] 
Author Message
PostPosted: Tue Apr 06, 2010 2:03 am 
Offline

Joined: Tue Apr 06, 2010 1:56 am
Posts: 2
Hi,

I'm evaluating Clover for a data migration project, moving data between two SQL Server 2008 databases.
As part of my testing I need to evaluate whether clover can migrate parent/child data between the two systems, whilst retaining the relationship. For my test I have a DBInputTable that reads the source parent records, and a DBOutputTable that inserts the mapped parents into the destination db. However I am getting stuck on having the DBOutputTable component return the new ids that the db has generated.

Does anyone have a solution to this problem?

Thanks i advance,

Dave Oram


Top
 Profile  
 
PostPosted: Tue Apr 06, 2010 12:40 pm 
Offline

Joined: Fri Jul 20, 2007 9:28 am
Posts: 841
Hello Dave,
if the key column is set as an IDENTITY you have to turn of generating the keys, before copying data from one database to another (phase 0 on the picture). Then copy data from one table to another (phase 1). Now you can turn off inserting the identity (phase 2). Loading new data after it, triggers the automatic key generation (phase 3). To make this graph working properly you have to uncheck "Thread-safe connection" box in Advanced properties of target db connection.
Attachment:
File comment: This graph copies data from one table to another and adds some more records to destination table.
mssqlcopy.png
mssqlcopy.png [ 163.45 KiB | Viewed 1245 times ]

_________________
Agata Vackova
Javlin a.s.
agata.vackova@javlin.eu


Top
 Profile  
 
PostPosted: Tue Apr 06, 2010 8:34 pm 
Offline

Joined: Tue Apr 06, 2010 1:56 am
Posts: 2
Thanks for that, however I realise I didn't really highlight the main issue.

In your sample, and the online help the DBOutputWriter uses the syntax:

Code:
Insert into table(col1,col2)
values ($col1, $col2)
returning $id:=auto_generated


This syntax is not acceptable for the SQL Server 2008 database I am using. I checked on wikipedia, and it appears valid for other databases such as Oracle but not SQL Server. Do you know what the equivalent SQL Server syntax is?

Cheers,
Dave


Top
 Profile  
 
PostPosted: Wed Apr 07, 2010 8:28 am 
Offline

Joined: Fri Jul 20, 2007 9:28 am
Posts: 841
Hello Dave,
the example above I've tested with the MSSQL Server 2008 and it works properly. Do you have MSSQL jdbc specific in your connection definition (jdbcSpecific=MSSQL)? Is one of the columns defined as IDENTITY? Do you get an error?

_________________
Agata Vackova
Javlin a.s.
agata.vackova@javlin.eu


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 


Who is online

Users browsing this forum: Bing [Bot], Google [Bot], Majestic-12 [Bot] and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group