January 11, 2011 at 7:05 am
Can anyone please fwd any information related to datamigration from sql 2005 to Oracle using SSIS. With DTS its pretty straightfwd but SSIS works little different so looking for any links that could provided me stepby step intructions how to move data from one database to the other. ANy help on this will be greatly appreciated. Thanks
January 11, 2011 at 7:46 am
This should get you started:
http://msdn.microsoft.com/en-US/library/ms137712(v=SQL.90).aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 11, 2011 at 12:38 pm
Using Oracle SQL Developer is the best way to migrate to Oracle.
If you want to use SSIS then you need to do the data type conversion between SQL Server and Oracle using data
http://download.oracle.com/docs/html/B10544_01/apa.htmconversion Task.
Especially you need to worry about Varchar and Nvarchar datatypes.
Thank You,
Best Regards,
SQLBuddy
January 11, 2011 at 1:53 pm
Thanks for the response. i just learnt the migration is from oracle to oracle only using SSIS> bit strange but thats how the client prefers. Now i am trying to work out the tns entry to set up an OLEDB connection to oracle server. can anyone please help me with this. This is the details i have for the oracle env.
driverClassName - oracle.jdbc.driver.OracleDriver
url - jdbc:oracle:thin@orasvcdbsl5555:1521:namsdev
username - chktest
password - chektest_dev
January 11, 2011 at 2:32 pm
Add the Oracle Server to the TNSNamesOra file and then use the Oracle Provider for OLEDB Provider and then create a connection manager to the Oracle Server and use that connection Mgr in the SSIS Pkg for the Migration.
Thank You,
Best Regads,
SQLBuddy
January 11, 2011 at 2:47 pm
Thats absolutely correct but i am not able to get the tns entry correct just with the details i have. it shows error msg as ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor
my tns entry is
<servername>=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =hostname>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <servername>)
)
)
January 11, 2011 at 3:18 pm
Try to use Fully Qualified domain names for the TNS Entry like and then try to do TNSPing to the Oracle Box
ServerName.DomainName.Com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Hostname.DomainName.Com)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = ServerName.DomainName.Com)
)
)
Try to use different combinations like
ServerName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Hostname.DomainName.Com)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = ServerName.DomainName.Com)
)
)
ServerName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Hostname)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = ServerName.DomainName.Com)
)
)
ServerName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Hostname)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = ServerName)
)
)
Thank You,
Best Regards,
SQLBuddy
January 11, 2011 at 4:38 pm
Many thanks for your input sqlbuddy. Now i have another strange problem. If i try some table from Oracle database and when i try previewing it i get error as opening a rowset for <table> failed. Check that objecst exists in the database. I tried setting properties for Dataflow task as TransactionOption = Required
IsolationLevel = RepeatableRead instead of the default which was Transactionoption -Supported and Isloationlevel- serializable. Does anything else need to be changed? on the other hand most tables work fine without any problem. i mean i could preview the data for most of the tables..any help will be appreciated. thx
January 12, 2011 at 11:49 am
Hi Ishaan,
Are you using Oracle provider for OLE DB or Microsoft OLE DB providerfor Oracle ?
Try to use Oracle provider for OLE DB in your connection manager.
Some times SSIS puts double quotes for the tablenames during the preview from the drop downbox.
Check to see if you are able to preview the data using the SQL Query instead of selecting the table from the drop down box
Type the sql like Select * from the TableName
Thank You,
Best Regards,
SQLBuddy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply