combining package variables in Foreach loop
it is similar. the difference is i have data from two different sources. I have one column, say var_product_name, in the table for varabile1 and assgined to that package adoObject. I have another query which pull the a single package varabile, say the var_manufacture_name. in the msgbox of the foreach loop, i need to cancat the var_manufacture_name to the var_product_name. it boom with the error above on the var_manufacture_name. how do i tell/introduce the ForEach container the value of var_manufacture_name and the task in the container will be able to see or use the var_manufacture_name? thanks. -- IBM has jobs for USA employee to work oversea (India) and get their wages.
January 22nd, 2011 7:21pm

assign ADO object output to a package level valuable with data type "Object". now introduce one new script task and feed it with the new variable which is holding sql query output and the variable whose value have to concate. Inside script task assign object variable to any string array or dataset or structure or any other collection objects. Now loop through the array and do concatenation there itself. store the cocatenated value to a new string array. at end assign this new array variable to another package level object variable. feed the foreach loop with the second package level object valiable. display its value to msg box.Chandrajit Samanta
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2011 8:04pm

I have a Foreach loop which loop thru an ADO object, which is basically an outcome of a column in a table. in foreach loop editor, variable mapping, a user_string_variable1 is assigned to index 0. The feed to the Foreach loop is a Execute SQL Task which send the user_adoObject as Result Set in Full result set. In the Foreach Loop Container, I've a script task with a msgbox to show the string stored in user_string_variable2. It works out fine. i.e. MsgBox(Dts.Variables("user_string_variable1").Value) What I like to know is how can I cancatenate the string in msgbox, if I have another package variable, user_string_variable2. i.e. MsgBox(Dts.Variables( "user_string_variable1").Value + Dts.Variables("user_string_variable2").Value) Everything works find-- IBM has jobs for USA employee to work oversea (India) and get their wages.
January 22nd, 2011 8:11pm

To concatenate you should use the & (ampersand) sign e.g. MsgBox(Dts.Variables("user_string_variable1").Value & " " & Dts.Variables("user_string_variable2").Value) Often, I find it becomes better formatted when you break the lines with Environment.NewLine so the code would look like MsgBox("Value 1: " & Dts.Variables("user_string_variable1").Value & Environment.NewLine & "Value 2: " & Dts.Variables("user_string_variable2").Value) Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2011 8:18pm

well, that's what i first try and failed the error is ... (how to introduce it to the container? or the msgbox in the script task within the container?) Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. ---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index) at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index) at ST_3fc81d7fcb2d4161803b6a55ed562454.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() -- IBM has jobs for USA employee to work oversea (India) and get their wages.
January 22nd, 2011 8:26pm

The variables are not initialized at the time you call them.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2011 8:36pm

well, user_string_variable1 is displayed fine. when i concat the user_string_variable2 that's when the problem happens. In General of Execute SQL Task Editor, user_string_variable2 is assigned from a Single row ResultSet from a SQL statement. In Result Set, the Resut Set has the Result Name and the Variable Name mapped. I did a debug on post, and the package variable is assigned with correct value. how to cancat it with the varilable user_string_variable1 in ForEach loop?-- IBM has jobs for USA employee to work oversea (India) and get their wages.
January 22nd, 2011 8:45pm

You have just narrowed the issue down, is what you are doing close to what is here: http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2011 8:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics