A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, October 30, 2014

Biml and Looping through excel files in SSIS

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">
        <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>
        <Package Name="LoopThroughExcel" ConstraintMode="Linear">
                <Variable DataType="String" Name="strExcelFile"></Variable>
                <Connection ConnectionName="CM_Excel">
                        <Expression ExternalProperty="ExcelFilePath" >@[User::strExcelFile]</Expression>
                    Name="FELC Iterate Excel">
                        <VariableMapping Name="0" VariableName="User.strExcelFile"/>
                            Name="DFT Do Excel"
                                    <ExternalTableInput Table="Sheet1$"></ExternalTableInput>
                                Do noting, but do it splendidly
                                <DerivedColumns Name="bit bucket"></DerivedColumns>


That should generate a package that has a control flow like

And look at that data flow!


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.".

No comments: