Calling Ssis Experts

[Darling999]

I have scenario like this:

 

Need to capture counts for different fields and mail them later using SSIS.

 

Package: 

 

Execute SQL task:  

DECLARE @CNT1 INT,@CNT2 INT,@CNT3 INT,@CNT4 INT

Select @CNT1 = Count(1)  From Tabl1;

Select @CNT2 = Count(1) From Tabl2;

Select @CNT3 =  Count(1) From Tabl3;

Select @CNT4 = Count(1) From Tabl3;

SELECT @CNT1 AS C1,@CNT2 AS C2,@CNT3 AS C3,@CNT4 AS C4

Later I am using Result set property to Single row and writing the above counts to that.

 

C1 = Variable1

C2 = Variable2

C3 = Variable3

C4 = Variable4

 

Now I am trying to use a script task and check the variable values.

 

First one good, I could see the values but others are not displayed and script task throws an Error.

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_6dfbaf4957db432bb8e4fd9f864315ac.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()

Any Help plz.

[Darling999]

ey evallu leraaa

[xxxmen]

ltt

[DaleSteyn1]

first of all why are you using script task to check values

[Darling999]

first of all why are you using script task to check values

any other Easy way? watch use cehyyachu ani telsu but never used. can you explain in short if posible?

[DaleSteyn1]

any other Easy way? watch use cehyyachu ani telsu but never used. can you explain in short if posible?

simple create a variable with table type and insert these variables into the variable and  compare against that variable

[DaleSteyn1]

simple create a variable with table type and insert these variables into the variable and  compare against that variable send email in your sql code(execute sql task)

this link will answer your problem

http://sqlage.blogspot.com/2014/08/ssis-email-load-summary-insert-update.html

[Darling999]

simple create a variable with table type and insert these variables into the variable and  compare against that variable

danikanna scrpt lo one line would save lot of time bro , also nothing physically exists like sql table

[DaleSteyn1]

danikanna scrpt lo one line would save lot of time bro , also nothing physically exists like sql table

ssis lo object type variable declare chesi andhulo capture chesko antuna value.emo ssis lo c# code maintainance wise ekkuva vadadhu antaruga

[dkchinnari]

ssis lo object type variable declare chesi andhulo capture chesko antuna value.emo ssis lo c# code maintainance wise ekkuva vadadhu antaruga

uncle..nu sql midha work aa 

[DaleSteyn1]

uncle..nu sql midha work aa 

yes dikkul

[dkchinnari]

yes dikkul

ohh ok..

[pachimirchi]

I have scenario like this:

 

Need to capture counts for different fields and mail them later using SSIS.

 

Package: 

 

Execute SQL task:  

DECLARE @CNT1 INT,@CNT2 INT,@CNT3 INT,@CNT4 INT

Select @CNT1 = Count(1)  From Tabl1;

Select @CNT2 = Count(1) From Tabl2;

Select @CNT3 =  Count(1) From Tabl3;

Select @CNT4 = Count(1) From Tabl3;

SELECT @CNT1 AS C1,@CNT2 AS C2,@CNT3 AS C3,@CNT4 AS C4

Later I am using Result set property to Single row and writing the above counts to that.

 

C1 = Variable1

C2 = Variable2

C3 = Variable3

C4 = Variable4

 

Now I am trying to use a script task and check the variable values.

 

First one good, I could see the values but others are not displayed and script task throws an Error.

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_6dfbaf4957db432bb8e4fd9f864315ac.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()

Any Help plz.

nee variables lo okati missing bhayyya.. sql task success avtundi annav kabatti , issue is due to the scope of variable.. 

[Darling999]

nee variables lo okati missing bhayyya.. sql task success avtundi annav kabatti , issue is due to the scope of variable.. 

ledu Dude.. everything was fine, except I did not add that varibale in the script task for read purpose. anduku Error throw chesindi.