Outbound connector on output tools

[SeanAdams]

It would be great to have an outbound connector on output tools for 2 reasons:

a) if this outbound connector can carry key results of the output process, this can be saved in an audit log.  For example - rowcounts; success/failure.   This kind of capabiltiy (to generate a log, or to be able to check the rowcount of rows committed to a database) is important for any large BI ETL process

b) this woudl also allow the process to continue after the output process and also act as a flow of control.   For example:

- First output the product dimension

- once done - then connect (using the outbound connector) to the next macro which then updates the Sales fact table using this product dimension (foreign key dependancy)

[DultonM]

I think (a) could prove useful for metric purposes. (b) can be accomplished using the Block Until Done tool.

[SeanAdams]

When pushing rows out to a SQL server table that has an identity column - we often need to use an Output clause in the insert statement to return the new primary keys used so that these can be passed on to the next step.

In cases like this - the output tool should be able to pass these IDs on to the next tool for down-stream manipulation (i.e. the output tool needs to be non-terminal node - so that it can have an outbound connector to pass this on).

This is a very common problem when inserting into a dimension table with an integer primary key, which you then need in order to pass this onto your Fact table loader.

Example: 

Table definition:

Create table Pets
(
PetID int identity(1,1) primary key not null,
PetName varchar(20) not null,

Age int not null,
)

Query: 

Truncate table Pets
GO
Insert into Pets
output
    inserted.petID,
    inserted.Petname
values('Fido',10)

Insert into Pets
output
    inserted.petID,
    inserted.Petname
values('tweety',15)

Output from SQL 

[autolycus]

It would also be helpful to get the exit code from the sql execution to determine success/warning/error status.

[rslopez2]

This would be extremely useful!

We currently spend lots of time\entire extra builds just to compensate for limitations on point A for @SeanAdams 

we would also find tons of value in B) specifically in use cases where a translation table between Dims and Fact runs are helpful. This would really simply workflow designs (often ultimately resulting in 2 separate builds to maintain)

Example:

  Updating\inserting primary Keys in Dim 1 and Dim 2 - Pass updates Dims to Translation table updates needed to simplify bridge logic between a set of natural keys to be leveraged in the subsequent Fact loader run.