To use tMSSqlLastInsertId correctly, you must disable "Use Batch Size" in the tMSSqlOutput component that does the insert. Otherwise, the last_insert_id value will always be zero.
NOTE: All kudos for this goes to Carl Bekwam Bekwam Blog: tMSSqlLastInsertId Returns 0. I added here because I didn't see it and felt it was helpful for the community.
Thanks for sharing it on forum.