package error in ssis but not bids

I created an export to access package using the wizard (because why reinvent the wheel) using a view (i later found out views take forever to run) as the data source. i did some research and found out that it runs faster if i just take the query that builds the view and use that as the source. This runs fast in bids 2008 (3 seconds vs almost 30 minutes). So i put this on the job agent for sql server so we have an automated export of data, run it to test it, and it fails.

Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:43:31 AM Error: 2017-10-31 12:04:09.50 Code: 0xC0202009 Source: Data Flow Task 1 Destination - Etching_LookupView_Sink1Temperature [109] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E03. End Error Error: 2017-10-31 12:04:09.50 Code: 0xC0047022 Source: Data Flow Task 1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Etching_LookupView_Sink1Temperature" (109) failed with error code 0xC0202009 while processing input "Destination Input" (122). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:43:31 AM Finished: 12:04:09 PM Elapsed: 1237.81 seconds. The package execution failed. The step failed.

From the research i have done on this error code, it talks about casting, but i am not casting anything.

this is the query i was provided for the view.

SELECT     dbo.Etching_Trace_Data.Etch_Date, dbo.Etching_Trace_Data.EtchLot, dbo.Etching_Trace_Data.Part_Number, dbo.Etching_Trace_Data.Lot_Number, dbo.Etching_Trace_Data.Leadbond_Lot, 
                      dbo.Etching_Trace_Data.Acid_Batch, dbo.Etching_Trace_Data.Operator_ID, dbo.Etching_Trace_Data.WorkOrder, dbo.Etching_Trace_Data.Etch_Sink, dbo.Etching_Trace_Data.Num_Diodes, 
                      dbo.Etching_Trace_Data.Etch_Number, AVG(dbo.Etching_Meas_Runtime.[210Temperature]) AS AvgOf210Temperature, MAX(dbo.Etching_Meas_Runtime.[210Temperature]) AS MaxOf210Temperature,
                       MIN(dbo.Etching_Meas_Runtime.[210Temperature]) AS MinOf210Temperature, AVG(dbo.Etching_Meas_Runtime.NitricTemperature) AS AvgOfNitricTemperature, 
                      MAX(dbo.Etching_Meas_Runtime.NitricTemperature) AS MaxOfNitricTemperature, MIN(dbo.Etching_Meas_Runtime.NitricTemperature) AS MinOfNitricTemperature, 
                      AVG(dbo.Etching_Meas_Runtime.LeftRinseWaterTemperature) AS AvgOfLeftRinseWaterTemperature, MAX(dbo.Etching_Meas_Runtime.LeftRinseWaterTemperature) 
                      AS MaxOfLeftRinseWaterTemperature, MIN(dbo.Etching_Meas_Runtime.LeftRinseWaterTemperature) AS MinOfLeftRinseWaterTemperature, 
                      AVG(dbo.Etching_Meas_Runtime.RightRinseWaterTemperature) AS AvgOfRightRinseWaterTemperature, MAX(dbo.Etching_Meas_Runtime.RightRinseWaterTemperature) 
                      AS MaxOfRightRinseWaterTemperature, MIN(dbo.Etching_Meas_Runtime.RightRinseWaterTemperature) AS MinOfRightRinseWaterTemperature, 
                      AVG(dbo.Etching_Meas_Runtime.RightRinseWaterResistivity) AS AvgOfRightRinseWaterResistivity, dbo.Etching_LookupView_LeadbondLots.RecipeName, 
                      dbo.Etching_Trace_Data.Etch_Time_210_1, dbo.Etching_Trace_Data.Etch_Time_210_2, dbo.Etching_Trace_Data.Etch_Time_210_3, dbo.Etching_Trace_Data.Etch_Time_210_4, 
                      dbo.Etching_Trace_Data.Etch_Time_Nitric_1, dbo.Etching_Trace_Data.Etch_Time_Nitric_2
FROM         dbo.Etching_Trace_Data INNER JOIN
                      dbo.Etching_Meas_Runtime ON dbo.Etching_Trace_Data.Leadbond_Lot = dbo.Etching_Meas_Runtime.LotNumber INNER JOIN
                      dbo.Etching_LookupView_LeadbondLots ON dbo.Etching_Trace_Data.Leadbond_Lot = dbo.Etching_LookupView_LeadbondLots.Leadbond_lot
WHERE     (dbo.Etching_Trace_Data.Etch_Date > '2017-08-01 00:00:00')
GROUP BY dbo.Etching_Trace_Data.Etch_Date, dbo.Etching_Trace_Data.EtchLot, dbo.Etching_Trace_Data.Part_Number, dbo.Etching_Trace_Data.Lot_Number, dbo.Etching_Trace_Data.Leadbond_Lot, 
                      dbo.Etching_Trace_Data.Acid_Batch, dbo.Etching_Trace_Data.Operator_ID, dbo.Etching_Trace_Data.WorkOrder, dbo.Etching_Trace_Data.Etch_Sink, dbo.Etching_Trace_Data.Num_Diodes, 
                      dbo.Etching_Trace_Data.Etch_Number, dbo.Etching_Trace_Data.Etch_Time_210_1, dbo.Etching_Trace_Data.Etch_Time_210_2, dbo.Etching_Trace_Data.Etch_Time_210_3, 
                      dbo.Etching_Trace_Data.Etch_Time_210_4, dbo.Etching_Trace_Data.Etch_Time_Nitric_1, dbo.Etching_Trace_Data.Etch_Time_Nitric_2, dbo.Etching_LookupView_LeadbondLots.RecipeName

I ended up "fixing" my problem by rerunning the export wizard and using the query for the source instead of the view, and it executed just fine. my guess is i mucked something up while in BIDS that i wasn't aware of.

The importance of the query vs the view is that when SSIS tries to validate the view there is a major slowdown that causes the package to fail when run through the agent, but when you provide the query instead, it validates much faster.

The details are here:

https://connect.microsoft.com/SQLServer/feedback/details/258901/ssis-views-as-data-source-very-poor-performance-or-ssis-hangs

with the pertinent portion being

Posted by Microsoft on 4/28/2008 at 2:45 PM This is a know issue and the result of the current design.

There are 2 ways to pull data from a view in OLE DB source:

1. Use "Table or view" access method

2. Use "SQL command" access method, and enter a query "select * from ***"

A different execution plan is generated in the two approaches.

The one used in the former is not as efficient as the latter.

If you hit the performance issue when going with the first approach, you can switch to the second approach as a work around.

We have also blogged this issue - >http://blogs.msdn.com/sqlperf/archive/2007/04/29/set-up-ole-db-source-to-read-from-view-efficiently.aspx.

Since this is a 'By Design' item and we believe that there is a work around we will not be providing any change at this time. As a result we are closing the case associated with your submission. If you disagree, please feel free to re-submit.

We appreciate your time, effort, and support of SSIS.