February 6, 2013 at 5:47 am
hi
ssis package
loading data from multiple tables to multiple tables in other database
idea s
thanks
pradeep
February 6, 2013 at 8:34 am
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.
February 6, 2013 at 8:47 am
i want to move data increment every 3 min
February 6, 2013 at 10:46 am
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)
February 7, 2013 at 7:48 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply