How to use multiple connections in SQL script task ssis

If the databases are not on the same instance of SQL Server then set up a Linked server - see Create Linked Servers - SQL Server | Microsoft Docs[^]
Then just use fully qualified table names including the server e.g.

select [column1],[column2] -- etc
into [server2].database2.dbo.table2 
from database1.dbo.table1

If they are on the same instance just use the database names

select [column1],[column2] -- etc
into database2.dbo.table2 
from database1.dbo.table1

There are a couple of alternatives - see this article https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/[^]