Post reply

SSIS Update multiple column values

  • October 29, 2018 at 11:54 am

    #372629

    Hi ya,

    I have scenario to update the columns with proper case. Written a SQL server function which returns a proper case string. Can you please tell me how to pass column values dynamically to the function and update columns in SSIS.

    Non-Proper Case:

    SourceSourceShortNameSourceLongName
    ACITYLocation
    BStateCOUNTRY
    CSchoolSCHOOL main

    Required Output:

    SourceSourceShortNameSourceLongName
    ACityLocation
    BStateCountry
    CSchoolSchool Main

    Best Regards,
    Danny

  • October 30, 2018 at 5:57 am

    #2011657

    Need a little more information.
    Are you trying to correct the data in a data flow in SSIS. i.e. are you looking at doing this change between a data source task and a data destination task?

    1.My preferred method for this type of thing is to load the data as is to a staging table using a data flow task.
    Then write a stored procedure that selects the data from staging as you want to see it.
    Then add a second data flow task - this will use the stored procedure as the data source and your target table as the destination task.

    2.Alternatively (and I think it is less maintainable is a script component)

    M

  • October 30, 2018 at 8:39 am

    #2011704

    Hi Ells,

    Yes, you're correct I'm trying to correct within SSIS. The data is already in staging tables and there is a function which will return us the string in a proper case.

  • October 30, 2018 at 9:19 am

    #2011708

    Ok
    so with the next data flow task your data source will be an OLE DB data source querying like ..
    SELECT
            EmpID,
            [dbo].[fn_capitalize](Name) AS Name,
            VacationDays
    from [AdventureWorks2012].[dbo].[EmployeeList]

    Then use OLE DB Destination Task to put the data into the destination table.

    M

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

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