Need to create a Run id for each package run in SSIS Package-CodePudding

I have an SSIS Package that runs a query and inserts values into a different table. Each time the package runs, I want to create a unique RunID for the results of that run. Here are the columns from my table. I have tried this using the Execute SQL Task and setting up the User::RunID variable but, I believe I am doing something wrong. Can anyone provide step by step instructions on how to do this?

CodePudding user response：

You need 2 tables for this.

create table runs(
runID int identity primary key,
runDateTime datetime default getdate()
)

create table runReturns(
runReturnsID int identity primary key,
runID int not null,
[the rest of the data set]
)

In ssis, start with an execute SQL.

Add this query...

insert into runs (runDateTime) values(?);
select SCOPE_IDENTITY()

Map the parameter (?) to Now();

Change the result set to single row and map the first column to a parameter called runID.

Now create a data flow.

Insert your query into a sql source.

Add a derived column and map a new column to runID.

Finally, add a destination to your table and map accordingly.

CodePudding user response：

Adding a completely sql answer to compliment as an alternative since there are no transformations at all:

Same 2 tables:

create table runs(
runID int identity primary key,
runDateTime datetime default getdate()
)

create table runReturns(
runReturnsID int identity primary key,
runID int not null,
[the rest of the data set]
)

Create a Job.

Add a step and base it on SQL.

declare @runID int;

insert into runs(runDateTime) values(getdate());
select @runID = scope_idenity();

insert into runReturns(
runID, [rest of your columns])
select @runID
, [rest of your columns]
from [rest of your query]

CodePudding user response：

An approach that might solve the issue, is the system scoped variable ServerExecutionID By default, System scoped variables are hidden in the Variables menu but you can expose them by clicking the Grid options button (rightmost of the 5).

If you reference that variable using the appropriate placeholder (? for OLE/ODBC or a named parameter for ADO) and map to the variable, then every server execution will have a monotonically increasing number associated to it. Runs from Visual Studio or outside of the SSISDB, will always have a value of 0 associated to them but given that this is only encountered during development, this might address the issue.

Sample query based on the newer picture

INSERT INTO dbo.RunMapTable
SELECT ? AS RunID
, D.Name
FROM
(
    VALUES ('A')
,   ('B')
,   ('C')
,   ('D')
)D([name];

Parameter Mapping

0 -> System::ServerExecutionID

As an added bonus, you can then tie your custom logging back to the native logging in the SSISDB.