12

At the beginning of the project I created 3 variables with Scope size Package:

enter image description here

I then created a SQL EXECUTE TASK:

enter image description here

Checking this query on SQL SERVER returns:

enter image description here

Setting EXCEL source as the variable which will get the file location:

enter image description here

Now on SSIS, I checked both ways 64bit debugging as true and as false. But rest of execution works when it is false, therefore I changed it back to false and saved it.

enter image description here

This is the Flow of project:

enter image description here

Whenever I Execute, it gives me this error:

enter image description here

How to resolve this error. It has taken my whole day but i am still clueless about it. I am new to SSIS. Help will be appreciated.

Edited:

Please see the result set of SQL EXECUTE TASK:

enter image description here

Andy K
  • 4,944
  • 10
  • 53
  • 82
ARr0w
  • 1,701
  • 15
  • 31
  • Kindly provide the screenshot for `ResultSet` options in `Execute SQL Task` – Viki888 Mar 02 '17 at 13:52
  • How about delay validation = true on the excel connection? – manderson Mar 02 '17 at 13:58
  • You may have to adjust the excel version in the connection manager. I ran into this yesterday. Although I was pointing at an excel file that was created with the 2016 version, the author had saved the file at a lower version number. The task only worked when the excel version was set to 2007-2010 in the connection manager. – digital.aaron Mar 02 '17 at 14:48
  • http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package it is very helpful – Hadi Mar 02 '17 at 21:12
  • @manderson and @Viki888, i added the screenshot of result set. Please see and check what is the issue. I also tried after changing the `DelayValidation = False`. It is giving the same error still. – ARr0w Mar 06 '17 at 08:10
  • @Hadi , i figured that out on my own even before going dynamic and it is working when the variables are given file paths directly. I want to fetch the path from the database -> store it in variable -> use variable on excel connection. – ARr0w Mar 06 '17 at 08:38

3 Answers3

6

I noticed that the DelayValidation is False in your Excel Connection String.

You have to make DelayValidation=TRUE for both Excel Connection String and the Data Flow Task within which the excel connection is used.

Hope this would help you out.

Viki888
  • 2,686
  • 2
  • 13
  • 16
  • i added the screenshot of result set. Please see and check what is the issue. I also tried after changing the DelayValidation = False. It is giving the same error still. [See here](http://imgur.com/a/3rJxx) – ARr0w Mar 06 '17 at 08:35
4

After alot of struggle i've resolved the issue, but i am really thankful to the people who have given me some extra knowledge about this Tool and some of their guidance must have worked as well at some point as i have set my things accordingly.

what i did at last, which made it work and running were:

1) In package property, under Execution set DelayValidation to True. After following:

(Above, Viki helped me also by setting property DelayValidation to True, but in Excel Connection Manager which counts.)

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
ARr0w
  • 1,701
  • 15
  • 31
3

When Building your ExcelFilePath in an expression (or any part of it I guess), make sure the "combination of" variables contains the full path to reach the file, otherwise you will not be able to open the excel source, since it does not find a file.(should be fine at runtime).

Secondly it could be that the values that was used in the original file is not the same in the "new file/next file". Meaning excel wants to convert the column from Unicode to double-precision float or something.

Try adding this in your ConnectionString in the properties window.

IMEX=1

like "*;HDR=YES;IMEX=1";

This could help with these types of mixed columns where it contains numbers and alpha values (causing conversion issues).

HTH

Inus C
  • 1,521
  • 1
  • 16
  • 23
  • i tried your way, also see the rest of the property settings of this connection. [link](http://imgur.com/a/3rJxx) still the same error occurs. – ARr0w Mar 06 '17 at 08:30
  • Do any other excel loads work on the machine? might be a excel driver issue. Mine stopped working when I got a newer version of office installed. – Inus C Mar 06 '17 at 08:41
  • when i directly provide path directory to the variable. it works fine completely and loads data correctly in database (the same excel version with same data in same format). – ARr0w Mar 06 '17 at 08:50
  • Try changing the "ServerName" option in the expression to be "ExcelFilePath" instead. – Inus C Mar 06 '17 at 09:00
  • bro, see the image link again on the first comment of this answer. i did change it and tried this too "excel file path" – ARr0w Mar 06 '17 at 10:10