Biml and Looping through excel files in SSIS

I came across a question on DBA.StackExchange.com and they wanted to know why they were getting an error about the file being locked. My fine compatriots already suggested the questioner use ProcMon but the person was rather addament that it wasn't open. psssst it's open I cover the steps on file in use by another process.

Where's the biml and Excel?

Easy with the pitchforks... The questioner was implementing Mike Davis's Loop Through Excel Files in SSIS and I figureed since I didn't have a Biml post covering Excel yet, I'd pop one out.

Biml and Excel

The only oddity to make note of is the connection string. Normally, your connection string would look something like Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ssisdata\Excel\USCustomers1.xls;Extended Properties="Excel 8.0;HDR=YES"; However, you're going to need to escape those double quotes and your normal tricks of "" or \" aren't going to work here as that's xml we're dealing with. Instead, you'll need to use "

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <ExcelConnection ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ssisdata\Excel\USCustomers1.xls;Extended Properties=&quot;Excel 8.0;HDR=YES&quot;;" Name="CM_Excel"></ExcelConnection>
    </Connections>
    <Packages>
        <Package Name="LoopThroughExcel" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="strExcelFile"></Variable>
            </Variables>
            <Connections>
                <Connection ConnectionName="CM_Excel">
                    <Expressions>
                        <Expression ExternalProperty="ExcelFilePath" >@[User::strExcelFile]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Tasks>
                <ForEachFileLoop 
                    Folder="C:\ssisdata\Excel" 
                    FileSpecification="*.xls" 
                    ConstraintMode="Linear" 
                    Name="FELC Iterate Excel">
                    <VariableMappings>
                        <VariableMapping Name="0" VariableName="User.strExcelFile"/>
                    </VariableMappings>
                    <Tasks>
                        <Dataflow 
                            Name="DFT Do Excel"
                            DelayValidation="true">
                            <Transformations>
                                <ExcelSource 
                                    ConnectionName="CM_Excel" 
                                    Name="XL_SRC">
                                    <ExternalTableInput Table="Sheet1$"></ExternalTableInput>
                                </ExcelSource>
                                <!--
                                Do noting, but do it splendidly
                                -->
                                <DerivedColumns Name="bit bucket"></DerivedColumns>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Success

That should generate a package that has a control flow like

And look at that data flow!

Error

Now, back to the original question. If I open that file in Excel and then run the package, YOU WON'T BELIEVE WHAT ERROR MESSAGE I RECEIVE.</Linkbait>

Error: 0xC0202009 at LoopThroughExcel, Connection manager "CM_Excel": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".