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"

Answer 1

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.