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:
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?
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
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!