February 5, 2008 at 3:10 pm
It seems that SSIS will not execute a sproc within OLE DB Destination if that sproc contains a variable. I need to insert some system patch information from a legacy data extract only if the extract contains newer patch information that what already exists. Very simple sproc actually:
DECLARE @prevUpdatedatetime
SELECT @prevUpdate = MAX(last_update)
FROM account_update
WHERE account_id = @account_id
SET NOCOUNT ON;
IF @last_update > @prevUpdate
BEGIN
INSERT INTO account_update(account_id
,last_update
,patch_lev01
,patch_lev02
,patch_lev03
,patch_lev04
,patch_lev05)
VALUES(@account_id
,@last_update
,@patch_lev01
,@patch_lev02
,@patch_lev03
,@patch_lev04
,@patch_lev05)
END
Anyone have an idea on alternative method?
Thanks!!
Chris
February 6, 2008 at 1:08 am
Online Help states that the ole db destination does not support parameterized insert statements and recommends using the ole db command transformation.
February 6, 2008 at 12:44 pm
Good call. I had to use the OLE DB Command transformation. For the very new like me if you want to do an insert or update and you can't stand the silly design and ridiculous guessing involved in trying to pair "parameter0, parameter1 etc. to your input columns you can do the following:
- Add an OLE DB Command tranformation
- Connect it to your transformation with your input columns
- Create a stored proc in your destination database for your insert, update etc.
- Paste the following in the Sql Command field:
EXEC sp_account_ins ?,?,?,?,?,?,?
Obviously replace the sproc name above with the name of your sproc. Put in the number of ? marks corresponding to the number of parameters you proc has.
-When you click on the Column Mappings tab all of your parameter names from your sproc are prepopulated. Thus making mapping input columns to destination columns a snap. This may be obvious to others but I just got this today.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply