Error running SSIS job scheduled by SQL server agent for bulk upload.

Question

I have a SQL server agent scheduled, SSIS job for bulk data upload. SQL server is hosted
on Windows server 2008 R2 Enterprise.<o:p></o:p>

This job bulk uploads files from a specific shared folder to SQL server 2008 R2.

It checks for a certain temporary file in the folder.
In case the temporary file was found, the temporary file is deleted and data is uploaded.
After the data upload, it deletes the data files as well.<o:p></o:p>

Data files are created by another scheduled C# module.<o:p></o:p>

This job has been running successfully for couple of months, but suddenly(from last
couple of days) the SSIS job is failing with below error:<o:p></o:p>

"Retrieving the COM class factory for component with CLSID
{19E353EF-DAF4-45D8-9A04-FB7F7798DCA7} failed due to the following error:
800703fa.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly. End Error DTExec: The package
execution returned DTSER_FAILURE (1)<o:p></o:p>

I have not been able to identify the root cause behind it. <o:p></o:p>

Let me know if you need any other information regarding the issue.

Answer 1

Most probably you are facing a DLL registration corruption.

You may try these in sequence: on a 32 bit machine run

regsvr32 "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\SQLTaskConnections.dll" (path may not be exactly as shown)

or on a64bit one:

regsvr32 "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\SQLTaskConnections.dll"

And then (for say a 32 bit OS):

regsvr32 "C:\Program Files\Microsoft SQL Server\100\dts\binn\dts.dll"

regsvr32 "C:\Program Files\Microsoft SQL Server\100\dts\binn\MsDtsSrvrUtil.dll"

---

Arthur My Blog