Hi Guys,
Related to my last questions on SSIS work i'm doing, Is there a way to pad 0 on my generated flat file dynamically.
I'm getting the data from 1 table and then generating the file. The file i need to generate would have data at the desired location as the file is being used by another system.
Depedning on the data I want to put the padding of "0" and "3" inc certain fields. How am i suppose to do it.
Apart from this I would need to megre 2 or more column and before the merge do an airthmatic operation.
What would be the best component to use script component or derived column?
Ta
Gemma
Gemma,
Not sure if this will help or not, but it is how i've padded zero's before...
Use a derived column:
@.LENGTH is the length you would like to pad to)
@.MyStr is the character expression or string you would like to pad
SUBSTRING(REPLICATE("0", @.LENGTH), 1, (@.LENGTH + 1) - LEN(TRIM(@.MyStr))) + TRIM(@.MyStr)
|||Here's another - using the same variables as above:
RIGHT(REPLICATE("0",@.Length) + @.MyStr, @.Length)
|||Hi Guys,
I don't know whether this is completely what I asked.
I would need to pad the values dynamically. By this I mean the padding would be done depending on the actual length of data in the columns. So when the file is generated I would have to check each column and if certain column's data length is less then fixed length then pad it with zero.
Other then that how would I suppose to use these variables?
That is why I asked Script or derived column which one.
Just a quick note, the file I'm generating is going to be fixed width as i guess it and i'm generating a dummy file but with fixed width it's not putting the records in each line. Instead file is having all the records at the same line. Why? I've already checked. There are 2 columns which i'm writing. The first i'm putting in as 20 and the 2nd one as 35 width still i'm getting this problem.
Ta
Gamma
|||
The above expressions do exactly what you asked. The variables are simply placeholders in the code. If you know that your column is 20 wide and you want it to pad out the non filled data to 20 places (to the right) you would simply put your column name in the @.MyStr and 20 in the @.Length.
for example we have a membership number that needs to be padded out to 9 digits. The column's name is Member.
SUBSTRING(REPLICATE("0", 9), 1, (9+ 1) - LEN(TRIM([Member]))) + TRIM([Member])
So, what that does is takes a string of 9 zero's (Replicate("0", 9) and substrings out the 1, to 9 - trimmed column length and then adds the trimmed column information.
i.e. member number of "12345 " would yeild substring(replicate("0", 9), 1, 10 - 5) + "12345" > "0000" + "12345" > "000012345"
You would need to set this up for each column seperately.
hope this helps.
sql