Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Reading object variable in Script task

Jun 9 2011 12:27AM by Sudeep Raj   

A lot of time people ask me if there is a way to read object variable in Script task. It comes in handy when you wish to work on the results of an Execute SQL query. Generally object variables are used along with Foreach Loop. However cases arise where you would like to read them separately. So how do you do it?

Here is a sample to raed an object variable "objTest":

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;
using System.Data.OleDb;

namespace ST_5aea2611332745a298a74702e4129af8.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {

            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion


            public void Main()
            {
                // TODO: Add your code here
                Dts.TaskResult = (int)ScriptResults.Success;
                OleDbDataAdapter oleDA = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                DataColumn col = null;
                DataRow row = null;
                string strMsg = null;

                oleDA.Fill(dt, Dts.Variables["objTest"].Value);

                foreach (DataRow row_ in dt.Rows)
                {
                    row = row_;
                    foreach (DataColumn col_ in dt.Columns)
                    {
                        col = col_;
                        strMsg = strMsg + col.ColumnName + ": " + row[col.Ordinal].ToString() + Environment.NewLine;
                    }
                    MessageBox.Show(strMsg);
                    strMsg = "";
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }

        }
    } 
VB Code:
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Data.OleDb

Namespace ST_5aea2611332745a298a74702e4129af8.csproj
	 _
	Public Partial Class ScriptMain
		Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

		#Region "VSTA generated code"
		Private Enum ScriptResults
			Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
			Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
		End Enum
		#End Region


		Public Sub Main()
			' TODO: Add your code here
			Dts.TaskResult = CInt(ScriptResults.Success)
			Dim oleDA As New OleDbDataAdapter()
			Dim dt As New DataTable()
			Dim col As DataColumn = Nothing
			Dim row As DataRow = Nothing
			Dim strMsg As String = Nothing

			oleDA.Fill(dt, Dts.Variables("objTest").Value)

			For Each row_ As DataRow In dt.Rows
				row = row_
				For Each col_ As DataColumn In dt.Columns
					col = col_
					strMsg = strMsg & col.ColumnName & ": " & row(col.Ordinal).ToString() & Environment.NewLine
				Next
				MessageBox.Show(strMsg)
				strMsg = ""
			Next

			Dts.TaskResult = CInt(ScriptResults.Success)
		End Sub

	End Class
End Namespace

Tags: SSIS, SQL Server, MSBI, Script Task, BRH, #SQL Server,


Sudeep Raj
12 · 13% · 4303
3
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

