Problem importing Excel files when fields can be more or less than 255 characters

• Realizm999

  SSC Rookie

  Points: 33

  More actions

  August 11, 2011 at 10:23 am

  #255383

  Hi all,

  I’ve been struggling with a problem for a few days now and hoping someone may be able to shed some light on it.

  What I would like to do is import a series of Excel 2007 files into a SQL 2005 table.

  Having very little experience with SSIS, I figured this would be an ideal project to get acquainted with it thought it would be fairly straight forward.

  The excel files have 11 columns and the data in all but 1 is either date/time or text fields that contain less than 255 characters. The problem I’m encountering is with the 1 column (let’s call it “Details”) that is a text column which can be variable in length…sometimes it can be blank; sometimes it contains less than 255 characters and sometimes it contains more than 255 characters.

  I’ve set up a SQL table and the column that “Details” maps to is nText.

  I’ve set up a Foreach Loop container to loop through all the .xlsx files.

  I’ve set up a connection manager for the source .xlsx files. This uses a variable for the filename and an expression for the connection string:

  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Filename] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

  So the problem seems to be as follows:

  File 1: 1 of the fields in the “Details” column has more than 255 characters. The data type is correctly set to DT_NTEXT and everything writes fine to the destination DB table.

  File 2: 1 of the fields in the “Details” column has more than 255 characters. The data type is correctly set to DT_NTEXT and everything writes fine to the destination DB table.

  File 3: All of the fields in the “Details” column have LESS than 255 characters. Package execution fails with the following errors:

  [OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

  [OLE DB Source [1]] Error: Failed to retrieve long data for column "Details".

  [OLE DB Source [1]] Error: There was an error with output column "Details" (88) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".

  I’ve tried a few things without success including using Data Conversion transformation and a Derived Column transformation (basically to try and convert DT_WSTR to DT_NTEXT). Again…I don’t have much experience with SSIS so perhaps I did something wrong here.

  Perhaps there is a way to use a Conditional Split to evaluate the data type (just not sure what the expression syntax would be for this).

  Has anyone run into this issue and if so, would you mind sharing the solution?

  Thanks in advance for your time

• graeme.wright-650064

  Newbie

  Points: 9

  More actions

  September 5, 2011 at 9:22 am

  #1377073

  I had the same problem, found the solution at [/url]

  Graeme