Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Execute Sql task to update a target table in a database using staging table from another Databse

Answered Execute Sql task to update a target table in a database using staging table from another Databse

  • Friday, January 18, 2013 5:13 PM
     
     
    Sign In to Vote
    0

    Hi,

    Is it Possible to use Execute Sql Task in SSIS  to update a target Dimension  table in a database using a staging table from another database  on the same server.I want to use this approach to update records for implementing  SCD2  

    Many Thanks

     

All Replies

  • Friday, January 18, 2013 6:10 PM
     
     
    Sign In to Vote
    0

    As long as your databases and tables reside on the same server, Execute sql task can use ALL DML statements. If your tables are on diff servers, then you would need linked server set up among themselves.

    Thanks,

    hsbal

     
  • Saturday, January 19, 2013 8:18 AM
     
     
    Sign In to Vote
    0

    When you specify a connection string, you specify the database ,how can we connect to two different databases.

    Thanks

     
  • Saturday, January 19, 2013 8:35 PM
    Moderator
     
     
    Sign In to Vote
    0

    Use the SSIS SCD component that is suited for the purpose: http://dimensionmergescd.codeplex.com/ OR

    http://msdn.microsoft.com/en-us/library/ms141715%28v=sql.100%29.aspx if none suites your need than there are alternatives

    http://bennyaustin.wordpress.com/2010/05/29/alternatives-to-ssis-scd-wizard-component/

    Arthur My Blog

     
  • Sunday, January 20, 2013 12:02 AM
     
     Answered Has Code
    Sign In to Vote
    0

    You specifiy one of the databases in your connection string.  In the query that you run, you refer to the object in the second database with a three part name:

    database.schema.object:

    Insert into sch.TargetTable 
select * from otherdb.src.SourceTable

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed As Answer by Harry Bal Tuesday, January 22, 2013 1:53 PM
    • Marked As Answer by Vision2040 Wednesday, January 23, 2013 3:38 PM
    •  
     
  • Sunday, January 20, 2013 6:25 AM
     
     Answered
    Sign In to Vote
    0

    If your databases are in the same server then you can use datbasename.schema.table because they would be having connectivity.

    If not then you have to do something in DFT, use SCD....

    Please mark it as helpful if it helps.

    Thanks

    Sumit

    • Marked As Answer by Vision2040 Wednesday, January 23, 2013 3:38 PM
    •  
     
  • Monday, January 21, 2013 6:22 AM
     
     Answered
    Sign In to Vote
    0

    As the databases are on the same server you can use three part naming in your query <databaseName.SchemaName.TableName>

    However, you might want to generate se names using package configurations or project parameters. Or convert the TSQL to a stored procedure where you pass the database names as parameters.

    • Marked As Answer by Vision2040 Wednesday, January 23, 2013 3:38 PM
    •