CODEWITHSUNDEEP
sql-serverssis
kanna
kanna

Reputation: 73

Incompatibility due to GUID

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.

Upvotes: 2

Views: 1355

Answers (3)

Ε Г И І И О
Ε Г И І И О

Reputation: 12371

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 {}.

Ex:  66498FED-E31F-4A6E-930B-C21E44F19CE9  BAD
    {66498FED-E31F-4A6E-930B-C21E44F19CE9} GOOD

Upvotes: 1

Vladislav
Vladislav

Reputation: 2005

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:

ReqType;accountid;contactid;
0;6E0DAA5D-CB68-4348-A7B2-AD2367190F83;FFA9D382-D534-4731-82A0-D9F36D8221B0;

This will be processed:

ReqType;accountid;contactid;
0;{6E0DAA5D-CB68-4348-A7B2-AD2367190F83};{FFA9D382-D534-4731-82A0-D9F36D8221B0};

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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 the AccountId column to a GUID. Then you will be able to handle the rows that don't have a valid GUID in the AccountId column without losing the rows that do.

Upvotes: 0

Related Questions