How to import a column from SQL into Excel via BIML

Question

I would like to import column 'Street' (NVARCHAR(50)) from a SQL table (Practice2.dbo.Adress) into Excel (ExcelDestination.xls). I know how to do this in SSIS, but in BIML I can't seem to find the right code, especially to do the column mapping between source and destination. When I try to generate the SSIS package, I get the error

"Could not resolve reference to 'Adress' of type 'TableResource'. 'TableName="Adress"' is invalid. Provide valid scoped name."

Here is what I have done so far:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
    <OleDbConnection Name="ConnectionWithPractice2" ConnectionString="Provider='SQLNCLI11'; Data Source='DWH'; Initial Catalog='Practice2'; User Id='system'; Password='password';"></OleDbConnection>
    <ExcelConnection Name="Excel Connection Manager" ConnectionString="Provider='Microsoft.Jet.OLEDB.4.0';Data Source='C:\Users\adm-jpna\Documents\ExcelDestination.xls';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'">
    </ExcelConnection>
</Connections>
<Packages>
    <Package Name="Package1">
        <Tasks>
            <Dataflow Name="ImportIntoExcel">
                <Transformations>
                    <OleDbSource Name="OLE_DB_Source" ConnectionName="ConnectionWithPractice2">
                        <DirectInput>SELECT Street FROM Practice2.dbo.Adress</DirectInput>
                    </OleDbSource>
                    <ExcelDestination Name="Excel_Destination" ConnectionName="Excel Connection Manager">
                        <Columns>
                            <Column SourceColumn="Street"></Column>
                        </Columns>
                        <TableOutput TableName="Adress"></TableOutput>
                    </ExcelDestination>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>

billinkc billinkc · Accepted Answer · 2017-07-12T17:59:34

I made a few minor changes to your ExcelDestination

        <Package Name="so_45063165">
            <Tasks>
                <Dataflow Name="ImportIntoExcel">
                    <Transformations>
                        <OleDbSource Name="OLE_DB_Source" ConnectionName="ConnectionWithPractice2">
                            <DirectInput>SELECT N'123 Oak' AS Street;</DirectInput>
                        </OleDbSource>
                        <ExcelDestination Name="Excel_Destination" ConnectionName="Excel Connection Manager">
                            <ExternalTableOutput Table="Sheet1$" />
                        </ExcelDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>                
        </Package>

TableOutput refers to the Biml collection of Tables. You're looking for ExternalTableOutput which instructs the engine to look to the referred object (Excel in this case) to validate that it exists. The change in tag results in the property changing from TableName to Table and then since we're referencing a worksheet and not a table, we need to specify as such with a $. Sheet1 would be a table or named ranged while Sheet1$ means the actual worksheet.

Since you didn't provide a column mapping between your source Street and a target column, I removed the tags.

How to import a column from SQL into Excel via BIML

1 Answers