Error at Data Flow Task [OLE DB Source [69]]: SSIS Error Code DTS_E

You might get the following error when using an OLE DB source in SSIS while trying to map a parameter in your SQL query:

Error at Data Flow Task [OLE DB Source [69]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occured. Error Code: 0x900004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Oh goody. I love these kinds of errors. Doesn't tell you squat about what you just ran into.  So, a developer on our team ran into a parameter mapping problem running this (well something sort of like this...we're not developing in our shop in the AdventureWorksDW database right on):

SELECT     D.CustomerKey, COUNT(1) AS CustomerCount
FROM         dbo.DimCustomer AS D INNER JOIN
      (SELECT    ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey
                , CurrencyKey, SalesTerritoryKey, SalesOrderNumber, 
                 SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice
                , ExtendedAmount , UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, 
                 TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber
                 , CustomerPONumber
        FROM          dbo.FactInternetSales
        WHERE      (ShipDateKey > ?)) AS f ON D.CustomerKey = f.CustomerKey
GROUP BY D.CustomerKey

So here's the deal. If you crack open Books Online you can see the following:

When you are use an OLE DB connection manager, you cannot use parameterized subqueries, because the OLE DB source cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlCommand property of the source.In SSIS Designer, you configure an OLE DB source by using the OLE DB Source Editor dialog box and map the parameters to variables in the Set Query Parameter dialog box.

It also thinks CTEs are subqueries as well, so don't try that either.  There are a bunch of workarounds for this, so try one of them for doing this operation.

Thanks,

Lee Everest

---------------------------

http://msdn.microsoft.com/en-us/library/ms141696.aspx