Extract All Tables

gravatar

cell

This sample demonstrates using BimlScript to generate a package that copies the data, of all tables, from a source to a destination.

published 09.07.12

last updated 09.09.12


Share

                            


<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data" #>
 
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <!-- Creates a new package for each table in the project -->
        <# foreach (var table in RootNode.Tables) { #>
            <Package Name="Extract <#=table.Name#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
                <Tasks>
                    <Dataflow Name="Copy Data">
                        <Transformations>
                            <OleDbSource Name="Retrieve Data" ConnectionName="Source">
                                <DirectInput>SELECT * FROM <#=table.Name#></DirectInput>
                            </OleDbSource>
                            <OleDbDestination Name="Insert Data" ConnectionName="Target">
                                <ExternalTableOutput Table="<#=table.Name#>"/>
                            </OleDbDestination>
                        </Transformations>
                    </Dataflow>
                </Tasks>
            </Package>
       <# } #>
        
            <!-- Creates a new package that executes each of the packages created above -->
            <Package Name="Driver - Extract All" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
                <Tasks>
                    <# foreach (var table in RootNode.Tables) { #>
                        <ExecutePackage Name="Extract <#=table.Name#>">
                            <Package PackageName="Extract <#=table.Name#>" />
                        </ExecutePackage>
                    <# } #>
                </Tasks>
            </Package>
    </Packages>
</Biml>

This BimlScript demonstrates how to automate a simple data flow across all your tables. The snippet assumes that your file or project has tables, which are then referenced from the RootNode object. The first foreach loop iterates over each table in the RootNode's Tables collection. For each table, a package is created. The <#= and #> tags are used to execute the .NET code between the tags and insert it back into Biml. Thus, each package's name includes the associated table's name. Within each package is a dataflow that uses an OleDbSource to retrieve the table's data from a Source connection and copy it to a Target connection.

The second package is a driver package that executes all the packages that have been generated. This driver package uses the ExecutePackage task to execute each table's associated package. Another foreach loop, again iterating over the RootNode's Tables collection, is used to define each ExecutePackage task.

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

Comments

gravatar

Sam7

8:13pm 12.18.17

Hallo Cell, i tried to do the same, but im not getting the Destination tables! the code is working without error, so every things is ok but im not getting the destination table, i cant find them in the destination database. am i doing something wrong?? did i forgot something?

please help me thanks