none
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.  
    Tuesday, July 23, 2013 1:46 PM

All replies

  • Does this happen every time?

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 23, 2013 1:50 PM
  • yes, you are correct 

    at every first attempt sql job throwing error and at second attempt job run successfully but file is not imported/archived in DB 


    Wednesday, July 24, 2013 5:55 AM
  • Hi Himanshu,

    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.

    To work around this issue, please see the following links:

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, July 30, 2013 8:06 AM
  • Hi Himanshu,

    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.

    To work around this issue, please see the following links:

    This would give a  totally different error in SSIS.
    Also, this doesn't explain why it runs "sucessfully" the second time but without importing the data.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 30, 2013 8:20 AM
  • 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.


    • Edited by Prajesh Tuesday, July 30, 2013 12:25 PM better
    Tuesday, July 30, 2013 12:25 PM
  • 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.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 30, 2013 12:38 PM
  • Hi Koen

    The text is from 

    http://support.microsoft.com/kb/189897


    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.

    Tuesday, July 30, 2013 12:47 PM
  • Hi Koen

    The text is from 

    http://support.microsoft.com/kb/189897


    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.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 30, 2013 1:00 PM