SQL Server uses the special variable @@IDENTITY to retrieve the auto-incremented column value of the last insert for a table with such a column defined.
For example, for a table
CREATE TABLE Contact (
contactId int identity(1,1) NOT NULL,
firstName varchar(50) NOT NULL,
when an insert statement is executed
INSERT INTO contact (firstName) VALUES ('carl')
which is followed with a select statement
will return the last inserted id. If this was the first insert, then a '1' is returned.
Talend Open Studio runs this "SELECT @@IDENTITY" query in the tMSSqlLastInsertId component, returning the value in the special field "last_insert_id".
The documentation for tMSSqlLastInsertId (4.2.3) points to an equivalent example with MySQL. In the example, an output component (tMySqlOutput) is run into a tMysqlLastInsertId. In the example, the result is directed to a tLogRow, but that could have been a second tMySqlOutput which performs a second insert.
One problem I found with the example is that the batching is used for the MS SQL components. This causes the tMSSqlLastInsertId component to not work. (The tMSSqlLastInsertId component returns 0 in all cases rather than the identifier.)
To turn off batching, uncheck the box on the Advanced tab of the preceding tMySqlOutput. See the following screenshot.
Another cause of a 0 last insert id is using the wrong connection. In your jobs, you should use a shared database connection. However, with certain drag-and-drop operations, a connection may be created as "built-in". Although the generated built-in may use the same credentials and database instance as the shared connection, the last_insert_id mechanism is connection based. To fix this, make sure that "Use existing connection" is checked in the output component's configuration.
Databases like Oracle create their unique identifiers upfront using sequences. These sequences can be carried into Talend Open Studio components allowing for multiple related inserts. MS SQL server uses auto-incremented columns for its unique identifiers and this requires finding those identifiers after-the-fact. To retrieve the last inserted MS SQL identifier, use the tMSSqllastInsertId component, but be sure to disable any batching.