3

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
  • Does data get deleted from your staging table? Can you do something like `RunID = (select max(RunID) from stg_table) + 1`? (Be aware of if you need to create an index, as well) – RToyo Jun 12 '17 at 16:26
  • 1
    You can use a sequence object if you are using 2012 or higher, or mimic one if you are using 2008 or lower. – Zohar Peled Jun 12 '17 at 16:37
  • 2
    From what you describe, the date and time is probably more useful than a simple integer. – Dan Bracuk Jun 12 '17 at 16:42

3 Answers3

2

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
2

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

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
1

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • You have to run it before the dataflow task, store the value in a variable, then inside the dataflow task create a derived column using this variable – Hadi Jun 14 '17 at 15:48
  • 1
    Thank You for this! I struggled with this for 3 days until I found your post! – Boltz May 02 '22 at 16:05
  • I created the RunID as stated in your answer. However I have noticed that it has stopped incrementing at 10. Is there any reason for this? – Boltz Jun 13 '22 at 20:27