How to fix SSIS : "Value, does not fall within expected range"?

A

12

15

When I open up the solution that contains SSIS packages created by a colleague, I get this awkward error that tells me nothing about what I'm supposed to do to fix it.

He left instructions to take all the "variables" out of the connection string in the dtsx file manually before opening up the solution. I have done that, now when try to view the package in the designer I just get an image of a red x and this message.

EDIT: You cannot see any design elements, no tabs across the top to switch to errors or data flows. Just a gray center area on the screen with a red x, and the message, its like VisualStudio dies in the process of reading the dtsx file.

Achromat answered 29/9, 2009 at 14:57 Comment(2)

Can you please provide a bit more information. What component type is displaying the red x? Data Source? Data Conversion? Destination? What other components are listed in the Data Flow and in what order? – Menology 29/9, 2009 at 16:36

it doesn't even get that far, its just hosed, a gray area in the design surface with this x and the message, I almost thought this is what it was supposed to do, and then I created a new project from scratch and my new stuff works, but nothing that our other developer did loads. – Achromat 29/9, 2009 at 17:10

D

7

The question is rather unspecific so it’s of course difficult to get on the right track here. All of the given answers focus different issues. I would say that PeterX had the best guess. The reason for the error could be as simple as a modified data source.

I came across with a bug "error output has no corresponding output" quite often when adding a new column to a table that needs to be processed by an existing SSIS package. This bug came along with an error message saying that a "Value does not fall within the expected range".

A newly added column needed to be processed by an existing SSIS Package. The expected behavior is that SSIS will recognize that there is a new column and select this column on the columns page of the OLEDB Source Task SSIS to be processed. However, when opening the OLEDB Source Task for the first time after having modified the table I got twice the following error message: "Value does not fall within the expected range." The error message showed up when opening the editor and when opening the Columns page of the editor. Within the Advanced Editor of the OLEDB Source Task the new column showed up in the OLEDB Source Output Columns Tree, but not in the OLEDB Source Error Output Columns Tree. This is the actual underlying problem of the error message. Unfortunately, there seems to be no way to add the missing column manually.

To solve the problem, remove and re-add the newly added column on the Columns Page of the normal Editor as mentioned by Jeff.

It is worth to be mentioned that the data source of the OLEDB Source task was a modified MDS View. Microsoft CRM Dynamics – as mentioned in the related thread – is using views, too. That leads me to the conclusion, that using views as a data source may produce either of the above mentioned errors, when modifying datatypes or adding/removing columns.

The described workaround refers to Visual Studio 2008 Version 9.0.30729.4462 QFE with Mircorsoft.NET Framework 3.5 SP1. The database is SQL Server 2008 R2 (SP2).

Drought answered 18/7, 2016 at 20:55 Comment(1)

I got this same error when I add a new column in a SQL Command source. I got the Value does not fal error as a warning and the non-error output error in the design gui. Your suggestion to uncheck the column in the Column picker and then select it cleared it for me, tks. – Daysidayspring 22/8, 2016 at 12:0

Z

4

I had to delete and recreate the OLE DB Data source in my Data Flow - this is where I got the error. I also noted I had to "re-select" the "OLE DB connection manager" in the drop-down-list to force it to recognise the new connection.

This was probably a combination of getting the solution from TFS (where I noticed the data-sources didn't come-across properly and it complaining about a missing connection GUID) and/or copying and pasting the elements from another package.

(For BIDS 2008).

Zorine answered 11/7, 2013 at 4:50 Comment(0)

H

3

I had this issue for my OLE DB Source component with an SQL command after adding new columns to the database, and it wouldn't let me select columns or anything else to add the new columns.

I'm working with an Oracle database, and the only way I could get it to update was to change the SQL query to select 1 from dual, and preview it. Then revert it back to my old query.

Humanoid answered 8/11, 2016 at 3:36 Comment(1)

This fixed it for me in SSIS connected to SQL Server 2012. Exact same circumstances: I added new columns to the SQL, but then could not preview or click OK. I changed the query to select 'hello' as test, then previewed, put my SQL back, and problem solved. – Psychoanalysis 10/9, 2021 at 18:18

A

2

You get a similar message if someone uses EncryptAllWithUserKey as the ProtectionLevel. However, I believe the message is slightly different (even though you get a grey design surface with a red X).

Have you tried viewing the file in Notepad? Is it just a series of GUIDs or is there anything in it that is humanly readable? If it doesn't have any readable code, then it was probably encyrpted with the user key.

If the employee deployed the packages to a server and used SQL Server as the deployment destination (not File System or SSIS Pacakge Store) then you can download the packages to your machine. Just connect to the SQL Server Integration Services engine, expand Stored Packages, expand MSDB, expand the relevant folder, right-click on the package, and click Export Package. Save the file on your local machine and open it. The package will probably lose annotations and pretty formatting, but otherwise it should be identical to what the employee deployed.

Adenectomy answered 30/9, 2009 at 22:22 Comment(0)

S

2

I just struck the same issue. After flailing about for a bit, I found the solution was to edit the Solution Configuration.

The Solution Configuration appeared to have a matching Project configuration, as shown:

Solution Property Pages

However clicking the drop-down arrow for that Project (SSIS-Advance in this example) revealed that there was no Project Configuration for that project called Production - Sub Reports. I'm not sure how that came about - this Solution has a 7-year history and many developers.

Anyway once I created a New Project configuration (using that same drop-down menu), it is all happy now.

Serialize answered 13/6, 2014 at 6:57 Comment(0)

M

2

If it has Oracle data sources, you may need to install the Microsoft Connectors v4.0 for Oracle by Attunity: https://www.microsoft.com/en-us/download/details.aspx?id=52950

I also had to use VS 2015 - the version originally used to create the project and package.

I had this exact problem and installing these connectors and using VS 2015 fixed the issue.

Miniature answered 14/9, 2017 at 21:6 Comment(0)

P

1

I had this occur as well when I tried to call a stored procedure with OUTPUT parameters with OLE DB.

I found this: http://sqlsolutions.blogspot.com/2013/04/ssis-value-does-not-fall-within.html, which resolved my issue. The relevant action was to rename the SSIS parameter mappings to '0', '1', etc.

So for example, when calling dbo.StoredProc @variable0 = ?, @variable1 = ? OUTPUT, @variable2 = ?;, in the parameter mapping dialog, you would name the parameters '0', '1', 2' to correspond to those. Ah, SSIS <3

Pedigo answered 19/6, 2017 at 21:14 Comment(0)

C

1

I get this when I do not follow the convention for parameter naming, e.g. not name parameters 0,1,2,... in the right order for OLE DB connections. The details are documented here.

Conservator answered 11/1, 2019 at 0:2 Comment(0)

S

0

In your connection manager, convert your connections to package level instead of project level

Syllogism answered 30/8, 2022 at 2:33 Comment(0)

P

0

Delete connection manager and re-create and setup ssis package solve the problem.

Prognostication answered 30/8, 2022 at 4:56 Comment(1)

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Mosier 3/9, 2022 at 19:35

S

0

In my case, when I am deploying the .ispac file into Integration Service Catalog, at the Protection level step I am getting this error 'Value does not fall within the expected range. (Microsoft.sqlserver.manageddts)

As my package contains the Kingswaysoft component and on the deployment server, SSIS Integration Toolkit for Microsoft Dynamics 365 (64-bit) was not installed. This is the main cause of getting the error message.

Thanking you

Strongroom answered 5/7, 2023 at 11:9 Comment(0)

J

-1

I got this issue after I Add Existing Connection Manager in a SSIS project. I was just importing a Project Connection Manager from a different project (.conmgr) to my project. My solution to fix the issue was:

Deleting the imported .conmgr
Recreating it from scratch

Jockstrap answered 21/1, 2021 at 14:55 Comment(0)

Recommended topics

Hot tags