Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS DATAMIGRATION ON DIFFERENT SERVERS

Answered SSIS DATAMIGRATION ON DIFFERENT SERVERS

  • Wednesday, February 06, 2013 4:01 AM
     
     
    Sign In to Vote
    0

    Hi All,

    Am new to SSIS...i created ADO NET Source, it takes value from one table on SERVER A.Then i have derived column control to add one extra column, finally i have ADO NET Destination it insert values on Server B. Till now every thing works fine but according to my requirement i want to change my source(ADO NET Source) table name dynamically, and also destination table name dynamically,derived column expression also dynamically. Can any guide me on this.

    Thanks SABARINATHAN87

     

All Replies

  • Wednesday, February 06, 2013 4:26 AM
     
     
    Sign In to Vote
    0

    Why do you need this? Is it some thing you have to move all the tables data from Server A to Server B?

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Wednesday, February 06, 2013 5:04 AM
     
     
    Sign In to Vote
    0

    Hi Eswararao,

    I dont want to move all tables to server A to server B...i want only few tables to move..

    Thanks SABARINATHAN87


     
  • Wednesday, February 06, 2013 5:12 AM
     
     
    Sign In to Vote
    0

    If the table structure are same then yes If not you have to do it separate Data flow task for each of the table.

    http://stackoverflow.com/questions/14503478/mutiple-tables-import-using-single-dataflow-in-ssis

    And also creating dynamic derived column is not possible in SSIS.

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Wednesday, February 06, 2013 5:16 AM
     
     
    Sign In to Vote
    0

    Hi Eswararao,

    I have 15 tables on server A and 5 on server B...the server B 5 tables have same structure as on server A. My question is now my package executes for 1 table alone how can i do it for other tables also.

    Thanks SABARINATHAN87

     
  • Wednesday, February 06, 2013 5:33 AM
     
     
    Sign In to Vote
    0
    I think you can use import/export wizard to do this task. you can also create a package at the end of the wizard. So, you can execute it in future.

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

     
  • Wednesday, February 06, 2013 9:09 AM
     
     
    Sign In to Vote
    0

    I see your requiremnet cannot be done uisng one Data flow task there should be no of data flow tasks as per your requirement

    Let you want to load 5 tables then you need 5 data flow tasks.

    http://social.msdn.microsoft.com/Forums/eu/sqlintegrationservices/thread/ab2e5536-a4ec-4a64-b385-319c9f6476bd

    Give me more details of the tables i can help you out.

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Thursday, February 07, 2013 8:59 AM
     
     
    Sign In to Vote
    0

    Hi Easwararao..

    I have partially get output for my problem but in middle the requirement gets changed..

    While moving data from server A to server B..if error comes due to foreign key constraints or any, those data need to be moved to error table that also done, after moving that i need to take count of the error record and compare with Server A total records then calculate percentage for that.. if it is above 30% we need to stop our process...

    I attached my designers...


    Thanks SABARINATHAN87

     
  • Thursday, February 07, 2013 9:14 AM
     
      Has Code
    Sign In to Vote
    0
     
    --ExecuteSQLTask1
select COUNT(*) from TableA
--ExecuteSQLTask2
Select COUNT(*) from TableA where foreignkey in (select foreignkey from LookupTable)

    Use two execute sql task one for total count other for matching FK Count.

    Then use the presedence constarint to compare and based on the value either process or don't process this is will be initial step.

    Precedence constraint exp:

    @[User::vs_Count2]>=(0.3)*@[User::vs_Count1]

    Let me know if you have any other questions

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Thursday, February 07, 2013 9:52 AM
     
     
    Sign In to Vote
    0

    Hi Easwararao..

    I put two sql task both will count no of records on two different tables..

    after that i try to add precedence constraint....But expression tab is not enabled for me....plz provide ur mail id i will send even more details to u....


    Thanks SABARINATHAN87

     
  • Thursday, February 07, 2013 10:55 AM
     
     
    Sign In to Vote
    0

    Double click on the green tab it will open an presedence constraint tab

    http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspx

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Thursday, February 07, 2013 11:08 AM
     
     
    Sign In to Vote
    0

    Hi Easwarao ,

    Plz give your mail ID...i get the requirement right now....every thing is changed. I will send every thing in details

    Thanks SABARINATHAN87

     
  • Thursday, February 07, 2013 12:11 PM
     
     
    Sign In to Vote
    0

    give urs..

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

     
  • Thursday, February 07, 2013 2:06 PM
     
     
    Sign In to Vote
    0

    Hi Eswararao,

    sabarinathanitt87@gmail.com.... plz response for this....

    Thanks SABARINATHAN87

     
  • Wednesday, February 13, 2013 8:33 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Sabarinathan87,

    I suggest you can use Sequence containers to achieve your target, If the value is above 30%, the subsequent rollback both reverses the database changes and puts the message back on the queue. Please refer to: http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

    Thanks,
    Eileen

    Eileen Zhao
    TechNet Community Support