Random Data is inserted in SSIS

Question

Random Data is inserted in SSIS

gajayku

SSC-Addicted

Points: 445
More actions
July 9, 2014 at 8:03 am

#308886

Hi
I m using SSIS to transform the data from csv file(which has fixed width as delimiter) to database. I m inserting 10K records from csv file to database table. My problem is that data is inserted successfully but in improper order. Some times, it is missing sequence order. For example, data from 1 to 520 records are inserted sequentially into the database table and then it is inserting 6000-10000 records and thereafter it is continuing to insert from 521-5999 records. I checked the csv file, data is in order(1-10000). Can you please tell me the reason why it is happen?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Database tables do not have an order. What is the problem, exactly?

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

gajayku SSC-Addicted Points: 445 More actions · Answer 2

Data is not inserting in order from csv to database table. csv file has 10K records which has unique id records(1,2,3,....10000). when I transform the csv file data to database table then it is inserting in the following format(1,2,3,....520,6000,6001,.....10000,521,522,....5999)

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

gajayku (7/9/2014)
Data is not inserting in order from csv to database table. csv file has 10K records which has unique id records(1,2,3,....10000). when I transform the csv file data to database table then it is inserting in the following format(1,2,3,....520,6000,6001,.....10000,521,522,....5999)

As long as the 'UniqueId' in the CSV maps to the 'UniqueId' in the table, order does not matter.

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

gajayku SSC-Addicted Points: 445 More actions · Answer 4

Hi

I m attaching the files of input csv file and the data inserted in the database table. The delimiter is fixed length of 88.

gajayku SSC-Addicted Points: 445 More actions · Answer 5

Hi

My unique Id is of type string. Is that is the problem for random data insertion??

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

I really don't understand the issue here.

Perhaps someone else in the forum can help you.

If you have source and target columns whose data types do not match, you may need to use a data transformation component to fix that.

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

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

gajayku (7/9/2014)
Hi
My unique Id is of type string. Is that is the problem for random data insertion??

I think you used a SELECT statement to verify the results?

Tables in a relational model do not have any order.

I repeat: do not have any order.

If you want an order, use ORDER BY in your query.

The database does not care if the unique ID jumps from 520 to 6000.

It is even possible the actual storage of the data is in another order. Who knows? The order of rows retrieved depends on so many things, such as indexes and query plan.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP