I've been having a painful time with this scenario: building an SSIS package, but to verify results, I've been asked to pull from production copy data sources, which are on another server. After much difficulty I was able to tweak the package to pull from production copy sources and to load the records into the Int server table, and it runs successfully, but I'm not seeing any records in the Int server's table. So I thought maybe a workaround would be to try to import the query's result records that I had in the package, into an .xlsx or a .txt file and then import via the SQL Server Import and Export Wizard, but one of the columns keeps erroring out, even after I tried changing the format multiple times. So now, I'm attempting to import the results of my query into a linked server, but not having much luck...my query populates a temp table, from which I'd like to load the records into the linked server that already exists. Is this even possible to do:
INSERT INTO ##FSEDMEnrollData
SELECT * INTO [ServerName].[DatabaseName].[dbo].[StgFSEMonthlyRevenue]
The above server has already been set up as a linked server on the ProdCopy server, per the db admin. Any advice on how to do this would be greatly appreciated. Thanks in advance!