SSIS OLEDB Command fails - unknown error

Question

I keep getting an unexplainable error (see below) when I try to run a simple query in SSIS' OLEDB Command.

My query runs just fine in SQL Server, I made some adjustments to make it simpler to ensure it runs, but to no avail.

DECLARE @Gender [nvarchar](6)
DECLARE @Ready [nvarchar](12)
DECLARE @DOB [datetime]

SET @Gender=?
SET @Ready=?
SET @DOB=?


IF @Gender = 'M'
    SET @Gender='Male'
IF  @Gender = 'F'
    SET @Gender='Female'
IF @Ready='Y'
    SET @DOB=NULL

INSERT INTO [dbo].[PHI_CLIENT_PROFILES]
([col43],
[col13],
[col22],
[col10],
[origin]
)
VALUES
(@DOB, @Gender, ?, ?, ?)

ERROR MESSAGE:

Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Syntax error or access violation"

@billinkc no I am not because of that error, the param page will NOT refresh. I tried 100 different varaitions, and added/removed my code line by line so the param page is outdated (it shows 1 param because of my line by line approach). Previous to breaking the code, the param page wouldn't show anything — LearnByReading
@billinkc I should also add that the issue seems to be over the variable part. Does SSIS OLEDB COMMAN accept variables? I tried a simpler code and it worked, but I'm quite new to this task. — LearnByReading
I would start by replacing all parameters by actual values and see if this executes, then restore them one-by-one, to see if it is a specific parameter which creates the problem. — Giorgos Altanis
@GiorgosAltanis Thanks for the advice. I did just that , but unfortuantely the only way it'd work is to get rid of ALL DECLAREs and SETs. I am fairly sure I had used DECLARE/SET before in a simpler case though - but it doesn't work here for some reason — LearnByReading

Raul Sebastian Raul Sebastian · Accepted Answer · 2017-02-27T21:48:10

I don't know what you are intending there, but unless you need the inserted identities or calling some legacy function, there is no need for the OLE DB Command. Using this transformation for executing only SQL insert statements to a single table should be avoided as it will execute each row as separate batch. Try a destination component instead.

Add a derived column component and use built-in expression functionality. Example:

DerivedGender :    
    [Gender] == "M" ? "Male" : "Female"
DerivedDOB :
    [Ready] == "Y" ? NULL(DT_DBTIMESTAMP) : [DOB]
...

After doing the transformation, use an OLE DB destination component.

In some cases, it might be worth to check whether the Foreach Loop Container can solve the task.

SSIS OLEDB Command fails - unknown error

1 Answers