Row number in the dataflow task

Hello all,

I got a text file with two columns. and I need to generate a integer key automatically with the row number (or any distinct number, I thought row number will be OK). and when I make the data flow task to import this text file into a raw file I need to get the unique rownumber as Id.
How can I make this in the data flow tak?

regards,

This should work for you:

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

|||

Did you try searching the forum? That question has been asked before and several are the ways of doing it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379124&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1533040&SiteID=1

|||

Thank you.....

I am so happy with the script componant......

It works good.....

Thank you again.

Regards

Rounding error: Between flat file connection manager Source & OLE DB Connection Destination

I have a Rounding error: Between flat file connection manager Source & OLE DB Connection Destination (SQL Server 2005) in my Dataflow.

File looks like this lets call column names Col A,B,C,D

70410000 RD1 1223631.92 196042.42
70329000 ICD 11025.84 3353.88
71167300 COL 104270.59 24676.96

flat file connection manager settings: first row Column names then Advanced tab Col A float , Col B float , Col C string ,Col D float ,

OLE DB Connection Destination (SQL Server 2005)

CREATE TABLE [dbo].[PT_CUST_ABR](

[PARTY_NO] [float] NULL,

[PARTY_NAME] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TELECOMABR] [float] NULL,

[GENIABR] [float] NULL,

Problem: ColA (Source) Rounding error to PARTY_NO (Destination)

I have a field of text of in a flat file that the flat file connection manager Source picks up correctly “70000893”

However when it gets the OLE DB Connection Destination the data has changed to 70000896. That’s before its even Written to the database.

The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07

Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00

ColA (Source) PARTY_NO (Destination)
71167300 71167296
70329000 70329000
70410000 70410000

Any ideas people?

Thanks in advance

Dave

Float type by definition is not precise, it holds about 7 decimal digits, so it is expected.

If you want to keep exact values, you should use types that are big enough to hold them. E.g. Int for ColA and Decimal for ColC & ColD.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_6r3g.asp

|||

Thanks Michael--already tried that --turned out to the XML file in SSIS had my old settings locked in and my new settings in the flat file connection manager Source did not take effect

I fixed the problem by changing the datatype to string in the flat file connection manager and bigint in the SQL Server 2005 table. However I needed to delete all the objects in the dataflow task and re-recreate them again.--this fixed the XML metadata problem

Dave