getting a rowcount

here is a screen shot of my data flow task...after the upsert I need to update the NavSync table with the record count of total records upserted....and I would like to include in the data flow a table to capture records that could not be upserted.
20100926.docx

>>I would like to include in the data flow a table to capture records that could not be upserted<<
what do you mean by "record that could not be upserted" ??!!!
in the upsert, if rows exists update will apply, if don't exists insert will apply. so what rows you mean?

and if you want to get number of rows which sent to upsert, so you should put row count transformation between OLEDB Source and lookup.

My OLE DB Destination only has a red arrow available...I get an error if I drag both the red arrow for the OLE DB Destination and the green arrow from the OLE DB Command to the Row Count Transformation. How can I get a total rowcount for the upsert?

OK...gotcha! Should I check all the available input columns for the row count configuration?

So my data flow goes from row count to lookup and then matched and not matched arrows to OLE DB Command and OLE DB Destination respectively. What tool should I use in the data flow after that to handle my update to NavSync table?

What should my query look like? Should I use parameter mapping, or just result set?

How should I set the parameter for myLasttime? It's binary in the database and that is not a parameter option with SSIS. How do I convert?

you should create an INT32 variable in PACKAGE SCOPE,let's name it RecCount
after configuring row count transformation to fill number of rows in this variable,
you should put two separate execute sql task after data flow task ( in control flow tab)
one with this sql statement:

 update NavSync set myTimeStamp = ?, LastUpdate=getdate(), LastExecuted = getdate(),RecordCount=? where TableID=11
and you should set parameter in parameter mapping tab for both parameters,
you should map User::RecCount to second parameter( if you use OLEDB connection manager, this parameter name should be 1 in parameter mapping tab)

and second execute sql task with this sqlstatement:
update NavSync set LastExecuted = getdate() where TableID=11
there is no need to set parameters for this one, because no input/output parameters exists in this query.


I don't know what do you mean by binary data type here? I can't find any binary information here.

myLasttime is of binary type in the database. There isn't a binary type option in SSIS. I get an error about the data type:

[Execute SQL Task] Error: Executing the query "update NavSync set myTimeStamp = ?, LastUpdate=get..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 
I set the variable to String type in the package variable window and I've tried both (varchar and DBTIMESTAMP) data type setting in the parameter configuration. The options in the package variable window and the options in the parameter configuration are not the same and binary option is not available in either. Don't know how to configure the data type for the variable (myLasttime)

you should use BOOLEAN instead of binary in SSIS for datatype of binary variables.

In the Parameter Mapping of the SQL task, I set the variable myLasttime to DBTIMESTAMP  type , but in the package variable configuration I set it to BOOLEAN type.

None of the same options are available in the Parameter Mapping configuration that are available in the package variable configuration. Makes it hard to know what to do?

BOOLEAN isn't a data type option in the parameter Mapping of the SQL task

BOOLEAN doesn't work for the earlier SQL task where I originally set myLasttime variable:
SELECT     myTimestamp AS myLasttime
FROM         dbo.NavSync
WHERE     (TableID = 11)

myTimestamp field in the NavSync table is of binary data type. When I changed the data type of myLasttime in the package variable I get error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "myLasttime": "The type of the value being assigned to variable "User::myLasttime" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

tell me datatype of myTimestamp column in the NavSync table?
and datatype of myLasttime variable in ssis package?

myTimestamp field in the NavSync table is of binary data type.
I changed the data type of myLasttime in the package variable to BOOLEAN

