23

Ive spent hours trying to fix this issue. For the column that's giving me the problem i am using a scalar-function in sql server to grab the name. The function is correct and works fine. I then use a stored procedure to use with ssis. The column that is giving me the error is stating it has no corresponding output column on the error output. Everything seems to be fine. I have done the following per previous recommendations:

  1. Deleted source and destination and remapped everything
  2. Changed DelayValidation property to true
  3. Changed maximum error count to 100
  4. Unchecked and rechecked Available External Columns in source

These were recommendations per previous forums.

I am an intern and want to get this on my own without having to ask my manager. I have spent countless hours trying to fix the problem.

Any suggestions?

EDITING TO ADD VALUE TO QUESTION

The following is my query in my OLE DB source

SELECT *
FROM RExtenstionBase R
LEFT OUTER JOIN AExtensionBase A
ON R.ASN = A.SN
AND R.ARN = A.R

This query gives me the fields I need from Dynamics CRM.

The following is my stored procedure call in the OLE DB Command:

EXEC InsertRepairs ?,?,?,?,?,?,?,?,?,?

The following is my stored procedure script:

USE [MSCRM_RC]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertRepairs] 
    -- Add the parameters for the stored procedure here

    @ROID nvarchar(100),
    @AType nvarchar(100),
    @ARN nvarchar(100),
    @DateReceived datetime,
    @RForRR nvarchar(100),
    @C nvarchar(100),
    @SN nvarchar(100),
    @ASN nvarchar(100),
    @Performed nvarchar(100),
    @COR decimal


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @newID AS UNIQUEIDENTIFIER
    SET @newID = NEWID()

INSERT INTO [dbo].[A_RBase] /***BASE TABLE ALWAYS THE SAME ***/
           ([A_RCRIDID] /*Always change this tho */
           ,[CreatedOn]
           ,[CreatedBy]
           ,[ModifiedOn]
           ,[ModifiedBy]
           ,[CreatedOnBehalfBy]
           ,[ModifiedOnBehalfBy]
           ,[OwnerId]
           ,[OwnerIdType]
           ,[OwningBusinessUnit]
           ,[statecode]
           ,[statuscode]
           ,[ImportSequenceNumber]
           ,[OverriddenCreatedOn]
           ,[TimeZoneRuleVersionNumber]
           ,[UTCConversionTimeZoneCode])
     VALUES
           (@newID
           ,GETUTCDATE() -- CreatedOn
           ,'XXXXXXXXXXXXXXXXXXXXXXX' -- CreatedBy
           ,GETUTCDATE()                            -- ModifiedOn
           ,'XXXXXXXXXXXXXXXXXXXXXXX'   -- ModifiedBy
           ,NULL                                    -- CreatedOnBehalf
           ,NULL                                    -- Modified''
           ,'XXXXXXXXXXXXXXXXXXXXXXX'   -- OwnderId
           ,8
           ,'XXXXXXXXXXXXXXXXXXXXXXX'
           ,0                                       -- statecode
           ,1                                       -- statuscode
           ,5                                       -- ImportSequenceNumber
           ,NULL                                -- Overrid
           ,0                                       -- TimeZoneRule 
           ,NULL)                                   -- UTCConversion




INSERT INTO [dbo].[A_RExtensionBase]
           ([A_RCRID]
        ,[A_Name]
           ,[A_AType]
        ,[A_ARN]
           ,[A_DateReceived]
           ,[A_RForRR]
        ,[A_C]
        ,[A_SN]
        ,[A_ASN]
           ,[A_Performed]
           ,[A_COR])
     VALUES
           (@newID,
         @ROID,
         @AType,
         @ARN,
            @DateReceived,
            @RForRR,
         @C,
         dbo.Lookup_SNID_GUID(@SN)
         @ASN,
            @Performed ,
         @COR)


END

The following is my scalar-valued function:

USE [MSCRM_RC]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Lookup_SNID_GUID] 
(
      -- Add the parameters for the function here
      @SN_Name nvarchar(100)
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
      -- Declare the return variable here
      DECLARE @SN_GUID UNIQUEIDENTIFIER
      SELECT @SN_GUID = SNID

      FROM SNExtensionBase
      WHERE name = (@SN_Name)

      RETURN @SN_GUID
END
J.S. Orris
  • 4,653
  • 12
  • 49
  • 89
  • I've spent seconds reading this question ;) Help me understand the question. You have a query that uses an OLE DB Source. That query calls a user defined scalar valued function. Somehow, you get the error in the title of the question? Do I have the facts correct? If so, we'll probably need to see more information. Screenshot of the data flow and the exact error message are a good starting place – billinkc Jan 08 '14 at 20:11
  • I get the error when running the SSIS package. I have an OLE DB Source and a OLE DB Command in my Data Flow Task. My OLE DB command is whats using my stored procedure. I am inserting data into MS Dynamics CRM. You are correct in stating the error is the error from the title of the question – J.S. Orris Jan 08 '14 at 20:12
  • Good edit, but now that we know more about how the package works, let's focus on the OLE DB Command. Can you show how that's wired up + the code inside of it? – billinkc Jan 08 '14 at 20:32
  • Ive edited the original question to add all valid scripts for ssis package – J.S. Orris Jan 08 '14 at 21:24
  • @billinkc Sorry for the delay on adding the scripts to original question....went to lunch to gather my thoughts on this – J.S. Orris Jan 08 '14 at 21:35
  • If you manually execute your stored procedure, does that work? I'm questioning the function call from the VALUES part. That doesn't smell valid to my mental parsing of the query – billinkc Jan 08 '14 at 21:38
  • The stored procedure executes successfully. What smells funny to you? – J.S. Orris Jan 08 '14 at 21:40
  • `VALUES(@newID,@ROID,@AType,@ARN,@DateReceived,@RForRR, @C,dbo.Lookup_SNID_GUID(@SN),@ASN,@Performed, @COR)` For some reason, my brain insists that you can't call a function within the VALUES part. Instead, you'd have to use a SELECT. I could be mad on that point. Anyways, gotta get some work done but I'll gin up a POC this evening to see if I can reproduce your error – billinkc Jan 08 '14 at 21:44
  • I wrote a similar procedure and called the dbo.scalarFunction within the VALUES part and it worked fine. I actually used the previous stored procedure as a template – J.S. Orris Jan 08 '14 at 21:49
  • Im thinking there is something wrong with the scalar-function...instead of setting the metadata in CRM to string for this particular field, should I make it a lookup? – J.S. Orris Jan 08 '14 at 21:52
  • So I deleted the scalar-function call on my stored procedure and only am trying to populate the fields with no scalar-function calls. I'm still getting the same error code. I am at a loss – J.S. Orris Jan 08 '14 at 22:13

3 Answers3

108

Apparently this is a common bug with SSIS packages. I got it to eliminate the error by drilling into the OLE DB Source, clicking on the columns tab, finding the Available External Column that was included in the error message, and then unchecking the check box and rechecking it. ERROR SOLVED!

J.S. Orris
  • 4,653
  • 12
  • 49
  • 89
3

I came across with the 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 Columnns Page of the normal Editor as mentioned by Jeff.

Probably, it is worth to be mentioned that the data source of the OLEDB Source task was a modified MDS View. Microsoft CRM Dynamics 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.

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

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

Community
  • 1
  • 1
Marcus Belz
  • 181
  • 1
  • 7
0

Correct me if I'm wrong, but I've found that clicking Preview-button when getting data from source, causes wrong lengths to set for columns, especially for strings.
This might be because of length are determined from preview data got. So, better practice is to click Columns-tab in Source-dialog after selecting source-table. This is the case mainly with ODBC sources.

RonVibbentrop
  • 281
  • 4
  • 3