February 1, 2012 at 3:55 am
I am trying to either UPDATE or INSERT records from a dataflow using an OLE DB Cmd Task firing a stored procedure with parameters.
SSIS seems to try and guess the external columns and does not seem to do a good job at it.
In particular, it seems easily fooled by optional parameters to which I supply a default value in the stored procedure definition.
CREATE PROCEDURE MyProcedure @Debug BIT = 0, @MyRealParam1 INT, @MyRealParam2 VARCHAR(99)...
Could someone provide a "strategy" to try and fix this temperamental tool?
I spend an awful amount of time trying to get round this clunkiness...
In the example above, I seemed to get through by shifting my @Debug parameter at the very end in my procedure definition...
Surely, there must be a more scientific approach to this! :crazy:
February 1, 2012 at 6:11 am
You can create a dummy result set at the beginning of the stored procedure that matches the column names and data types of your actual result set. Just remember to change this whenever you make changes to the actual code
IF 1=2
SELECT CAST(NULL AS TINYINT) AS Column1
,CAST(NULL AS DATETIME) AS Column2
,CAST(NULL AS NUMERIC(16,0)) AS Column3
,CAST(NULL AS VARCHAR(30)) AS Column4
...
This will also work to resolve the same issue in SSRS.
February 1, 2012 at 7:45 am
Actually, my problem was very different from what I thought...
The OLE DB Cmd was producing an error and redirected the rows with 2 new columns ErrorCode and ErrorColumn.
Unfortunately, there were already 2 columns called ErrorCode and ErrorColumn because this flow was already the error output of a Lookup...
Of course, I was looking at the wrong Error Code and Error Columns, hence the error was complaining about having no lookup row...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply