The package has one step where I need to pull 14 queries as the Source location and move the data into a single table.
The table had to be created first.
And permissions needed to be added up front.
In order to truncate the table with the User running the package in SSIS.
Once permissions were granted, next step is to create all 14 Data Sources within the Data Flow task.
The next step is to add a Union All to the package and point all 14 Data Sources to this component.
After that, you add an Ole DB Destination to move the data to final destination.
Here's what mine looked like:
Now when this runs, it appears nothing happens.
The output screen shows: SSIS.PipleLine: Pre-Execute phase is beginning.
Long Duration Pre-Execute Phase
One suggestion looked promising which was "ValidateExternalMetadata is set to false"
Another suggestion was that there is no issue, the 14 queries are running at the same time which could possibly take 10+ minutes, and the lack of Yellow/Green indicators is that the system is pegged memory wise so just need to be patient.
So I moved 2 of the 14 components to a separate Data Flow task to look under the hood, got the following error:
[OLE DB Destination ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The INSERT permission was denied on the object 'FuzzySFSC', database 'Report', schema 'dbo'.".
Added permissions and who-la! We got successful package!
So next step, I split the 14 Data Source connections into 4 Data Source steps (a+b+c+d) to produce a daisy chain of events to reduce server costs.
And that's my intro to using SSIS Union All~!