Copy All Data from Live Source Database


Scott Currie

This snippet demonstrates how to use Bimlscript to create a package that will read a source database and produce dataflow tasks to copy all of its tables.

published 08.01.13

last updated 08.01.13



<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
// This is inelegant.  We would normally use an approach similar to where we reference an existing connection.
// In this case, we are instantiating a new connection in code, so that the sample is entirely self-contained in one file.
var sourceConnectionString = "Provider=SQLNCLI10;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DataPatterns"; 
var connection = new AstOleDbConnectionNode(null) { ConnectionString = sourceConnectionString }; 
var importResult = connection.ImportDB("","",ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);#>
<Biml xmlns="">
        <OleDbConnection Name="Source" ConnectionString="<#=sourceConnectionString #>" />
        <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI10;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DataPatterns" />
        <Package Name="Copy All" ConstraintMode="Parallel" AutoCreateConfigurationsType="None">
                <!-- Creates a new dataflow for each table in the target DB -->
                <# foreach (var table in importResult.TableNodes) { #>
                <Dataflow Name="Copy <#=table.Name#>">
                        <OleDbSource Name="Retrieve <#=table.Name#>" ConnectionName="Source">
                            <DirectInput>SELECT * FROM <#=table.SchemaQualifiedName#></DirectInput>
                        <OleDbDestination Name="Load <#=table.Name#>" ConnectionName="Target">
                            <ExternalTableOutput Table="<#=table.SchemaQualifiedName#>"/>
                <# } #>

In previous snippets such as 'Import Database Assets into Biml' and 'Extract All Tables,' we saw examples of how to do a simple copy of all tables in a project and how to directly access a live database from Biml. In this snippet, we will bring the two examples together to directly copy all tables from a source database into a target, without any required fore-knowledge of the source schema. This sample could be supplemented with a second script that would create the target schema prior to attempting the copy. For an example of that, check out 'Use GetTableSql() Utility Extension to Create T-SQL CREATE TABLE Scripts.'

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.


There are no comments yet.