Sql Query Task in SSIS

Question

I have added a Execute Sql Task in my Project.I have added a Sql query in it

Insert into M1
select * from M4

But the problem is M1 table is in AAA database & M4 table is in DDD Database.

It is showing some error...?

Answer 1

you can use a Data Flow Task. add a OLE DB Source and a OLE DB Destination. Then configure source and destination as required. Take a look at here

Answer 2

If both databases are on the same server then fully qualify the table names:

insert into AAA.dbo.M1 (col1, col2, ...)
select col1, col2, ...
from DDD.dbo.M4

Of course, if your objects are not in the dbo schema then you need to put the correct one. You should never use SELECT * by the way, it can lead to problems if you ever change the table structure (or someone else does). Instead, always specify the column names.

An alternative would be to use a data flow to copy the data, but that's probably unnecessary here.