June 27, 2012 at 10:27 pm
I have a fixed width flat file like " abc.txt".
This File contains the records in following format
asdfggfbffhfhg123345466577asddghghg
asfdgffhereohotkjk172366478585895895
aewretrytytg243545fgsghfhhhshfhhhhhh
I have to Map these records to sql server table say "dbo.Customer"
While moving data I have to Skip certain characters while mapping these records to the column
I have to put 2nd to 8th characters into column 1 of table by skipping 1st character
& 9th to 15th characters into column 2 . All this I have to do this by using SSIS package.
I facing problem & I want solution for this.
Can anybody please help .............?
Thanks a millions in Advance......................!!!
June 28, 2012 at 12:44 am
You can either read the flat file is a fixed width file, specifying lengths for each column and keeping all the columns you need.
Or you read the data is one column and select what you need with the SUBSTRING fuction inside a derived column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 28, 2012 at 3:25 am
Thanks,
I tried in a way suggested but I want to map certain length of characters to certain columns in table from each record . because the file contains the each record on new line.
June 28, 2012 at 3:45 am
I don't understand the issue. If you import it as fixed width, what's not working?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 28, 2012 at 3:58 am
Will you plz give me some examples to do in a way you suggested .
That will be really helpful to me ...
Thanks.....
June 28, 2012 at 4:44 am
What have you tried so far?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 28, 2012 at 11:05 pm
My Flat file (.txt) is in followong format :
1000034567
1000089564
1000034216
1000024076
.
.
.
.
.
The file Contains too many records.
I have to put first 5 characters(digits) into "Code" column of "dbo.Device"
I also have to put next 2 characters into "Location" column of same table & last 3 characters into last column of same table.
I have used derived column transformation with following expression :
"SUBSTRING([Column 0],1,6) ". I have also used expression for next two columns. I set the datatype to DT_STR. But don't know about the length & precision.
I have also checked the same expressions for the Null value.
When I have used the OLEDB destination for the first time there was only values in first column of table as below:
Code Location Lid
0
1
1
2
3
4
5
6
7
8
9
& the result shows 12 rows affected but actual values shown in BIDS was near about 10,340 or something ......
Will you please help me out for this ....... or provide an appropriate expression to solve this problem.
Thanks
June 28, 2012 at 11:11 pm
There's no such thing as too many records. As long as you don't have blocking components (sort component, aggregate component for example) you can read as many rows as you want.
I would solve this with a derived column. Read everything in as one string column.
To get the seperate columns, create a line for each column in the derived column transformation.
A typical line will look like this:
SUBSTRING(myColumn,a,b)
where a is the starting point and b the length of the column.
You don't need to worry about the datatype, codepage and length of the string, the derived column will define that for you. It should look like this: DT_STR(1252,b).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 2, 2012 at 12:18 am
Thanks,
I tried it & it's working .
I also have to check a particular character at an index no. 8 is blank/null/not specified .
If character is not present there then I have to put a new character say 'a' there & put it in it's appropriate column.
I have checked the record for the null but it's not working.
Please give me any way for doing this .......
July 2, 2012 at 1:03 am
What is the expression that you used?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 2, 2012 at 2:33 am
tush002sunny (7/2/2012)
Thanks,I tried it & it's working .
I also have to check a particular character at an index no. 8 is blank/null/not specified .
If character is not present there then I have to put a new character say 'a' there & put it in it's appropriate column.
I have checked the record for the null but it's not working.
Please give me any way for doing this .......
Saying that something is "not working" is not very helpful. We cannot see your screen and do not know what you are trying to do. If you cannot provide a more detailed explanation of what you are trying to do, including what you have tried and what errors you are getting, this post could easily stretch to several frustrating pages.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 2, 2012 at 3:53 am
I used an Expression
IsNull(SUBSTRING([column 0],8,1))?"a":[column 0]
but it's not working .
Is this expression checks whole string for null value or only particlar character.
Plz help for this.....................
July 2, 2012 at 3:57 am
Your checking for NULL values.
A part of a string can't be NULL. Either the whole string is NULL or it has a value.
I think you are looking for a blank character, aka a space.
SUBSTRING([column 0],8,1) == " " ? "a" : [column 0]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 5:02 am
I have the records in my flat file as below :
HDR123495968908
1111023456712345
1234567890098007
TRL4567897593494
After the use of derived column I am able to map certain characters to the particular column of database table.
Now while mapping to the database table I want to skip these header & trailer row.
Data present in the SQL server table like this:
column1 column2
1111023 456712345
1234567 890098007
HDR1234 495968908
TRL4567 897593494
Will you Plz help me out for this doing in SSIS Package Transformation.........?
Thanks...............!!!!
July 4, 2012 at 5:08 am
Why? A table has per definition no order defined. It doesn't make sense to go through all sorts of troube just to insert two rows after another two rows.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 15 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