October 8, 2019 at 1:41 pm
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!
October 8, 2019 at 1:56 pm
I sympathise!
On which component is the error happening, exactly?
Are your production files stored locally, or on a network drive somewhere?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2019 at 2:21 pm
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!
October 8, 2019 at 2:37 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2019 at 2:58 pm
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
October 8, 2019 at 3:17 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2019 at 3:37 pm
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!
October 8, 2019 at 3:49 pm
Can you create a couple of screenshots & paste them in, as follows:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2019 at 3:52 pm
will do.... I'll need to sanitize it a bit since there's sensitive info, but in essence, should be the same...
thank you
October 8, 2019 at 3:57 pm
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.
October 8, 2019 at 4:36 pm
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!
October 8, 2019 at 4:52 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2019 at 5:10 pm
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
October 8, 2019 at 5:31 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2019 at 6:49 pm
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 15 total)
You must be logged in to reply to this topic. Login to reply