Deployment folks do not want me to use a sproc because it involves a linked server. I must supply the full query in the SSIS package and get around the character limit of the OLE DB data flow task, without resorting to a sproc in the Execute SQL Task. (because the called sproc has to reside on one server or another therefore requires a linked server name, which deployment team does not want to supply)

Will someone kindly advise on how to build an etl that will pull data from one server and dump it into another, with the constraint that I can't use a sproc and my query is too large to put into the OLE DB Data Flow Task?