October 29, 2018 at 11:54 am
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:
| Source | SourceShortName | SourceLongName |
| A | CITY | Location |
| B | State | COUNTRY |
| C | School | SCHOOL main |
Required Output:
| Source | SourceShortName | SourceLongName |
| A | City | Location |
| B | State | Country |
| C | School | School Main |
Best Regards,
Danny
October 30, 2018 at 5:57 am
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
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
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy