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

Improve this question
6
  • Kindly provide the screenshot for ResultSet options in Execute SQL Task Commented Mar 2, 2017 at 13:52
  • How about delay validation = true on the excel connection? Commented Mar 2, 2017 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. Commented Mar 2, 2017 at 14:48
  • stackoverflow.com/questions/7411741/… it is very helpful Commented Mar 2, 2017 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. Commented Mar 6, 2017 at 8:10

3 Answers 3

Reset to default
6
+50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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
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.)

Improve this answer

1 Comment

you have to accept his answer if he is the one who solved our issue
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

Improve this answer

5 Comments

i tried your way, also see the rest of the property settings of this connection. link still the same error occurs.
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.
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).
Try changing the "ServerName" option in the expression to be "ExcelFilePath" instead.
bro, see the image link again on the first comment of this answer. i did change it and tried this too "excel file path"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.