Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tooba111
Starting Member
22 Posts |
 Posted - 2010-10-29 : 01:44:08
|
| I want to keep one column as a identity column. but i want to use identity column like this "yyyymmdd hh:nn:ss.mi+1",(Autogenerate). Please guide me how i can accomplish this issue in SSIS. THANKS . |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-29 : 11:26:06
|
| Right out of SSMS:Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. |
 |
|
|
tooba111
Starting Member
22 Posts |
Posted - 2010-10-30 : 00:28:54
|
| First of all i really appreciate your reply. I am using SSIS to finish this project, and i am using EXCEL SOURCE file and OLE DB source. during, i want one field called "LINEID" datatype varchar(30) not null, as a auto generate, it should be like this "yyyymmddhhnnssmi+1". My question is how i can accomplish in SSIS or SSMS. It would be awesome if any 1 provide sample code for me. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-10-30 : 09:53:53
|
| You mean you want to add 1 millisecond to the previous row to get the value?That's a very curious requirement.A datetime isn't held in character format anyway - it is just formatted for display from a number.You could have an integer identity column (and use a base value if necessay). Then when it is extracted convert - or include a comuter column on the table or use a view to access it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tooba111
Starting Member
22 Posts |
Posted - 2010-10-30 : 22:44:06
|
| Below is the t- sql code that some 1 is using. But when i try to run giving me error. Please advise where i m wrong and how i can link with field "CUSTID DATA TYPE VARCHAR(30)".SET @lCounter = @lCounter + 1 -- Set unique RecordID SET @id ='abc:' + right(('00' + str(DATEPART(yy, GETDATE()))),2) + right(('00' + ltrim(str(DATEPART(mm, GETDATE())))),2) + right(('00' + ltrim(str(DATEPART(d, GETDATE())))),2) + right(('00' + ltrim(str(DATEPART(hh, GETDATE())))),2) + right(('00' + ltrim(str(DATEPART(n, GETDATE())))),2) + right(('00' + ltrim(str(DATEPART(ss, GETDATE())))),2) + right(('000000'+ ltrim(str(@lCounter))),6) end |
|
|
|
|
|
|
|
|
SSIS HELP