Currently, I am working on Data Migration, old legacy (Huge Oracle Data) System Data to SQL Server 2012 for Multiple Languages through XML/Excel file but I have concerns which file format need to prefer to Migrate the Data into SQL Server.
Could you please suggest the limitations and difficulties for each Excel/XML Formats.
Appreciate your suggestions.
By data migration using Excel/XML, if you meant that you are extracting the data into XML files or Excel files and importing that data into your SQL Server, the first thing that comes to mind is that it might be easier to use SSIS to do the data transfer.
As for limitations, one thing to consider is whether you will lose precision for numeric data types because when you write to XML or Excel, you are asking the data to be converted to a string and then written. Then when you import the data into SQL Server, you are converting it back to a numeric type.
Might be, we will not use the SSIS tool to do the Data Migration as we need to massage the data in between.
Query
Is't possible to Export the huge data via XML/Excel file in the SSIS?
Approach
We will be developing our own Own Console/Service application to read and export the XML/Excel Data to SQL Server 2012.
My concerns, before requesting the Data in a specific format from the client, I want to make sure below points will not impact in Data Migration.
Appreciate your help.
Is Oracle still online? It is easy to set up a linked server from SQL Server to Oracle, Then you can move the data as easy as
insert into
select from
Although possible - I wouldn't recommend this approach. Doing this - you have no control over the batch or commit sizes and could end up bloating the transaction log to the point it fills the drive. This will cause the insert to rollback - and that could end up taking a very long time.
Using SSIS you can control the batch and commit sizes on the OLEDB Destination. Also, using SSIS you can connect directly to Oracle with a destination directly to SQL Server and avoid the whole XML/Excel file extract and load. You still have to worry about data type conversions - but you still have those issues with files.
If you cannot utilize direct database connections - and must rely on a file - then please don't use XML or Excel. A simple CSV file will be much better and you won't be limited in the number of rows (Excel has a limit) - or be processing huge files (XML has a very large overhead).
The only way to insure you have the right data types - is to make sure you identify the appropriate data types for each column. If the source system (Oracle) is using decimal - then map to decimal in SQL Server with the same precision. If the source system is using varchar2 - make sure you map to the varchar in SQL Server with the same length.
To manage performance - SSIS will help because you can control the batch and commit sizes. Using a reasonable batch and commit size will help. If you import 1,000,000 rows all at once - then SQL Server has to commit all of those rows and that will take much longer than committing 5 batches of 200,000 rows each.
I'd use SSIS too, but it is easy to control the batch and commit sizes with the other approach. for simple scenarios:
insert into ...
select top (n) from ...
order by ...
in a while loop, would do it. either commit once a loop or have an outer loop doing the commits. Actually not that different to what SSIS would do.
Anyway the OP said they will not be using SSIS