Hi ,
My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .
what approach should i take so that data gets loaded in a relational structure.
for example
Name Age Salary Address
dsds 23 fghghgh
Salary description level
2345 nnncncn 4
here salary is used in this example , the datatype is char in real scenario
what approach should i take to load the data in with cleansing the spaces in ssis
Assumption: You don't have any control over the structure of the destination tables, but you can add records.
To maintain your foreign key constraint, you will need to create a "salary" record that you can use where salary is not defined. EG:
Salary Description Level
9999 Not Assigned 1
Then you can use a Derived Column expression to create a new column in your dataset that uses an If expression and then write that derived column to your destination table
Like this:
Code Snippet
[Salary]=="" ? 9999 : [Salary]
This is not an optimal solution (even though it is very practical.) Ideally you should restructure your tables so:
Assumption: You can change the structure of your destination tables
In this case, you need to take the Salary column out of your destination table, and put it into another table. The relationship is that employees can have zero or one salary code. So you can create a table relating employees to salary codes with two columns (EmployeeNumber, SalaryCode) and a primary key on EmployeeCode.
(This is a drastic simplification, of course. In a real-world scenario you would probably want to track historical information too.)
|||Thanks for the Quick reply i tried doing as you said using derived transformation and using replace function but it doesnot seem to capture the space as value in the replace expression in ssis.
Replace(salary," ",9999) it does not work it says that there might be a possibility of divide by zero error. what expression should i use here to replace the values
|||first of all, since this is a char value, you should put quotes around the 9999.
Seond, I didn't say to use the REPLACE function, though I guess that would work, but you run the risk of having a valid id messed up by replacing a leading or trailing space with a 9999.
Also, if your Salary code was " " (eight spaces) then the REPLACE function will put in:
"99999999999999999999999999999999" which migh be too big for your destination field.
So, try using TRIM first to get ris of spaces, then ?: to replace blank with 9999.
Put this into the Expression column of Derived Value step:
Code Snippet
TRIM([SALARY])=="" ? "9999" : TRIM([Salary])
sql