Using a regular Ouput Data Tool, it is possible to insert records into a MS SQL database without specifying the identity column. MS SQL generates the integer id's. Using In-Database Tools it doesn't seem to be possible to insert records into a MS SQL database table which contains an Identity column. The following error occurs:
An explicit value for the identity column in table 'dbo.test' can only be specified when a column list is used and IDENTITY_INSERT is ON
Is there any workaround using only In-Database Tools?
You may try the Sample IN-DB tool. It doesn't create the Record ID column but identifies a sample based of your column selected. Example in my workflow I was needing to identify the most recent 113 weeks. I used 113 for my number in the sample tool. the results brought back the most recent 113 and removed all other weeks.