Loading Data From Excel With Biml

Kevin Feasel

2018-02-23

Biml

Ben Weissman loads an Excel file with Biml:

Did you know, that you could call GetDatabaseSchema on Excel files? You can!

Just define an ExcelConnection first:

01_Environment.biml
1
2
3
4
5
6
7
8
9
10
11
12
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <ExcelConnection Name="MyExcel" ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Flatfiles\XLS\MyExcel.xlsx;Extended Properties=&quot;Excel 12.0 XML;HDR=YES&quot;;" />
        <OleDbConnection Name="Target" ConnectionString="Data Source=localhost;initial catalog=MySimpleBiml_Destination;provider=SQLNCLI11;integrated security=SSPI"></OleDbConnection>
    </Connections>
    <Databases>
        <Database Name="MySimpleBiml_Destination" ConnectionName="Target"></Database>
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="MySimpleBiml_Destination"></Schema>
    </Schemas>
</Biml>

You can call GetDatabaseSchema on that connection and loop through the tables just like any regular database.

Click through to see what to do with this connection.

Related Posts

Importing Biml Metadata from Excel

David Stein wraps up a series on using Biml to load flat files: Make sure you read and understand the concepts in each of the following articles before tackling this one. – Import Biml Metadata with GetQuerySchema– BimlScript Code Nuggets and Mad Libs– Import Biml Metadata Directly from Excel One of the primary issues I’ve […]

Read More

Importing Biml Metadata from Excel

Kevin Feasel

2019-06-24

Biml

David Stein shows how you can take table and column data from Excel and use it to populate Biml flows: Excel Spreadsheets as a metadata source have a lot going for them.– Everyone uses Excel and is comfortable with it.– Excel is incredibly customizable and versatile.– Excel offers data validation and filtering. For these reasons, […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728