0

I need to load data from ODBC source to OLEDB destination. The OLEDB destination table does not contain Identity column but it does have a Primary key column where the values has to be inserted similar to Identity column(increment it by 1).

I have queried the destination table(OLEDB Source) and have retrieved the max(Id) and assigned to an SSIS variable of Object type and using Script Component from Data Flow to get the max(Id)+1 value row by row.

While converting the Object type variable to Integer type, I get error as

Unable to cast COM object of type 'System.__ComObject' to interface type 'System.IConvertible'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{805E3B62-B5E9-393D-8941-377D8BF4556B}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

The column from destination table can not be altered.

MaxUID = Object Variable

RowNumber = Output variable

Script Component Code

int i = 1;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.RowNumber = Convert.ToInt32(this.Variables.MaxUID) + 1;
    i = i + 1;
}
Abhishek Dubey
  • 1
  • 1
  • Why don't you simply use a variable of type `Int` ? – Filburt Nov 24 '21 at 09:32
  • Its a DB2 Ole db source, not able to connect using Execute-T-SQL tasks Currently I am using Third party tool Kingsway Soft to connect to the destination to retrieve the max(Id) value and using RecordSet component to store it in ObjectType Variable. Recordset only allows Object type vaariable for storing the value dynamically. – Abhishek Dubey Nov 24 '21 at 09:37
  • You should be able to store the scalar integer value of this ODBC query instead of the result recordset. – Filburt Nov 24 '21 at 09:40
  • Can you please suggest how can i achieve this. – Abhishek Dubey Nov 24 '21 at 09:46
  • [How to set a variable using a scalar-valued tSQL function in SSIS SQL Task](https://stackoverflow.com/a/14965132/205233) shows how to do it. With the current version (SQL 2019) it's even possible to use the column name instead of the column index as shown in the above answer. – Filburt Nov 24 '21 at 10:15

1 Answers1

0

This are the steps you need:

  1. Prepare an OleDb Connection to the database you intend to query the MAX(id) from.

  2. Declare a Variable MaxUID of type Int32 (or Int64 if necessary)

  3. Add an "Execute SQL Task" to your package.

  4. Configure your "Execute SQL Task" like this:

    "General" Tab
    ResultSet = "Single Row"
    SourceType = "Direct Input"
    SQL Statement = SELECT max(Id)+1 FROM your_table_name AS [MaxUID]

    "Result Set" Tab
    Result Name = MaxUID
    Variable Name = User::MaxUID

Now you have a Variable of type integer you can use in your Script Task.

Side note: If you only use this script task to increment the MaxUID value would an Expression Task be much easier?

Filburt
  • 17,626
  • 12
  • 64
  • 115