Link to home
Start Free TrialLog in
Avatar of robrodp
robrodpFlag for Mexico

asked on

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?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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.
Avatar of robrodp

ASKER

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)
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of robrodp

ASKER

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
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.
Avatar of robrodp

ASKER

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?
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
Avatar of robrodp

ASKER

Thx

Got it right
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..
Avatar of robrodp

ASKER

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.
Okay, then please accept that comment as the answer, so that others that search this question for their own issues may benefit.
Avatar of robrodp

ASKER

Sorry I did not set it as the solution....
Thanks for the grade.  Good luck with your project.  -Jim