SSIS Flat File as Source and SQL Server As destination

Question

SSIS Flat File as Source and SQL Server As destination

tush002sunny

Old Hand

Points: 310
More actions
June 27, 2012 at 10:27 pm

#392619

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......................!!!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

tush002sunny Old Hand Points: 310 More actions · Answer 2

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.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

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

tush002sunny Old Hand Points: 310 More actions · Answer 4

Will you plz give me some examples to do in a way you suggested .

That will be really helpful to me ...

Thanks.....

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

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

tush002sunny Old Hand Points: 310 More actions · Answer 6

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

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

tush002sunny Old Hand Points: 310 More actions · Answer 8

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 .......

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

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

Phil Parkin SSC Guru Points: 246988 More actions · Answer 10

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

tush002sunny Old Hand Points: 310 More actions · Answer 11

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.....................

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

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

tush002sunny Old Hand Points: 310 More actions · Answer 13

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...............!!!!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 14

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