hi , my date field is coming from file which has formate CCYYMMDD. now in ssis package i declared this column as a date ,in script component and try to get values. using conver.todatetime(columnname) but getting error, now if i make this field in ssis string and in my sql table string it works fine, but i cannot change my sql table fileld. so need something that works around and get me value in sql table as a date CCYYMMDD

Add a derived column and write this expression for it: (DT_DATE)(SUBSTRING([Column 0],5,2) + "/" + SUBSTRING([Column 0],7,2) + "/" + SUBSTRING([Column 0],1,4))

tried getting error

Replace "/" with "-" and let us know your observationsRajkumar

i replace , not getting any syntax error at derived column, but when i run package giving me error: Error: 0xC0049064 at Data Flow Task, Derived Column [18]: An error occurred while attempting to perform a type cast. Error: 0xC0209029 at Data Flow Task, Derived Column [18]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" 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" (18) failed with error code 0xC0209029 while processing input "Derived Column Input" (19). 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. i am using script component before derived column, on which outputcolumn salary date is a unicode_string let me know where is the error

Looks like Overflow error is happening. Can you post the first error message? Since the errors you posted didn't give enough information. Can you post some sample data of this column as well?Rajkumar

OK.ITS WORKING what i did is juts put (SUBSTRING([Column 0],5,2) + "/" + SUBSTRING([Column 0],7,2) + "/" + SUBSTRING([Column 0],1,4)) in the expression , and remove(dt_date) front of it. i need similar expression for time ,as one of the column on my table is time(7),can you give me

my time will come as HHMMSS OR HHMM what expression do i need to put in derived column

SUBSTRING([timecolumn],1,2)+":"+SUBSTRING([timecolumn],3,2)+":"+LEN([timecolumn]) > 4 ? SUBSTRING([timecolumn],5,2) : "00" Rajkumar

GETTING error, also one field ,i am getting as string and in my table its numeric(2,0) ,tried to convert but getting error while running package

error: the data tpe dt_wstr and dt_it are incompatible for binary operator "+".the operand types could nto be implicilty cast into compatible types of operation

i solve it bu using (SUBSTRING(TH06,1,2) + ":" + SUBSTRING(TH06,3,2) + ":" + (LEN(TH06) > 4 ? SUBSTRING(TH06,5,2) : "00")) th e problem was brackets. now i am just stuck with numeric (2,0) hwo to convert from string to numeric(2,0) in derived column

In the derived column transformation set the expression to (DT_NUMERIC, 2,0)[MyColumnToConvert] Arthur My Blog

i did it, if you see my screen shot. package is failed bcoz of that

I do not see this expression, what was the error? I am thinking you try to assign a value incompatible with NUMERIC, that's all.Arthur My Blog