SSIS Script Component convert Object type variable to Int Variable

Question

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;
}

Answer 1

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?