8

I am using an OLE DB command in SSIS for which the SQL command looks like this:

UPDATE DBO.CLIENT
SET
    TimeZoneID = ?,
    DaylightSavingTime = ?,
    ModifiedBy = ?,
    MicrosPropertyID = ?,
    IsOffline = ?,
    GlobalReporting_MaskPatronNumberType = ?,
    GlobalReporting_PatronNumberReadableCharacters = ?,
    GlobalReporting_MaskPrimaryCardType = ?,
    GlobalReporting_PrimaryCardReadableCharacters = ?,
    BICAddedDateTime = ?,
    BICUpdatedDateTime = ?,
    IsDBInDailyBoardRate = ?
WHERE 
    ClientID = ?

When I try to do the column mappings, these parameters show up as PARAM_0, PARAM_1, PARAM_2, PARAM_3, etc. Is there a way to get them to show up as the column name?

Raj More
  • 47,048
  • 33
  • 131
  • 198

2 Answers2

5

I found that SSIS will read column names from a stored procedure instead of the SQL command for the OLE DB command!

It will however, not ignore the @ for the parameter name and therefore the intelligent matching does not work for CustomerId vs. @CustomerId.

+1 for everybody that uses Stored Procs. :-)

Raj More
  • 47,048
  • 33
  • 131
  • 198
3

You can rename them manually in the Input/Output Properties tab (they reside under "External Columns"), but they will always instantiate to PARAM_0, PARAM_1, ...

Eric
  • 92,005
  • 12
  • 114
  • 115
  • @Eric: SSIS 2008 does not allow me to change the name. – Raj More Jul 16 '09 at 15:22
  • @Tapori: You're right, I was thinking the OLEDB Source. Updated to reflect the OLEDB Command task. – Eric Jul 16 '09 at 16:42
  • @Eric: If you use a stored procedure instead of a SQL command, SSIS recognizes the parameters and pulls them in by name instead of PARAM_0, 1, etc. I will try what you suggested right away. – Raj More Jul 16 '09 at 18:06
  • @Eric: I found a bug in SSIS. I can rename it, but if I close and reopen SSIS, it comes back to it's original name. – Raj More Jul 16 '09 at 18:13