Capture database name before Merge in SSIS Data Flow Task

Add the variables that you want to your package then use the Execute SQL Task.  Set the ResultSet General Property of the Excecute SQL Task to SingleRow and specify your query that returns the things you want; e.g.

SELECT DatabaseName = DB_NAME(), ServerName=@@SERVERNAME

Go to the ResultSet page of the Execute SQL Task properties and map your variables to the colums in your select statement.

As far as the connetion string, since it is in a connection manager you can add a script task to assign the connectionstring property to a variable.

It's a shame the Audit data flow transform doesn't proide this information.  It does have a task name which allows you to name your data flow task and easily get that into the data flow.

Jackie,

Did Ray's post answer your question?

Thank you,
Jeremy Kadlec

Ray  I understand what you are saying but can you do this in DATA FLOW task.   in other words if I have 3 different sources that I pulling data from and and I am merging the data together...is there someway to put the dataname name from the source into the table before the merge happens?

I don't want to use a for each loop because I have more than one source and I am doing a merge.

Why doesn't Microsoft put Execute SQL Task in the Data Flow as well.  Or if they are going to provide audit information then add database name and server name per Connection Manager used in the Data Flow.

Anyone have any suggestions?

You can add columns to your queries in the data flow; e.g. @@SERVERNAME, DB_NAME(), etc.