August 9, 2009 at 11:40 pm
I am having a table in SQL server 2005 database (64-bit enterprise edition & SP3). I am having a query that extracts some columns and their values from SQL Server that needs to be transfered to the table in ORACLE database.
Can any one please assist me with this?
Thanks.
August 10, 2009 at 12:16 pm
Is it a one time task or a recurrent one?
How many rows?
Either way, a SSIS package woud do the trick.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 10, 2009 at 9:43 pm
It is a recurrent process.
Initially it has 960 records to be transfered.
If using SSIS package can we get the new transactions logged to SQL server to the ORACLE server.
-Thansk
September 1, 2010 at 3:51 am
Hi
Can you please give me some tips about posting the data from SQL Server 2005 to Oracle?
I need to populate a table in Oracle on an hourly basis.
I need some info on how can we connect to oracle from SQL Server 2005.
Is SSIS Package the only way to do it or can we create a Stored Procedure?
Many Thanks.
September 1, 2010 at 4:18 am
Other than SSIS you could use linked server to transfer data.
Since you need this on hourly basis i suggest SSIS would be the better option.
Make sure Oracle is installed in the machine and try connecting the oracle db to test whether the connection works fine.
January 18, 2011 at 6:18 am
hii
What is the final solution for that.
I want same thing.same way data should be transferred every hour.
January 18, 2011 at 6:33 am
Creating SSIS package will work by which the data can be loaded initially. Also by SQL server agent the same package can be scheduled as a job to run for every hour.
July 1, 2011 at 1:24 pm
I'm no SSIS expert, but... Some simple tasks for importing into SQL Server are not simple when exporting to other databases. I understand that just helps people move to other DBs, but I just need to push some data out to Oracle on a daily basis.
SSIS works great for textual data, numerics... etc.. But you run into trouble with Bools, and.. There is plenty of information on how to overcome this though...
But.. it doesn't allow me to insert DT_DATE or DT_DBTIMESTAMP data into Oracle 10g... and I haven't found any good information on how to overcome this. I'm sure it's easy, but when it isn't documented, it doesn't matter how easy it is.
Example: I have a package which is using ADO.NET connectors on both the source and destination data flows.
The data/column giving me trouble is stored as yyyy-mm-dd in SQL Server, and Oracle is storing it as mm-dd-yyyy. I've extracted the data from SQL Server so the data is formatted as mm-dd-yyyy using a query. It didn't work. I got the exact same error that I got from using it's native format (yyyy-mm-dd).
When I wrote the data into a Flat file, the date formats have reverted back to yyyy-mm-dd (Being passed in the DT_DATE format). So there is some data conversion occuring which there is no easy way to shut off, or more importantly configure...
Any suggestions? Should I pass it as a DT_TEXT? Can I control the format of DT_DATE somehow? Help..
July 1, 2011 at 1:38 pm
Chad Slagle (7/1/2011)
The data/column giving me trouble is stored as yyyy-mm-dd in SQL Server, and Oracle is storing it as mm-dd-yyyy.
Not reallly. Oracle stores DATE data type data in a fixed-length, seven bytes format corresponding to century, year, month, day, hour, minute, and second.
How Oracle shows the data depends on client side setup and most importantly on the mask you use... try this, log into an Oracle database and issue:
select to_char('mm/dd/yyyy', sysdate) from dual;
select to_char('dd/mm/yyyy', sysdate) from dual;
SYSDATE - which is Oracle's version of date() - will be displayed as per the to_char() mask.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 1:46 pm
July 1, 2011 at 2:12 pm
Jayanth_Kurup (7/1/2011)
Migration of data to oracle via SSIS is probably the best approach but you can also setup replication to Oracle.
Not sure if I would tackle a replication schema for a one thousand rows data transfer.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 2:50 pm
I hope I'm not being rude, but that doesn't really answer my question. I understand Oracle very well, as well as the formatting of dates, and the functions available on that platform.
My lack of understanding is how I can use a query to extract some data from the ADO.Net connection, I use this to reformat the dates:
IIF(ISNULL(<table>.<Column with Date type>), NULL, CDATE(FORMAT(<table>.<Column with Date type>, 'dd-mmm-yy')))
If you are sharp, you recognized this is a query I did from ACCESS as a test of this issue that is also happening on that platform, moving to Oracle.
This converts the date format nicely when I use this query in SSMS. And when test the query in the Source Data Flow ADO.Net connection (using preview, and the query browser), the dataset is properly formatted. I have also made sure that the SSIS data types match throughout the process (DT_DBTIMESTAMP), which is configured automagically when I add the connection...
Oracle is using WE8MSWIN1252, and SSIS is using the 1252 code page on each field. So that is matched up. Everything is matching..
So I send the dataset to Oracle, it complains about the format. When I put a data viewer on, the dates are displaying as "yyyy/mm/dd hh:mm:ss" which is the format for DT_DBTIMESTAMP. There is reformatting occuring, that i need absolute control over and would like to know how I can do that (<--- My question) I would love to run a Oracle function to recast the data into the right format, but I don't have that option available in either the ADO.Net or OLE DB Destination Data Source data flows.
I am pretty sure I'm the problem, and I have not picked the right Integration Services data-type. So far, I've tried DT_DATE, DT_DBTIMESTAMP, DT_DBTIMESTAMP2, I've even tried using DT_NTEXT and DT_TEXT, DT_WSTR, but the Destination Data Flow will not allow me to change the column in questions from DT_DBTIMESTAMP to DT_WSTR.. and so on. Or is this just how it works? Data will always be converted to a "SQL Server" friendly data format?
I want control over the Integration Services Data Types.. I don't want them rearranging my data!! Kind of defeats the purpose.. Help!!:w00t:
July 2, 2011 at 8:03 am
PaulB-TheOneAndOnly (7/1/2011)
Jayanth_Kurup (7/1/2011)
Migration of data to oracle via SSIS is probably the best approach but you can also setup replication to Oracle.Not sure if I would tackle a replication schema for a one thousand rows data transfer.
It is a recurrent process.
Initially it has 960 records to be transfered.
I figured since its repeatable and seems like ETL process; maybe replication is an option.
Besides 1k records per transaction doesn't seem to be whats happening , I guess the initial load is 1k and then you have deltas in smaller batchs which can be handled by transactional replication. I know its not a big deal for sql to sql re but maybe for sql to ora it could be
July 2, 2011 at 11:52 am
Chad Slagle (7/1/2011)
I hope I'm not being rude, but that doesn't really answer my question. I understand Oracle very well, as well as the formatting of dates, and the functions available on that platform.
Don't worry, you are not being rude but in a previous post you also say - let me quote: "The data/column giving me trouble is stored as yyyy-mm-dd in SQL Server, and Oracle is storing it as mm-dd-yyyy." which is not even close to how Oracle stores it. Please check my previous post for details on this 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 6, 2011 at 9:40 pm
I've run into similar issues using a file-based transfer. SQL Server produces a file of data, and we use SQL*Loader to get that up into Oracle.
To get around the date format expectations of SQL*Loader, we have to create a view that formats the date into a string of the right format for Oracle.
I would expect SSIS to be able to avoid that issue, as it is transferring a date as a date not a string. But if you are having issues, why not convert the date to a string in the format Oracle is expecting (which you can do either in the SQL statement extracting the data from SQL Server, or inside the SSIS package.)
Seems odd that you would have to do that though.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply