i have flatfile which has a field accountid(ex:123123123) .
I need to import my accountid which is in flatfile to database in which it is also named accountid(uniqueidentifier,null)(it's a GUID unique identifier)
Tried changing the metadata of flat to unique identifier but am getting error.
[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Account Id" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". [Flat File Source [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Flat File Source.Outputs[Flat File Source Output].Columns[Account Id]" failed because error code 0xC0209084 occurred, and the error row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[Account Id]" 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.
3条答案
按热度按时间dvtswwa31#
Create field for storing source accountid as integer. Add derived column with generated GUID id's (howto Create a GUID column in SSIS ) to your source data and use it as primary key in target.
If you trying import GUID values with the same error message:
[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "AccountId" returned status value 2 and status text "The value could not be converted because of a potential loss of data."...
or (the same in Russian):
[Flat File Source [177]] Error: Ошибка преобразования данных. При преобразовании данных для столбца "accountid" возращено значение состояния 2 и текст состояния "Невозможно преобразовать значение из-за возможной потери данных.".
[Flat File Source [177]] Error: Код ошибки служб SSIS: DTS_E_INDUCEDTRANSFORMFAILUREONERROR. Сбой Flat File Source.Выводы[Выход источника "Неструктурированный файл"].Столбцы[accountid] из-за возникновения ошибки с кодом 0xC0209084, и стратегия обработки ошибок строк в "Flat File Source.Выводы[Выход источника "Неструктурированный файл"].Столбцы[accountid]" определяет сбой по ошибке. Ошибка возникла в указанном объекте указанного компонента. Возможно, до этого были опубликованы сообщения об ошибках, в которых содержатся более подробные сведения о причине сбоя.
Dublecheck that GUID values have curly braces. This CSV throws errors:
This will be processed:
uxh89sit2#
I would solve this by importing your flat file to a Staging table that has a varchar datatype for
AccountId.Then call a stored procedure that copies the data from the staging table to your final destination and uses
TRYPARSE()to convert theAccountIdcolumn to a GUID. Then you will be able to handle the rows that don't have a valid GUID in theAccountIdcolumn without losing the rows that do.elcex8rz3#
I had a similar problem importing GUIDs from an Excel file into a table having uniqueidentifier column. Turned out the SQL import wizard throws the dreaded potential data loss error if the GUIDs are not wrapped in {}.