I recently had to develop an SSIS package that took Excel files from a different server, import them into a SQL Server table, and process the information from them. Normally, not a hard thing to do. To complicate matters, the name of the Excel files was always unknown and ever changing, and there was no consistency in what the worksheet was named. Normally I’d create a generic connection and dynamically change the connection properties in a script task, but the added complexity of having variable worksheet names made that unbearably difficult.
I solved the process by creating a ForEach loop looking for any Excel files in the landing folder. I elicited the help from a colleague to develop a C# Script Task (I know Java and VB.NET, but C# is a little iffy for me) to convert that file, regardless of what the worksheet is named, to an XML datatable, from which I could process into a landing table in SQL Server via a stored procedure. In order to accomplish this, given that we’re creating an Oledb connection string to Excel in the C# script, was to make sure the SSIS package was running in 32 bit runtime.
Running the package manually posed no problem. Worked like a charm. I built the package and deployed it and created a SQL Server Agent job to run this package once a day to process any new files that arrived in the landing folder. The package would fail each time it ran. Thinking it was a permissions issue on reading the file, I made sure the SQL Server Agent Service account had the necessary permissions to access and read the file. After verifying this, I ran the package again through the SQL Server Agent and it still failed. I then looked at the new job I created to see if I had missed anything. Turns out I did. For the step that actually executes the package, I missed one important part. I opened the job, and clicked Edit on the step I needed to look at. It turns out I missed one little important check box in the Executions Options tab of the job step.
The step that executes the package that needs to run in 32-bit runtime also needs to run in 32-bit runtime. Once I fixed that and saved the changes, the job worked like a charm.
In conclusion, I guess the lesson from this is to make sure your settings are consistent throughout your whole process of design and deployment. I learned something new today, and I’m glad I did. This will be very useful in the future.