Loading Data From Excel With 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.