I had it set to String and didn't get error...package would run successfully, but didn't seem to have any effect in criteria filter(WHERE     ([timestamp] > ?). All records got selected with no filter

When I changed it to BOOLEAN data type package fails and  I get error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "myLasttime": "The type of the value being assigned to variable "User::myLasttime" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

>>WHERE     ([timestamp] > ?<<
is the  ? parameter referred to myLastTime variable in package? you should check boolean values with = or <> in t-sql , not with > or <

>>WHERE     ([timestamp] > ?<<
is used in the SQL statement for the OLE DB Source in the Data Flow right before row count transformation.

myTimestamp in database is binary looks like this:
0x000000002454137A

trying to store myTimestamp into variable myLasttime

try SByte or Byte variable datatypes in ssis

tried both SByte & Byte variable datatypes in ssis...netiher works

I have a question,
you read myTimestamp here:
SELECT     myTimestamp AS myLasttime
FROM         dbo.NavSync
WHERE     (TableID = 11)

and then you update myTimeStamp here:
update NavSync set myTimeStamp = ?, LastUpdate=getdate(), LastExecuted = getdate(),RecordCount=? where TableID=11

what this thing means?
you got a value and then update that value in same table and same record again! why?!!!! what happen if you don't do this?!

<<SELECT     myTimestamp AS myLasttime
FROM         dbo.NavSync
WHERE     (TableID = 11)<<

need myLasttime to query source to see if any new records since last update

Your right I missed a step there:
after I query source where timestamp > myLasttime, I need to store a new timestamp value  from source for @TempTimestamp

then set @myRevTimestamp = @TempTimestamp

then:
update NavSync set myTimeStamp = @myRevTimestamp, LastUpdate=getdate(), LastExecuted = getdate(),RecordCount=@RecCount where TableID=11

So not sure where to put put - @myRevTimestamp = @TempTimestamp
in my control flow or data flow

and then, still have data type problem?

Here's the stored procedure I'm trying to convert to SSIS:

USE [MyDatabase]
GO

/****** Object:  StoredProcedure [dbo].[Sync_Lab_Test_PCB] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sync_Lab_Test_PCB] AS
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
      declare @myLasttime as binary(8)
      declare @RecCount as int

      declare @myRevTimestamp as binary(8)
      declare @TempTimestamp as binary(8)

      declare @Customer_No as varchar(20)
      declare @TC_No as varchar(20)
      declare @Draw_Date as Datetime

      declare @OutsideLab as tinyint
      declare @Recommendation_Code_ID as varchar(20)
      declare @Comment_Code_ID as varchar(10)

      declare @PowerFactor100 as decimal(20,5)
      declare @PowerFactor100Result as int
      declare @PowerFactor25 as decimal(20,5)
      declare @PowerFactor25Result as int


      -- only one record in the NavSync database

      set @RecCount = 0

      select @myLasttime = myTimestamp from NavSync where TableID=11

      if @@rowcount=0
      begin
            Insert NavSync values(9,1,getdate(),getdate(),0,'PCBPosted')
            select @myLasttime = myTimestamp from NavSync where TableID=11
      end

      DECLARE syncLT_ICP_cursor CURSOR READ_ONLY FOR  
            select [timestamp],
             [Customer No_],
             [TC No_],
            [Draw Date],
            [Outside Lab],
            [100 Degree Power Factor],
            [100 Degree Result],
            [25 Degree Power Factor],
            [25 Degree Result]

      from MYSQL.MyDatabase.dbo.[TableName]
      where [timestamp] > @myLasttime
      order by timestamp

      OPEN syncLT_ICP_cursor
     
      FETCH NEXT FROM syncLT_ICP_cursor INTO
            @TempTimestamp,
            @Customer_No,
            @TC_No,
            @Draw_Date,
            @OutsideLab,
            @PowerFactor100,
            @PowerFactor100Result,
            @PowerFactor25,
            @PowerFactor25Result


      WHILE @@FETCH_STATUS = 0 and @RecCount < 40000
      BEGIN
     
            update PCBPosted set
                  OutsideLab = @OutsideLab,
                  PowerFactor100 = @PowerFactor100,
                  PowerFactor100Result = @PowerFactor100Result,
                  PowerFactor25 = @PowerFactor25,
                  PowerFactor25Result = @PowerFactor25Result
             where  Customer_No = @Customer_No and TC_No = @TC_No and Draw_Date = @Draw_Date

            if @@rowcount = 0
            begin
                  Insert into PCBPosted
                  (
                        TC_No,Customer_No,Draw_Date,
                        OutsideLab,
                        PowerFactor100,PowerFactor100Result,
                        PowerFactor25,PowerFactor25Result
                  )
                   values
                  (
                        @TC_No,@Customer_No,@Draw_Date,
                        @OutsideLab,
                        @PowerFactor100,@PowerFactor100Result,
                        @PowerFactor25,@PowerFactor25Result
                  )
            end

            set @RecCount = @RecCount + 1
            set @myRevTimestamp = @TempTimestamp

            FETCH NEXT FROM syncLT_ICP_cursor INTO
                  @TempTimestamp,
                  @Customer_No,
                  @TC_No,
                  @Draw_Date,
                  @OutsideLab,
                  @PowerFactor100,
                  @PowerFactor100Result,
                  @PowerFactor25,
                  @PowerFactor25Result
      END

      CLOSE syncLT_ICP_cursor
      DEALLOCATE syncLT_ICP_cursor
      if @RecCount > 0
            begin
                  update NavSync set myTimeStamp = @myRevTimestamp, LastUpdate=getdate(), LastExecuted = getdate(),RecordCount=@RecCount where TableID=11
            end
      else
            update NavSync set LastExecuted = getdate() where TableID=11
GO:

Would this be your recomendation?:

Create another package variable TempTimestamp (data type???)

Alter SQL statement for OLE DB Source in Data Flow:

SELECT     [timestamp] AS TempTimestamp , [Customer No_], [TC No_], [Draw Date], [Outside Lab], [AROC 1242 Content], [AROC 1254 Content], [AROC 1260 Content], [Label Color],
 [Other AROC Content], [Total PCB Content], [PCB Class]
FROM         dbo.[Lab Test PCB Posted]
WHERE     ([timestamp] > ?)
order by timestamp

Alter Result Set and Parameter mapping for SQL Task in Control Flow:
replacing TempTimestamp (data type ???) for myLasttime

>>after I query source where timestamp > myLasttime, I need to store a new timestamp value  from source for @TempTimestamp<<
I'm not clearly understand what you mean in this part, could you explain more?
tell me where @TempTimestamp filled?

Query in OLE DB source
SELECT     [timestamp] AS TempTimestamp
FROM         dbo.[Lab Test PCB Posted]
WHERE     ([timestamp] > ?)
order by timestamp

I had no similar experience on binary data types in ssis,
but I think you can do this work around:
set pacakge variable as string.
and in your update statement, convert varchar data to binary.
update myTable set [timestamp]=convert(<new_data_type>,?)

try it and let me know the result

I'm not sure what you mean with update statement...conversion needs to be in select statement?
 I tried this but didn't seem to convert:
SELECT     [timestamp] AS TempTimestamp , [Customer No_], [TC No_], [Draw Date], [Outside Lab], [AROC 1242 Content], [AROC 1254 Content], [AROC 1260 Content], [Label Color],
                      [Other AROC Content], [Total PCB Content], [PCB Class]
FROM         dbo.[Lab Test PCB Posted]
WHERE     ([timestamp] > convert(binary, ?))
order by timestamp

Also, I need to somehow assign a value to TempTimestamp and It doesn't seem to work by putting it in my select query in the OLE DB Source:
SELECT     [timestamp] AS TempTimestamp , [Customer No_], [TC No_], [Draw Date], [Outside Lab], [AROC 1242 Content], [AROC 1254 Content], [AROC 1260 Content], [Label Color],
                      [Other AROC Content], [Total PCB Content], [PCB Class]
FROM         dbo.[Lab Test PCB Posted]
WHERE     ([timestamp] > convert(binary, ?))
order by timestamp
There's no way to configure a result set in the OLE DB Source?

reza rad,
Thank-you so very much for all of your help! I am going to accept your solution for the row count and play around with these queries and data types and open a new question if I still need help. You have been very helpful, and I am very gracious to you!

Thank-you!

Hi,
I missed your question for a while
did your problem solved?

regards,

No, I used your suggestion:

<<set pacakge variable as string.
and in your update statement, convert varchar data to binary<<

UPDATE    dbo.NavSync
SET           [myTimestamp] = CONVERT(binary, ?), LastUpdate = GETDATE(), RecordCount = ?
WHERE     (TableID = 11)

I configured the parameter Mapping and Result Set...see attachment

And I get error:
Error: No result rowset is associated with the execution of this query.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "TempTimestamp": "Exception from HRESULT: 0xC0015005".




20101001.docx

hard to track this more on the fly.
could you upload all of your materials and let me work on your package and help you more handy.
I need :
your package file now
all tables which you work on them from ssis package ( I mean structure and sample rows of each table)

if you can't upload them here , you can mail them me to :
a dot raad dot g at gmail dot com

I'll open a new question.....Maybe the solution I'm working on now won't be so complex!