locked
Variable Mapping and Resultset Questions RRS feed

  • Question

  • I'm just getting started with SSIS and have a couple basic questions. I have a Foreach Loop that iterates over a collection of database names. The collection must include only database names that match a substring. I haven't found a way to filter the SMO Enumerator collection so, instead, I feed the resultset of an Execute SQL Task into the Foreach Variable Enumerator. 

    Of course this variable (oDBnames) is object type so in Foreach Variable Mapping a string variable (sDBname) is mapped to the only value in the collection (database name). Now sDBname should be available for an Execute SQL Task inside the container. But I keep getting the following errors:

    Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::sDBname" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::sDBname" cannot be applied.

    Can someone explain what is going on and how to correct this? I haven't found an answer in BOL or MSDN yet. Thanks.

    Another beginner question: The results/messages from a query run in Management Studio are displayed in the results/messages panes. But how would you save and view query output when using the Execute SQL Task? Seems like I should be able to dump Full result set into an object variable and display that, but everything I try raises an error ...

    Friday, November 17, 2006 3:05 PM

Answers

  • Is sDBname a string variable?

    Is the variable sDBname mapped to index 0?

    I'm not aware of any visualizers in the control flow, but if you create a dataflow task, you can add a visualizer there.  You can also set breakpoints and look at values but I'm not sure how you extract a field value from an OLEDB recordset and the default value just shows System.__ComObject.

    Monday, November 20, 2006 3:14 PM

All replies

  • Is sDBname a string variable?

    Is the variable sDBname mapped to index 0?

    I'm not aware of any visualizers in the control flow, but if you create a dataflow task, you can add a visualizer there.  You can also set breakpoints and look at values but I'm not sure how you extract a field value from an OLEDB recordset and the default value just shows System.__ComObject.

    Monday, November 20, 2006 3:14 PM
  • Thanks Larry. I lost track of this thread, but if i recall index 0 vs. index 1 was the the issue. I've come a long way since then, but assistance on these simple stumbling blocks is very helpful when you're just getting started!

    Thursday, January 25, 2007 4:07 PM