July 15, 2012 at 9:45 pm
Import fails when i try to import the flat file to data base
i have used AdventureWorks database to test this scenario
Steps:
1. Imported Production.Product table from AdventureWorks database to Flat File
2.Created Production.Product table in database
3.Tried to import data from flat file to SQL Server.But import fails.
Tried all the scenario like matched data types using Advanced option in choose data source simply it does not work
I know there is conversion problem. Please let me know how to solve the problem
Data types in Production.Product table
ProductIDint
NameName nvarchar(50)
ProductNumbernvarchar(25)
MakeFlagFlag:bit
FinishedGoodsFlag
Colornvarchar(15)
SafetyStockLevelsmallint
ReorderPointsmallint
StandardCostmoney
ListPricemoney
Sizenvarchar(5)
SizeUnitMeasureCode
WeightUnitMeasureCode
Weightdecimal(8, 2)
DaysToManufactureint
ProductLinenchar(2)
Classnchar(2)
Stylenchar(2)
ProductSubcategoryIDint
ProductModelIDint
SellStartDatedatetime
SellEndDatedatetime
DiscontinuedDatedatetime
rowguiduniqueidentifier
ModifiedDatedatetime
Please let me know what could be the problem
July 15, 2012 at 10:01 pm
Smash125 (7/15/2012)
Import fails when i try to import the flat file to data basei have used AdventureWorks database to test this scenario
Steps:
1. Imported Production.Product table from AdventureWorks database to Flat File
2.Created Production.Product table in database
3.Tried to import data from flat file to SQL Server.But import fails.
Tried all the scenario like matched data types using Advanced option in choose data source simply it does not work
I know there is conversion problem. Please let me know how to solve the problem
Data types in Production.Product table
ProductIDint
NameName nvarchar(50)
ProductNumbernvarchar(25)
MakeFlagFlag:bit
FinishedGoodsFlag
Colornvarchar(15)
SafetyStockLevelsmallint
ReorderPointsmallint
StandardCostmoney
ListPricemoney
Sizenvarchar(5)
SizeUnitMeasureCode
WeightUnitMeasureCode
Weightdecimal(8, 2)
DaysToManufactureint
ProductLinenchar(2)
Classnchar(2)
Stylenchar(2)
ProductSubcategoryIDint
ProductModelIDint
SellStartDatedatetime
SellEndDatedatetime
DiscontinuedDatedatetime
rowguiduniqueidentifier
ModifiedDatedatetime
Please let me know what could be the problem
You say that you "Imported Production.Product table from AdventureWorks database to Flat File.
I assume that you exported to a flat file?
The Production.Product Table already exists in Adventureworks2008 with the same DDL you specified?
What error(s) are you getting?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2012 at 10:11 pm
July 15, 2012 at 11:42 pm
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "ModifiedDate" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "output column "ModifiedDate" (106)" failed because truncation occurred, and the truncation row disposition on "output column "ModifiedDate" (106)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "D:\Users\Administrator\Desktop\iMPORT.txt" on data row 211.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - iMPORT_txt" (1) returned error code 0xC0202092. 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)
July 16, 2012 at 5:33 am
You are getting an error on the ModifiedDate.
A Record(s) in the Source Column for ModifiedDate is too large.
What is the largest value for that column in your flat file.
Have you taken a look at this?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 16, 2012 at 10:58 pm
For the last two days i scratched my head day and night,but was unsuccessful.Tried all whatever i had. I have attached the doc file steps i have followed. Tried all trial and error methods but no use:(
July 17, 2012 at 12:02 am
Please tell us, what is your "ModifiedDate" column; I assume that since you are trying to import from a flat file; it must be a text; now, you have to check, what maximum size is this column in that file and what column size is SSIS is picking in Flat File Source?
As per my experience, Flat File Source by default pick 50 characters for all columns; so, if any column is having value greater than this; you have to manually increase the size in this control and accordingly reflect it down the stream till your destination control.
Second thing, please check what type your file is? is it a unicode or an ASCII? if ASCII, please make sure of your code page; and make sure that you specify same settings in your Flat File Source control.
Try to see if there are some other warnings as well while compiling or running from Business Intelligence Studio.
Telling us some details will help in identifying the bottom line issue.
July 17, 2012 at 6:42 am
- When i upload the flat file as destination. Data types for all the columns is string[DT_STRING] this is expected behavior. The maximum size for the particular column(ModifiedDate) is 50. I have changed more than 50,still issue persists
- i have checked with both unicode or an ASCII.Still issue persists
July 17, 2012 at 12:44 pm
This problem occurs only in the case of inserting rows into existing table.
while importing data from Flat File to SQL Server if you specify new table rather than existing table this will work. I have give up the first scenario anybody has any idea please let me know
July 18, 2012 at 6:14 am
You are saying; you are loading flat file as destination; then you are saying that you are importing data from your flat file to sql server table?
these two are seemingly contradictory statements; i am assuming that you are loading flat file data to a sql server table;
see, if the table is already exists; then in destination control, SSIS will pick the data types automatically from sql server table given your connection string;
similarly, when you will specify your flat file source; that control will generate source columns automatically; which will be 50 characters each;
please make sure that you transform each column correctly from source to destination; for that; you may need a transformation control "Data conversion"; which will convert the required fields from text to other formats; or may be unicode to ascii or vice versa.
one more thing; you need to check is that; whether your file has corrupted data; like if comma "," is your column separator; may be it is used somewhere in the data itself;
as a test; i will suggest to create a file containing only 1 row and see whether that row is being loaded to your table or not; this will make your debugging easier; as you will be able to point out the issue in the data or your script for one single row easily.
July 18, 2012 at 10:42 am
Trying to load Flat File to SQL Server. Number of columns in Flat file
ProductID - two-byte signed integer [DT_I2]
Name -Unicode string [DT_WSTR](50)
ProductNumber-Unicode string [DT_WSTR](25)
MakeFlag-Boolean [DT_BOOL]
Getting issue with last column 'MakeFlag'.
I have attached sample Flat file if some body wants to test it
Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "MakeFlag" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "MakeFlag" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "MakeFlag" (22)" 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.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "D:\Users\Administrator\Desktop\New Folder\IndiaIndia.txt" on data row 211.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. 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.
July 18, 2012 at 11:21 am
Your import is failing because you are trying to put 'False' or 'True' into a bit field. You need to convert the boolean value to a 0 or 1 before importing it.
July 18, 2012 at 1:01 pm
Can you please send me the expression. Google d to check whether i can get some thing
These are some of the examples i got it
[ColumnName] == "Y" ? (DT_BOOL)1 : (DT_BOOL)0
(DT_BOOL)([ColumnName] == "Y" ? 1 : 0)
[Recycled] == "Y" ? True : False
Tried with these bu not sucessful
July 18, 2012 at 1:26 pm
In your first example, if you remove the conversion to boolean, it should work. The problem is that you are attempting to insert a data type (boolean) into a field that is not boolean.
A simple 1 or 0 should work.
July 19, 2012 at 1:16 am
User derived column to convert the boolean value to a 0 or 1
Expressions what i used in derived column for two columns MakeFlag and FinishedGoodsFlag
MakeFlag ? (DT_BOOL)1 : (DT_BOOL)0
FinishedGoodsFlag ? (DT_BOOL)1 : (DT_BOOL)0
I get error messages as below.Any thing wrong with the expression i am using here.Please let me know
Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "MakeFlag" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "MakeFlag" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "MakeFlag" (22)" 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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply