Asked by:

xls file is not imported in sql job and gives error

Question

Code: 0xC0047039 Source: Import pdy File Detail & Footer Description: SSIS
Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Are there columns greater than 255 characters in the Excel file? If so, it seems to be the known issue:

SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will write a logic to build the table's logic for the package. If the length of some data is not in the 8 rows, then SSIS won’t respond to
it.

Are there columns greater than 255 characters in the Excel file? If so, it seems to be the known issue:

SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will write a logic to build the table's logic for the package. If the length of some data is not in the 8 rows, then SSIS won’t respond to
it.

Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters.
The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column.

Even though you can change the Rows To Scan value in the ODBC Microsoft Excel Setup dialog box to something higher than 8 (but not higher
than 16) this value is not being used by Excel. The Excel ODBC driver uses the TypeGuessRows DWORD value of one of the following registry key to determine how many rows to scan in your data:

Excel 97

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters.
The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column.

Even though you can change the Rows To Scan value in the ODBC Microsoft Excel Setup dialog box to something higher than 8 (but not higher
than 16) this value is not being used by Excel. The Excel ODBC driver uses the TypeGuessRows DWORD value of one of the following registry key to determine how many rows to scan in your data:

Excel 97

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

First of all, where do you get this idea that it is caused by a column width larger than 255?

Secondly, it is not the Excel ODBC driver - such a thing doesn't exist as far as I know - it is the ACE OLE DB or the JET OLE DB. The first one is actually the connectivity engine for Access.

Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

That must be massively deprecated by now. It's talking about Excel 2000 and earlier. I believe they replaced the JET ODBC with the JET OLE DB in Office 2003. SSIS supports Excel starting from 97 till 2003, but it uses the OLE DB provider. This provider has
the same flaws however, but I'm still not convinced this has anything to do with this thread.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.