November 11, 2011 at 2:21 pm
I get data pulled out from table to Flatfile ( Fixedwidth with Rowdelimiters).....
Where some source columns data comes with less character values, I should make sure that columns with less character values are appended with ZEROS before actual value and in some cases after the actual value.
Let me give out this example:
Source data columns from Table
Weight zip order code
1 0000x 1234567 1
Now when this are mapped to my Fixed length Flat file, I get this way :
1 0000x1234567(2spaces)1(1space)
Here spaces are left out Blank of my faltfile file ( 1 0000x1234567 1 )
MY goal :
I should make my incoming Code column 00 format.
Code value in table has one digit value. I should make this column value appear in flatfile as 2digit value, appending 0 infront of actual value(1) as 01
Similarly for Weight column, I should make 3digit value, appending 00 infront of actual value(1) as 001.
and my order column has to have 9 digits ( if any missing digit occurs, zeros should be appended at last)
Finally it should look this way :
001 0000x12345670001
Please let me know, incase not clear.
Is there a possiablity to do this in Derived column?, Script code is also welcome ....
November 11, 2011 at 2:54 pm
Now this is NOT a complete solution to your requirements, but a step by step snippet of code, which you can expand upon for all your fields.
CREATE TABLE #T(Weight INT, zip VARCHAR(10), [order] VARCHAR(10), code int)
INSERT INTO #T
SELECT 10,'00X','12345',2
SELECT 1, '0000x', '1234567', 1
SELECT * FROM #T -- Validate test input
--- Just to illustrate how to solve your problem step by step
SELECT REVERSE('00' + CAST(Weight AS VARCHAR(3))),'00' + CAST(Weight AS VARCHAR(3)) FROM #t
SELECT SUBSTRING(REVERSE('00' + CAST(Weight AS VARCHAR(3))),1,3) FROM #t
--Final select statement
SELECT REVERSE(SUBSTRING(REVERSE('00' + CAST(Weight AS VARCHAR(3))),1,3)) FROM #T
Result from this snippet
Weight
001
010
November 11, 2011 at 4:03 pm
I tried out some Tsql Format for the query ... Its was just getting what i need ... but It doesn't sound good for SSIS ... I guess SSIS doesn't take those Formats.... Can some guru save me at this point .... tired of this stuff
November 14, 2011 at 1:33 am
quillis131 (11/11/2011)
I tried out some Tsql Format for the query ... Its was just getting what i need ... but It doesn't sound good for SSIS ... I guess SSIS doesn't take those Formats.... Can some guru save me at this point .... tired of this stuff
You can do most of the formatting in the derived column component.
However, it has a single line editor, which makes it a bit cumbersome to write expressions in.
So I would still stick with TSQL 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2011 at 7:42 am
quillis131 (11/11/2011)
I tried out some Tsql Format for the query ... Its was just getting what i need ... but It doesn't sound good for SSIS ... I guess SSIS doesn't take those Formats.... Can some guru save me at this point .... tired of this stuff
Let me be honest at this point, what I understand about SSIS can be written on the head of pin with a blunt piece of chalk.
I think that SSIS can be utilized to export data from a VIEW, hence you might want to consider using the T-SQL which formats the data into what is required, to create the view, and then use SSIS to extract the data from the VIEW.
Just a thought, now can someone, anyone advise explicitly how to use SSIS with a VIEW as its data source.
November 14, 2011 at 7:56 am
Hehe.
Here's how it's done in SSIS.
1) Add a data flow task.
2) Add an OLEDB source connection to the DF.
3) In the connection manager settings for the OLEDB source, select 'SQL Command' as the 'Data Access Mode'. This presents the user with a box into which a SELECT query can be typed directly - using a view or not.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 14, 2011 at 8:03 am
Phil Parkin (11/14/2011)
Hehe.Here's how it's done in SSIS.
1) Add a data flow task.
2) Add an OLEDB source connection to the DF.
3) In the connection manager settings for the OLEDB source, select 'SQL Command' as the 'Data Access Mode'. This presents the user with a box into which a SELECT query can be typed directly - using a view or not.
Thanks Phil, well now I am beginning to know enough about SSIS to be a danger to myself and anyone around me.
November 14, 2011 at 8:14 am
Thanks Phil, well now I am beginning to know enough about SSIS to be a danger to myself and anyone around me.
No problem!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 14, 2011 at 8:25 am
If you want/have to do it in a Derived Column you can use the following expressions:
Weight: RIGHT("000" + (DT_STR,3,1250)Weight,3)
Order: SUBSTRING((DT_STR,9,1250)Order + "000000000",1,9)
Code: RIGHT("00" + (DT_STR,3,1250)Code,2)
Peter
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply