Retrieving source and destination table names in script

1
votes

I've created an SSIS package with a dataflow.

The oledb source uses a variable with a sql statement.

Following the oledb source, there is a derived column transformation that converts the data. If that breaks, it is redirected to a log table, if it doesnt break it goes to the oledb destination table.

Question: Is there a way I can determine what the source and destination tables are when the conversion breaks, for logging purposes?

Note: The source table is in the variable used by oledb source, and the destination is selected in the oledb destination task.

Thanks for your time.

1
ssiserror-logging

1 Answers

0
votes

It sounds like you are already using data access mode "SQL command from variable". Let's say your variable is named "SourceQuery" and that you construct this in your package before the data flow task. Add 2 more package variables:

  1. SourceTableName
  2. DestinationTableName

Construct "SourceQuery" similar to the way you are doing now, but also store the table name in SourceTableName.

Change the destination task's data access mode from "Table or view" to "Table or view name variable" and select variable name "DestinationTableName". This could either have the correct table name as its initial value, or be set before the data flow task.

The variables SourceTableName and DestinationTableName will now contain the information you want for your logging.