Tuesday, October 9, 2012

BIML Transfer DataBase Objects

Quick demo on moving tables and other object between MS-SQL environments:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Transfer.DataBase.Objects.biml
            Demo on transferring database objects
            between MSSQL environments
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <Connections>
        <SqlServerManagementConnection
            Name="SSMCLocalHostMSSQL2012"
            SqlServerName=".\MSSQL2012"
            UseWindowsAuthentication="true"></SqlServerManagementConnection>
            </Connections>
    <Packages>
        <Package Name ="Transfer.DataBase.Objects" ConstraintMode="Linear">
            <Tasks>
                <TransferSqlServerObjects
                    Name ="TransferSqlServerObjects"
                    SourceConnectionName="SSMCLocalHostMSSQL2012"
                    SourceDatabase="AdventureWorks2012"
                    DestinationConnectionName="SSMCLocalHostMSSQL2012"
                    DestinationDatabase="AdventureWorksDW2012"
                    DropObjectsFirst="true"                   
                    CopyData="true"
                    CopyAllTables="false">
                    <Annotations>
                        <Annotation>
                            Notation for tables:
                            [schema].[TableName]
                            (including bracktes}
                            Target schema must exist!
                        </Annotation>
                    </Annotations>
                    <Tables>
                        <Table>[HumanResources].[Department]</Table>
                        <Table>[HumanResources].[Employee]</Table>
                        <Table>[HumanResources].[EmployeeDepartmentHistory]</Table>
                    </Tables>
                </TransferSqlServerObjects>               
                                         
            </Tasks>
        </Package>
    </Packages>
</Biml>

The biml can als be downloaded here: Transfer.SqlServerObjects.biml

This will get you:

image

Till Next Time

This article is also published on www.bimlscript.com

No comments:

Post a Comment