SQL Server Functions work in SSMS but not in SSIS Derived Column

  • Oscar_Boots

    Old Hand

    Points: 313

    Hi Forum,

    I am creating a Project that will process tables of data and remove unwanted characters, Pad spaces onto the existing values and change the Date Format from YYYY-MM-DD to YYYYMMDD (and stay in Date Data Type.

    I've attached a screensshot of the code and pasted some below.

    All of these run without an issue in SSMS, not in the Derived Column Transformation in Visual Studio.

    Any ideas about what is going wrong? Thanks Peter

    REPLICATE(' ',7-LEN([DET_NUMBER])) + CAST([DET_NUMBER] AS VARCHAR(7)) as DET_NUMBER
    ,REPLACE([POS_START],'-','') AS POS_START
    ,CONVERT(VARCHAR(1000),[FILLER_01]) AS FILLER01

     

    Attachments:
    You must be logged in to view attached files.
  • PB_BI

    SSCoach

    Points: 17094

    SSIS Expressions are not T-SQL. Not only are there some things in your code that don't exist in SSIS expressions (CAST, CONVERT), where there are shared function names sometimes the syntax is different. A good place to start is here: https://docs.microsoft.com/en-us/sql/integration-services/expressions/functions-ssis-expression?view=sql-server-2017


    I'm on LinkedIn

  • Oscar_Boots

    Old Hand

    Points: 313

    Thanks PB_BI,

    I thought it was something like that, I'll look at the link.

    Cheers Peter

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply