Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2008 Package design Expand / Collapse
Author
Message
Posted Sunday, April 14, 2013 10:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:02 AM
Points: 63, Visits: 562
Hi All,

I have a requirement where i need to copy around 70 tables from oracle database to Sql server 2008 and further this total no of tables may increase to 300.

I thought of implementing it dynamically using ADO.NET in script task,instead of having 70 data flow tasks. As a proof of concept i have developed a package where i'm copying data from one sql server to another table in different database. Below is the code for the same. I have taken source and destination for sql server for this POC.

public void Main()
{
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;

string Src_connectionString =@"Data Source=XXX;Initial Catalog=Test;Integrated Security=True";
string Dst_connectionString = @"Data Source=XXX;Initial Catalog=Movies;Integrated Security=True";


using (SqlConnection sourceConnection =
new SqlConnection(Src_connectionString))
{
SqlCommand myCommand =
new SqlCommand("SELECT * FROM Customers_1", sourceConnection);
sourceConnection.Open();
IDataReader reader = myCommand.ExecuteReader();

using (SqlConnection destinationConnection =
new SqlConnection(Dst_connectionString))
{
destinationConnection.Open();

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "Customers_Dest";
bulkCopy.WriteToServer(reader);
}
}

reader.Close();

}
}

Here i'm handling 1 source table and both source and target are having same table structure, going forward i'll have a control table where i'll store all 70 table names and there source connection and queries and also its corresponding destination table

Please suggest/advice to go with this design or is there any alternative approach to handle the requirement.

Thanks,
Sam
Post #1442112
Posted Monday, April 15, 2013 11:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 18, 2013 12:37 AM
Points: 33, Visits: 68
Hello sam 55243,

Instead of writing code for this problem, I advice you to use SSMA (SQL Server Migration Assistant)

This is a free tool provided by Microsoft for Migration purpose from various database servers to MS SQL Server

You can find much information on this link

http://blogs.msdn.com/b/ssma/archive/2012/01/31/microsoft-sql-server-migration-assistant-ssma-5-2-is-now-available.aspx

Hope this will be helpful to you

Thanks.....
Post #1442579
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse