CODEWITHSUNDEEP
ssisezapi
daniely
daniely

Reputation: 7733

Get Row Count for Data Transferred using EzAPI SSIS

I am transferring some data from one table to another using SSIS with EzAPI. How can I get the number of rows that were transferred?

My setup is as follows

        EzPackage package = new EzPackage();

        EzOleDbConnectionManager srcConn;
        EzOleDbSource src;

        EzOleDbConnectionManager destConn;
        EzOleDbDestination dest;

        EzDataFlow dataFlow;

        destConn = new EzOleDbConnectionManager(package); //set connection string
        srcConn = new EzOleDbConnectionManager(package);

        dataFlow = new EzDataFlow(package);

        src = Activator.CreateInstance(typeof(EzOleDbSource), new object[] { dataFlow }) as EzOleDbSource;
        src.Connection = srcConn;
        src.SqlCommand = odbcImport.Query;

        dest = Activator.CreateInstance(typeof(EzOleDbDestination), new object[] { dataFlow }) as EzOleDbDestination;
        dest.Connection = destConn;
        dest.AttachTo(src, 0, 0);
        dest.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;

        DTSExecResult result = package.Execute();

Where in this can I add something to get the number of rows? For all versions of SQL server 2008r2 and up

Upvotes: 1

Views: 237

Answers (1)

billinkc
billinkc

Reputation: 61211

The quick answer is that the Row Count Transformation isn't included out of the box. I had a brief post about that: Row Count with EzAPI

I downloaded the source project from CodePlex and then edited EzComponents.cs (in EzAPI\src) and added the following code

[CompID("{150E6007-7C6A-4CC3-8FF3-FC73783A972E}")]
public class EzRowCountTransform : EzComponent
{
    public EzRowCountTransform(EzDataFlow dataFlow) : base(dataFlow) { }
    public EzRowCountTransform(EzDataFlow parent, IDTSComponentMetaData100 meta) : base(parent, meta) { }

    public string VariableName
    {
        get { return (string)Meta.CustomPropertyCollection["VariableName"].Value; }
        set { Comp.SetComponentProperty("VariableName", value); }
    }
}

The component id above is only for 2008.

For 2012, it's going to be E26997D8C-70DA-42B2-8208-A19CE3A9FE41 I don't have a 2012 installation at the moment to confirm I didn't transpose a value there but drop a Row Count component onto a data flow, right click and look at the properties. The component/class id is what that value needs to be. Similar story if you're dealing with 2005.

So, once you have the ability to use EzRowCountTransform, you can simply patch it into your existing script. 

// Create an instance of our transform
EzRowCountTransform newRC = null;

// Create a variable to use it
Variable newRCVariable = null;
newRCVariable = package.Variables.Add("RowCountNew", false, "User", 0); 

// ...
src.SqlCommand = odbcImport.Query;

// New code here too
newRC = new EzRowCountTransform(dataFlow);
newRC.AttachTo(src);
newRC.Name = "RC New Rows";
newRC.VariableName = newRCVariable.QualifiedName;

// Continue old code

I have a presentation on various approaches I've used over time and what I like/don't like about them. Type more, click less: a programmatic approach to building SSIS. It contains sample code for creating the EzRowCountTransform and usage.

Upvotes: 1

Related Questions