loading data from multiple tables to multiple tables in other database?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » loading data from multiple tables to multiple...

loading data from multiple tables to multiple tables in other database?

Rate Topic

Display Mode

Topic Options

Author

Message

mpradeep23

Posted Wednesday, February 06, 2013 5:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 45, Visits: 167

hi

ssis package

loading data from multiple tables to multiple tables in other database

idea s

thanks

pradeep

Post #1416443

Steven Willis

Posted Wednesday, February 06, 2013 8:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 283, Visits: 1,236

It's not SSIS, but from SSMS you can right-click on the database, select Tasks | Generate Scripts. This utility will let you select whatever objects you want to copy including data (under Advanced option) and then it will produce a script to file or to the query window. You can also direct the generated script to a remote server by using the "Publish to a Web Service" option.

If you must use SSIS, then you can use Tasks | Export Data and save the export procedure in a file readable in SSIS which you can then run from SSIS as needed.

Post #1416548

mpradeep23

Posted Wednesday, February 06, 2013 8:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 45, Visits: 167

i want to move data increment every 3 min

Post #1416557

Steven Willis

Posted Wednesday, February 06, 2013 10:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 283, Visits: 1,236

mpradeep23 (2/6/2013)

i want to move data increment every 3 min

Then one option would be to create the export via Tasks | Export Data and save it as a package you can run from SSIS.

If you can create a linked server you could write a procedure that writes directly to the target server (IMHO the easiest option).

Or if you have the proper credentials you can write a procedure to use OPENDATASOURCE and write directly to the target tables. If you use OPENDATASOURCE, I would recommend that the procedure using it be saved WITH ENCRYPTION because of the plain text connection string.

Then with any of these options you can create a scheduled job to run the procedure.

INSERT INTO 
OPENDATASOURCE(
     'SQLOLEDB'
    ,'Data Source=000.000.0.000;user id=TargetUserID;password=TargetPassword'
    ).TargetServer.dbo.TargetTable    
    (Col1
    ,Col2
    ,Col3)
VALUES
    (
    ,@Col1Val
    ,@Col2Val
    ,@Col3Val)

Post #1416640

opc.three

Posted Thursday, February 07, 2013 7:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 6,693, Visits: 11,706

It sounds like you are planning to implement something in SSIS already provided in SQL Server. Have you looked into SQL Server Replication?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1417081

« Prev Topic | Next Topic »

Permissions