SSIS Excel Import Error

  • John524

    Old Hand

    Points: 329

    Hello,

    I'm an SSIS newcomer, but, far as I can tell, have done everything correctly.  In a nutshell, I need to search a folder for Excel files and bring them into a table on the server.  I also need to add a SheetName field as well as a FileName field.  These 2 fields are ready to go in the table on the server.  I basically followed along with a wonderful Youtube video and, practically speaking, it works flawlessly.  I say practically speaking, because when I create my own (trial) Excel files, it works great, but when I try the same thing with the production Excel files, it errors out every time.  "Exception has been thrown by the target of an invocation"  (including some ugly system messages below that)  For the last 10 hours or so, I've been Googling and going through every scenario I can imagine.... making the datatype nvarchar, making all fields text fields, removing fields...  I have to think it's a matter of the Excel file in question, and was hoping someone had a suggestion.  I'm at a loss.  Again, there doesn't seem to be an issue with the code (adding the SheetName and FileName fields) but thought I'd throw that in just in case.

    thank you for your time and any suggestions you might have!

  • Phil Parkin

    SSC Guru

    Points: 243681

    I sympathise!

    On which component is the error happening, exactly?

    Are your production files stored locally, or on a network drive somewhere?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John524

    Old Hand

    Points: 329

    thanks Phil,

    I just stepped through this and it hung up at adp1.Fill(dt1)

    Also, the pop out box says 'OleDBException was unhandled by usercode'

    ..then

    System.Data.OleDb.OleDbException was unhandled by user code. An Exception type of 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code.

    ...the folder it's pulling from is just on my C(desktop)  This is the same folder I put the trial Excel in as well as the production excel (both .xlsx)

     

    thanks again!

  • Phil Parkin

    SSC Guru

    Points: 243681

    Are you using code (rather than a data flow) to transfer the data? If so, please explain why. And consider adding some TRY/CATCH error handling to help you diagnose the error.

    Are the sheet names and column names exactly the same between trial and prod files?

    If you cut & paste data from prod to trial and then import trial, does that work?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John524

    Old Hand

    Points: 329

    interesting.  Maybe a Data Flow Task is in order.  Yes, the example I followed (one of the few that seemed to suit my needs) used a Script, and inside the Edit Script, I Copied & Pasted his sample code which I only needed to modify slightly.

    The sheet names differ, but the field names are identical.

    The Cut and Paste into the trial does not work at all... one of the first things I tried

    thanks Phil

  • Phil Parkin

    SSC Guru

    Points: 243681

    Sheet names being different is critical ... unless you are handling it dynamically in your package, which is a little advanced.

    Try renaming the sheet after cut & paste from trial to prod.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John524

    Old Hand

    Points: 329

    yeah, far as I can tell, the sheet names can be whatever.  Basically, let's say the Excel file is called Pets_01012019.  Within that workbook, there is a worksheet 'Cats' and another 'Dogs'  Lets say within those worksheets there is PetID and PetName.  After running the package, the table now has these fields with these values (sorry, this will look a bit scrappy...)

    PetID     PetName     FileName     SheetName

    1          FiFi               Pets_01012019     Cats

    2         Fluffy            Pets_01012019     Cats

    3        Rover            Pets_01012019     Dogs

    4       Fido               Pets_01012019     Dogs

     

    ...I just can't understand why this works great until I try that Excel file.... it's like it's poisonous.  I can even create my own file, and even the headers, but as soon as I paste the data, it goes awry.  BTW, I did try renaming the sheets after the C & P to no avail

    thanks again for the help!

  • Phil Parkin

    SSC Guru

    Points: 243681

    Can you create a couple of screenshots & paste them in, as follows:

    1. Showing the trial Excel file, with headings & first 10 rows
    2. Showing the prod Excel file, with headings & first 10 rows

    • This reply was modified 6 days, 3 hours ago by  Phil Parkin. Reason: Fix typo

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John524

    Old Hand

    Points: 329

    will do.... I'll need to sanitize it a bit since there's sensitive info, but in essence, should be the same...

    thank you

  • palandri

    SSC Veteran

    Points: 217

    If the "Exception has been thrown by the target of an invocation" error is coming from a script task or component, set a break point at the start of the script and step through it until you find the code that throws the errors.

    Also, make sure that there are no "phantom" columns in the production workbooks that cause the error.  By phantom, I mean that even though there appear to be, say three, columns in the workbook when looking at the column heading or the last populated column, there could be a space or other character in one of the rows in column D, or further to the right.  A couple things that might help.  First, save a copy of the problem workbook as .csv, and examine in a text editor, looking for rows that end in a comma, including the heading row.  You will only be able to save one worksheet at time to .csv, so you might need to make copies of the workbook each containing one sheet, and save them as .csv,  You can also, if the worksheet contains three columns, select column D through, say Z, and delete them, which will remove the phantom columns.

  • John524

    Old Hand

    Points: 329

    Thanks Phil (and also Palandri)  It was funny, as I was getting ready to do screenshots and such, I tried altering just one thing... that was, the individual Sheet Names contained spaces.  For kicks and giggles, I took the spaces out, and lo and behold... it ran.  I guess I'll need to either have the production sheet names changed, or look deeper into the underlying code.

    thank you so much!

  • Phil Parkin

    SSC Guru

    Points: 243681

    So this bit

    BTW, I did try renaming the sheets after the C & P to no avail

    wasn't quite true? 🙂

    Changing your code to accommodate the space character should be trivial.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John524

    Old Hand

    Points: 329

    well.... renaming them let's say 'All Dogs' to 'All Dogs' didn't seem to work, but renaming them 'All Dogs' to 'AllDogs' did work.  If that's the point you were trying to get across to me, I apologize.  I thought you just meant to rename after the cut and paste (to what it was named before)

    BTW, trivial to you and trivial to me may be entirely different trivials..... can u please tell me (from the below C&P) how I'd do that?

    ("select " + SQLColumnList + ",'" + filename + "' AS FileName" + ",'" + sheetname + "' AS SheetName from [" + sheetname + "]", cnn1)

     

    many, many thanks

  • Phil Parkin

    SSC Guru

    Points: 243681

    No apologies are needed here!

    What I assumed you did was as follows:

    a) Have a sheet called AllDogs in your trial spreadsheet ... imports successfully.

    b) Have a sheet called (whatever) in your prod spreadsheet

    c) Create a new spreadsheet, cut & paste from prod spreadsheet, name the sheet 'All Dogs' ... fails to import.

    Regarding the code, as I (almost) always use data flows to import data, I'm not certain of the syntax required. Your syntax, however, looks to me as though it should accommodate spaces as it stands. Are you certain that this is what is generating the error?

     

     

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John524

    Old Hand

    Points: 329

    yes, it does seems that (the spaces) was somehow the issue, at least far as I can tell.  In fact, I didn't recreate anything this time.  I just took the Excel production file (which I pull from an email and place into a folder), removed the spaces in the worksheet names, and everything went in swimmingly.  From what I know, I do agree the code would seem to allow for spaces(?)  I noticed that the table field SheetNames came out as AllDogs$, but I guess that's just how SQL server and Excel do things

    thank you

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply