July 2, 2012 at 2:45 am
Heads Up: This is a long post but it'll be worth it for you to read through. 🙂
So I've been working with SSIS and deploying and scheduling packages for a long time without bothering to know whether it was executed in 64 bit or 32 bit. It never occurred to me what material impact using either execution options can have until now.
I've got a whole ssis package infrastructure sheduled on SQL jobs to run on 64-bit (now i know). However for the past couple of weeks one package (out of a lot) has been failing every 3 times out of 5. This particular package is not so different from any of the numerous others. I know that no table/data structure has been modified between the source and destination. So just your regular package. Yet the package will fail sometimes and then execute sometimes. Whether it is executed in bids or via a sql job, the results are the same.
After intensive debugging inclding recreating the whole package from scratch; the package will still fail 3 times out of 5. The other couple of times the package executes successfully has not been due to any intervention from me. The package pretty much chooses when to execute successfully. This was very inconvenient to me and causing unreliability in the whole ETL process.
So another developer I work with suggested to create a seperate job-step and execute the package in 32-bit runtime. And lo presto! The package now successfully executes 5 times out of 5!!
So now I have a singular job step that executes this package alone in 32 bit runtime while the other packages continue to execute in what is 64 bit (i think?) job steps. What could be the reason(s) for this? Why should a package fail in 64 bit yet execute successfully in 32 bit runtime? When should one use either runtime options? Has anyone encountered similar?
Thanks.:ermm:
July 3, 2012 at 12:03 am
What does that package do?
Which providers are used in that package?
What were the error messages?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 3, 2012 at 2:35 am
The package loads data from a MYSQL source into a SQL destination.
Uses an ADO.NET Source Editor. The error is at the data flow source.
Error message package executed in 64 bit mode is
The component "ADO NET Source" (1) was unable to process the data. Fatal error encountered during data read.
July 3, 2012 at 2:38 am
akin.akinwumi (7/3/2012)
The package loads data from a MYSQL source into a SQL destination.Uses an ADO.NET Source Editor. The error is at the data flow source.
Error message package executed in 64 bit mode is
The component "ADO NET Source" (1) was unable to process the data. Fatal error encountered during data read.
Are you using ODBC behind the ADO.NET source?
Maybe that ODBC provider is only available in 32-bit. (or the 64-bit counterpart hasn't been installed yet)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 3, 2012 at 2:47 am
it's a .NET provider and it uses the latest version of the MySql connection manager.
And the kicker is that this package has been executing no problem for months using the 64 bit option. And the same configuration (.Net and connection manager) is used in dozens of other packages. Yet this particular package will fail 3 times out of 5 if executed in 64 bit mode. Since i switched to 32 bit execution; not 1 processing error.
July 3, 2012 at 2:52 am
Well, I'm all out of ideas 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 3, 2012 at 2:58 am
It's quite bizzare i should say.
Switching to 32 bit option was a hail mary! pass after making all sorts of debugging on the package up to creating a new data flow task.
I'll contine doing research on why this is so. Hopefully there is a kb article somewhere that might shed light on this.:unsure:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply