CODEWITHSUNDEEP
sqlsql-serverssisetlderived-column
user7753835
user7753835

Reputation:

How to make a "Run ID" in SQL Server and SSIS

I have a report that is run regularly and put into a staging database for review. It is processed through Microsoft's SQL Server Integration Services that transforms the data; however, I'd like to add an 'ID' column that will increment as an integer each time the trending report is run but so far can't really figure out how to do this.

The goal would be not to assign a new number for each row but assign all rows from one run the same number

Ideally, it would be easier for me to do it through a derived column in SSIS than changing the SQL code around but I'll consider both options.

RunID   Name
----- |----------------
 1    |  A
 1    |  B
 1    |  C
 1    |  D
 2    |  A
 2    |  B
 2    |  C
 3    |  A

Upvotes: 3

Views: 4944

Answers (3)

Nick.Mc
Nick.Mc

Reputation: 19194

If the run id is bound to the execution of an SSIS package, you can use the ExecutionInstanceGUID. https://learn.microsoft.com/en-us/sql/integration-services/system-variables

Are you sure you need an SSIS package to transform the data? Most things can be done in T-SQL

Upvotes: 2

Hadi
Hadi

Reputation: 37348

You can Achieve this using a package Variable

  1. You can add a Package Variable that store an integer @[User::RunID]

  2. On each package execution get the Maximum RunID from the destination table using Execute SQL Task using a similar query:

    select ISNULL(max(RunID),0) + 1 from stg_table

  3. Add this value to the variable as a ResultSet

This way the value will increment by 1 on each package execution

More detailed info about mappinf result to variable in the following link:

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Create a table with an IDENTITY column for the RunID. The table can hold whatever other information about the run you want, but at the very least I would include the run datetime.

Let the first step in your SSIS package be to insert a new row into the table and get the IDENTITY, and populate a package variable with the IDENTITY of the new row. That is your RunID which you can then do whatever you want with.

As a bonus, you'll have a permanent history record of your Runs, when they occurred, and whatever other meta data you choose to store about them.

Upvotes: 2

Related Questions