none
DATA FLOW TASK - How to add database name and server name to a column before Merging data sources tables??? RRS feed

  • Question

  • WITHIN DATA FLOW TASK ONLY

    Is there a way to get the database name, server name from a Data Flow source and add it as a column to the table used in the ETL?

    I am performing a merge between three data sources and the business needs to identify what record comes from what data source upon the merge.  I wish Microsoft put Execute SQL Task within the Data Flow Task so this could be done EASILY or Add Database Name, Database ID, Connection String, and Server Name to the Audit Task.   I am using three ADO.NET data sources and plan to merge the tables from the three sources into one table as my destination, but I need to know which ADO.NET source record is dominate in the Merge.

    I don't want to use Control Flow task nor a For Each Loop to accomplish this becuase I am doing a Merge.

    Please help.

    Tuesday, February 7, 2012 3:17 PM

Answers

  • Yes...

    Tuesday, February 7, 2012 5:08 PM
  • If you already know the table and server, use the derived columns like Harvshai suggested to add columns containing that info (in the dataflow transformations). 

    If you want to reduce the amount of places you define your server and database, use expressions.  For instance define a var for the server, and one for the db.  Then define the connection string of your oledb connection by using expression relying on your vars.  For the derived columns, you do not event need expressions.

    • Proposed as answer by Eileen Zhao Monday, February 13, 2012 8:22 AM
    • Marked as answer by Eileen Zhao Tuesday, February 14, 2012 5:44 AM
    Tuesday, February 7, 2012 8:32 PM

All replies

  •  

    Step1: Declare variables - DatabaseName and ServerName as string

    Step2: Pull a execute SQL Task and use the below query- Assign the resultset to the variables created above. Use the datasource connection you want this information from.

    select @@servername [ServerName],db_name() [DatabaseName]

    Step3: Use these variables in the derived column or any other place you want to use

    Tuesday, February 7, 2012 3:59 PM
  • One more option is to have two extra columns in all your sources to have the server name and DB name - so when you merge they will have the references -

    select

    @@servername,db_name()
    Tuesday, February 7, 2012 4:01 PM
  • Is there a way to do it without using execute sql task?

    Tuesday, February 7, 2012 4:59 PM
  • I have multiple data sources (multiple connection managers).  can this be done in Data Flow?
    Tuesday, February 7, 2012 4:59 PM
  • Yes...

    Tuesday, February 7, 2012 5:08 PM
  • Yeah I thought about this option but I didn't want to have to use sql command for each table that I need to merge.  Is this the last option?

    Tuesday, February 7, 2012 5:49 PM
  • If you already know the table and server, use the derived columns like Harvshai suggested to add columns containing that info (in the dataflow transformations). 

    If you want to reduce the amount of places you define your server and database, use expressions.  For instance define a var for the server, and one for the db.  Then define the connection string of your oledb connection by using expression relying on your vars.  For the derived columns, you do not event need expressions.

    • Proposed as answer by Eileen Zhao Monday, February 13, 2012 8:22 AM
    • Marked as answer by Eileen Zhao Tuesday, February 14, 2012 5:44 AM
    Tuesday, February 7, 2012 8:32 PM