Found a way to achieve this. It is a hack way of doing it but it gets the job done.

For anybody interested on how I am doing this:

1. Created a new script task

2. In the Edit Script I load the source and destination server name and credentials

3. I also load the SQL query from .sql file and destination table name.

After I have all of the above, I am using SQLBulkCopy class of System.Data.SqlClient to move data between the two servers. This is ideal because this step is moving data to a staging area on the second server and I do not need to perform any transformations or apply any heavy business logic.

Thanks