CODEWITHSUNDEEP
sql-serverexcelvariablesssisfilepath
ARr0w
ARr0w

Reputation: 1731

SSIS Variable use issue in Data Flow

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

Upvotes: 12

Views: 938

Answers (3)

ARr0w
ARr0w

Reputation: 1731

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

Upvotes: 4

Inus C
Inus C

Reputation: 1551

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

Upvotes: 3

Viki888
Viki888

Reputation: 2774

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.

Upvotes: 6

Related Questions