I have a query that produces a list of part numbers from one DB I need to pass that list of PartNumbers to a second DB to use in a query. Both lists are too large to efficiently use a lookup task. I have a stored procedure written on the second DB that uses a table variable to filter the data and produce the results I need. I am having trouble figuring out how to pass the results from the first query (I put them in a result set) that is linked to a global variable of type object. Then I created an Execute SQL Task anf specify the Stored Procedure and the ? for the input parameter that should be a table variable... and in the parameters tab I specify the variable object that has the part list.
When I run the package it errors at the Execute SQL task step and says:
[Execute SQL Task] Error: Executing the query "up_LoadSitePart" failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'No mapping exists from object type System.__ComObject to a known managed provider native type.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I understand that SSIS dies not seem to be translating the Object with my filter list into something the SP can use as a table variable. What should I do to translate the object into a table variaqble or something that SSIS can map to a table variable for the stored procedure?