July 20, 2012 at 12:55 pm
You cannot vote on your own post
0
Package fails when importing data from flat to SQL Server
Number of columns in Flat File
ProductID,Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost
Table structure in SQL Server
[ProductID] [int] NULL,
[Name] [nvarchar](50) NULL,
[ProductNumber] [nvarchar](25) NULL,
[MakeFlag] [bit] NULL,
[FinishedGoodsFlag] [bit] NULL,
[Color] [nvarchar](15) NULL,
[SafetyStockLevel] [smallint] NULL
Used derived column to convert the data types
MakeFlag1 <add as new column> MakeFlag == "False" ? "0" : "1" Unicode string [DT_WSTR] 1
FinishedGoodsFlag1 <add as new column> FinishedGoodsFlag == "False" ? "0" : "1" Unicode string [DT_WSTR] 1
SafetyStockLevel1 <add as new column> (DT_I2)SafetyStockLevel two-byte signed integer [DT_I2]
Mapped the data types correctly in OLE DB destination.I am getting error message in the case of 'SafetyStockLevel' . As far i know everything has been done properly here what could be the issue.Please correct me if i am going wrong any where. Please find the error message as below
Error: 0xC0049064 at Data Flow Task, Derived Column [58]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [58]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (58)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "SafetyStockLevel1" (83)" 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: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (58) failed with error code 0xC0209029 while processing input "Derived Column Input" (59). 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
July 21, 2012 at 7:44 pm
Looks like a data conversion error. Can you confirm there are no lines in the file where that field is empty? You may want to change your derived column expression to something like:
SafetyStockLevel == "" ? 0 : SafetyStockLevel
If you continue to have issues you might think about redirecting the error rows to a file somyou can inspect them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 23, 2012 at 11:27 pm
After debugging found issue if SafetyStockLevel is less than double digit
This is how the data should be inserted in the destination table
ProductID Name ProductNumber MakeFlag FinishedGoodsFlag Color SafetyStockLevel
716 Long-Sleeve Logo Jersey, XL LJ-0192-X 0 1 Multi 4
When i upload the flat file as source
and when i preview the data,this is how the data looks
ProductID Name ProductNumber MakeFlag FinishedGoodsFlag Color SafetyStockLevel
716 Long-Sleeve Logo Jersey XL LJ-0192-X 0 1 Multi,4
We have around 54 records to deal with. What should be the approach . I was thinking about using SQL queries to correct the data
July 24, 2012 at 12:22 am
To have more clarification destination table should look like
ProductID ->716
Name->Long-Sleeve Logo Jersey, XL
ProductNumber->LJ-0192-X
MakeFlag>0
FinishedGoodsFlag->1
Color->Multi
SafetyStockLevel->4
and when i preview the data,this is how the data looks
ProductID ->716
Name->Long-Sleeve Logo Jersey
ProductNumber->XL
MakeFlag>LJ-0192-X
FinishedGoodsFlag->0
Color->1
SafetyStockLevel-> Multi,4
July 24, 2012 at 3:14 am
please send me any body has script for this scenario
July 24, 2012 at 6:41 am
Embedded commas in the data are problematic for SSIS.
Here is a good thread to read up on the issue:
http://www.sqlservercentral.com/Forums/Topic1092764-148-1.aspx#bm1194909
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply