June 7, 2010 at 5:06 pm
I'm copying data from Oracle to SQL Server 2008 using a linked server in SSIS. I've heard you don't need the linked server for this, but with the 64 bit issue and my 32 bit development environment, it seems like the best option for now (although I'm open to alternatives).
Anyway, I'm able to get the data using a SQL query on the linked server as a data source (is it ok to use the destination server as the connection for that? It seems odd, but a connection is needed and it seems to work).
The main issue is that I need to combine 2 records into 1 for each person. To explain, the data that I'm getting through my SQL query data source on the linked server returns:
person_idmoney_typeavailable_balance
099480P20.64
099480E43.54
099576P5.73
099576E0.00
099585P147.17
099585E5.00
And, in my SSIS package (trying to work this out in a data flow) it needs to be copied into this:
PersonNoEarningsBalancePrivateBalance
09948043.54 20.64
0995760.00 5.73
0995855.00 147.17
I've looked around, but can't seem to find something that does this using the SSIS transformations. The closest thing I found was a merge join, but I'm not sure that is intended for a single source.
Any help would be greatly appreciated.
June 7, 2010 at 6:50 pm
Never mind. I ended up going retro and using a couple of views (using case statement for the merge) and a stored procedure which I'll schedule in stead.
Works fine, and I suppose I can get some success/fail from the scheduling. Not brilliant error reporting but it will do.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply