SQL inport flat file add identity

I have a large flat file. One Coulmn one field

I can impot to SQL but I need te resulting SQL table have an identity filds so I can select the first or the last recors of the flat table

Any ideas?
robrodpProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Just impot into a pre-existing table that contains both the identity field and the one-column, importing into the one column only.

Make sure you do a TRUNCATE TABLE your_table before impotting to remove any previous recors.
robrodpProgrammerAuthor Commented:
I get this:


 Copying to [dbo].[sso] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'id', table 'sso.dbo.sso'; column does not allow nulls. INSERT fails.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - sso.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination - sso.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - sso" (14) failed with error code 0xC0209029 while processing input "Destination Input" (27). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - sso_log returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
Jim HornMicrosoft SQL Server Data DudeCommented:
>"Cannot insert the value NULL into column 'id',
Explain why you are inserting into the identity field id.  If this is a 'One Coulmn one field' ETL, then the table should have id identity, and a some_column_name varchar(big number), where the data flow task ONLY pumps values into the some_column_name column.

The identity column will automatically generate a new number without having to explicitly insert one.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

robrodpProgrammerAuthor Commented:
My existing table has

id as bigint identity
logs as nvarchar(4000)

The flat file column is named logs

mappings have the append rows check and enable identity checked

The mappigs shows a warning (yellow triangle with an exclamationmark)

What am I douin
Jim HornMicrosoft SQL Server Data DudeCommented:
Close request cancelled.  The above comments answer the question, and I don't see enough info in the asker's comment to get away with a 'Nevermind, I did it myself' close.
robrodpProgrammerAuthor Commented:
One ast idea will the table be indexed by the identity field? or do I have have to index it after it is imported.

If so Is there a way to index it "on the fly" so to speak?
Jim HornMicrosoft SQL Server Data DudeCommented:
Curiosity overwhelms me ... how can you claim you 'got it right' and have follow up questions?

>One ast idea will the table be indexed by the identity field?
Doesn't matter as far as the ETL, but the case you'll have to make is...
Pre-existing index - Basically don't want to alter schema in case someone cares, as certain developers may not have privs to edit indexes, no big deal if small number of rows
DROP-CREATE INDEX - Ok if we're talking a large number of rows (multiple millions) and the speed of the ETL job differs greatly between having an index (insert into the table + insert into the index, takes time) and not having the index.

>If so Is there a way to index it "on the fly" so to speak?
Yes, T-SQL CREATE INDEX
robrodpProgrammerAuthor Commented:
Thx

Got it right
Jim HornMicrosoft SQL Server Data DudeCommented:
Close request cancelled again.  The above comments answer the question, and I don't see enough info in the asker's comment to get away with a 'Nevermind, I did it myself' or 'Got it right' close.

So, explain for us what the eventual solution was..
robrodpProgrammerAuthor Commented:
I got it afte I read:

the table should have id identity, and a some_column_name varchar(big number), where the data flow task ONLY pumps values into the some_column_name column.

The identity column will automatically generate a new number without having to explicitly insert one.
Jim HornMicrosoft SQL Server Data DudeCommented:
Okay, then please accept that comment as the answer, so that others that search this question for their own issues may benefit.
robrodpProgrammerAuthor Commented:
Sorry I did not set it as the solution....
Jim HornMicrosoft SQL Server Data DudeCommented:
Thanks for the grade.  Good luck with your project.  -Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.