SSIS Variable use issue in Data Flow

Question

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

I then created a SQL EXECUTE TASK:

Checking this query on SQL SERVER returns:

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

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.

This is the Flow of project:

Whenever I Execute, it gives me this error:

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:

Firework

Kindly provide the screenshot for ResultSet options in Execute SQL Task – Caribbean

How about delay validation = true on the excel connection? – D

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. – Roband

#7412241 it is very helpful – Coumarone

@D 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. – Firework

@Coumarone , 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. – Firework

Answer 1

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.

Caribbean

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 – Firework

Answer 2

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

Firework

you have to accept his answer if he is the one who solved our issue – Patsypatt

Answer 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

Fireboard

i tried your way, also see the rest of the property settings of this connection. link still the same error occurs. – Firework

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. – Fireboard

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

Try changing the "ServerName" option in the expression to be "ExcelFilePath" instead. – Fireboard

bro, see the image link again on the first comment of this answer. i did change it and tried this too "excel file path" – Firework