22  Comments  

  • Hi.. Would be useful too if VB equivalent is posted...

    commented on Jul 1 2011 3:25AM
    Rajkumar
    282 · 0% · 153
  • Sure will do that.

    commented on Jul 1 2011 9:40AM
    Sudeep Raj
    12 · 13% · 4303
  • Done!!

    commented on Jul 2 2011 2:02PM
    Sudeep Raj
    12 · 13% · 4303
  • Can we do the same using SQLDataAdaptor?

    commented on Nov 16 2011 5:50AM
    prasa
    3050 · 0% · 2
  • Could you please elaborate.

    commented on Nov 16 2011 6:06AM
    Sudeep Raj
    12 · 13% · 4303
  • hi sundeep , can you help me

    I am tring the above C# code in script component using it as source

    so am reading a object and pushing that in to a datatable

    and then am trying to push the datatable in to output buffers , to the mapping out put columns ... can we achieve this if we can can you help me

    commented on Jan 4 2012 8:43AM
    akhil393
    3050 · 0% · 2
  • Hi akhil, Can you provide more details as to what you need and what are the input and output sources?

    commented on Jan 4 2012 8:54AM
    Sudeep Raj
    12 · 13% · 4303
  • Thank you. Your example made it easy. Do you have an example of writing to an object variable?

    commented on Jul 14 2012 2:36AM
    gstark
    2749 · 0% · 4
  • Hi GStark,

    Can you provide a scenario where you need to do this. Have a look at this it could be used to send data to objet variable using RecordSet Destination.

    commented on Jul 16 2012 3:59AM
    Sudeep Raj
    12 · 13% · 4303
  • Hi Sudeep,

    I'm trying to mimic your C# code as it is and some how i'm getting the below error at this oleDA.Fill(dt, Dts.Variables["users"].Value); statement. Can you please help me with this?

    Error: Object is not an ADODB.RecordSet or an ADODB.Record. parameter name adodb

    commented on Sep 16 2012 12:57PM
    sam123
    2749 · 0% · 4
  • Hi Sam,

    How are you populating the object variable?

    commented on Sep 16 2012 1:05PM
    Sudeep Raj
    12 · 13% · 4303
  • System.Collections.ArrayList usernames= new System.Collections.ArrayList();

    if (userslostpercent> 20)
    {

    usernames.Add(Dts.Variables["usrname"].Value);

    }

    Dts.Variables["users"].Value = usernames;

    commented on Sep 16 2012 9:37PM
    sam123
    2749 · 0% · 4
  • Hi Sam, It will not read the object variable created by .NET Code. It will read the object variable created by SSIS, either using execute SQL task of Recordset destination in Fata Flow Task.

    commented on Sep 17 2012 6:40AM
    Sudeep Raj
    12 · 13% · 4303
  • @Sam... Dts.Variables["users"].Value is an object variable created by SSIS only. If this is not what you meant please correct me.

    System.Collections.ArrayList usernames= new System.Collections.ArrayList();

    if (userslostpercent> 20)

    {

    usernames.Add(Dts.Variables["usrname"].Value); (Dts.Variables["usrname"].Value is a String variable)

    }

    Dts.Variables["users"].Value = usernames; (usernames is an Arraylist and after this stmt get executed i'm seeing the value in users object variable.)

    commented on Sep 17 2012 8:13AM
    sam123
    2749 · 0% · 4
  • Sam, what you are doing here is creating a string array. You cannot read this value from the script I provided in the block. Can you tell me what are you trying to achieve?

    commented on Sep 17 2012 8:23AM
    Sudeep Raj
    12 · 13% · 4303
  • Hi Sudeep,

    Thanks for the script to loop through an objects dataset and columns, etc. I use it often to check data in my SSIS object variables.

    However, I use my object variables to check for data (simple SQL, like Select Name from Contacts where lastName='Smith') and often, there are no rows returned. Is there a statement like "IsNull(obj)" that I can check? Otherwise, you will get the error that Sam123 mentions:

    Error: Object is not an ADODB.RecordSet or an ADODB.Record. parameter name adodb

    Thanks, schmoot

    commented on Oct 1 2012 11:17PM
    schmoot
    2829 · 0% · 3
  • Hi Schmoot,

    Which version of SSIS are you using? I am doing the same for SSIS 2008 and SSIS 2012 and it works just fine in both SSIS. I tried a query like Select * from tbl where 1=0, This too works with no issues.

    commented on Oct 2 2012 1:04PM
    Sudeep Raj
    12 · 13% · 4303
  • Hi Sudeep,

    I'm using SSIS 2008. Basically my question is either how do I check for IsNull(objThing)? Or, if I'm looping through (shredding) objThing, can I exit the process if objThing recordset has no records?

    commented on Oct 3 2012 2:16PM
    schmoot
    2829 · 0% · 3
  • If I understand your query correctly, if there are no records it will any ways not go into the loop.

    commented on Oct 4 2012 2:23AM
    Sudeep Raj
    12 · 13% · 4303
  • I think it would be more efficient to iterate the ADODB record set directly instead of loading it into a DataTable. My result set in SSIS is so huge (GB) that it will consume even more memory when filled into DataTable. Can you show me an example of how to do that in C# ? Thanks, David

    commented on Nov 11 2013 7:40PM
    blasto
    2367 · 0% · 5
Previous 1 | 2 Next

Your Comment


Sign Up or Login to post a comment.

"SSIS - Reading object variable in Script task" rated 5 out of 5 by 3 readers
SSIS - Reading object variable in Script task , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]