escape clause causes error in lookup modified sql statement

I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:

select * from dbo.typecustomer where ? like '%'+type_subtype +'%'
ESCAPE '_'

Is this is a bug? Any help will be greatly appreciated.

Thanks

Akin
The escape clause shouldn't be affecting the parameter usage if the SQL works without it. The lookup doesn't parse the SQL, so I suspect the problem is with the provider. Essentially, the lookup asks the provider to prepare the command and then to derive parameter information. The provider is failing in one of the above steps (probably the prepare step). I would first try this with the latest provider (i.e. use snac instead of sqloledb in the connection manager for the lookup) and if the problem persists, ask on the snac forum if command preparation followed by parameter derivation is problematic with your specific command.

Error-Data Conversion Component

Hi

I am getting error in an SSIS,

Source-Excel File

Destination-SQL Server

Source has fields of DataType-Unicode strig[DT_WSTR], I am using "Data Conversion" Component to convert these into Integer.

Source(Unicode strig[DT_WSTR])Destination(numeric[DT_NUMERIC] Precision:18 ; Scale:0)

Error:

[Data Conversion [14]] Error: Data conversion failed while converting column "Column1" (99) to column "Column1_Converted" (204). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [14]] Error: The "output column "Column1_Converted" (204)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Column1_Converted" (204)" specifies failure on error. An error occurred on the specified object of the specified component.

Can anyone tell me which Data Type should I use to convert string into integer.

Thanks

If you have control over the database table, you should get rid of using a numeric data type to hold an integer. Instead, you should use an integer data type.

Then inside SSIS, I'd personally use a derived column instead of a data conversion component and use the following expression:

(DT_I4)TRIM([yourField])

You can replace DT_I4 with any of the appropriate integer data types. (unsigned integers, 8 byte integers, etc...)|||

I can't change the DB table as an application is running on that and we are using this SSIS for the initial load of the data. But I am trying what u have suggested. Thanks a lot for replying, I'll get back to u after trying it.

|||In that case, this is a better expression:

(DT_NUMERIC,18,0)TRIM([yourColumn])|||

I tried it, but still no luck

This is what I am doing:

Step 1:Excel Source->

Step 2: columns-->

Derived Column Name Derived Column Expression Data Type Length

Column1 Replace 'Column1' (DT_I4)TRIM([Column1]) Unicode string [DT+WSTR] 255

Column2 Replace 'Column2 (DT_I4)TRIM([Column2) Unicode string [DT+WSTR] 255

Step 3ata Conversion-->

Input Column Output Alias Data Type Length Code Page

Column1 Column1_STR eight-byte unsigned integer[DT_UI8] 1252 (ANSI -Latin I)

Column2 Column2_STR eight-byte unsigned integer[DT_UI8] 1252 (ANSI -Latin I)

Step 4: OLE DB DEstination

With Columns mapped to Column1_STR and Column2_STR

Error:

[Derived Column [20]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [20]] Error: The "component "Derived Column" (20)"
failed because error code 0xC0049064 occurred, and the error row
disposition on "input column "Column1" (1260)" specifies failure on error.
An error occurred on the specified object of the specified component.

|||

I tried (DT_NUMERIC,18,0)TRIM([Column1]) also,

Error

[Derived Column [20]] Error: The "component "Derived Column" (20)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Column2" (1260)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (20) failed with error code 0xC0209029. 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.

I can change the Excel file if it can solve this problem.

|||Excel Source -> Derived Column -> OLE DB Destination

Derived column component:
Derived Column Name Derived Column Expression Data Type
NumColumn1 <add as new column> (DT_NUMERIC,18,0)TRIM(Column1) numeric [DT_NUMERIC]

Then just use NumColumn1,2,3, etc... in your destination. Don't use the original columns from the Excel source. You can't cast a column to a different data type in place -- you need to create a new output column.|||

Still no luck,

Now I am adding a new column,

Derived Column Name Derived Column Expression Data Type Precision Scale
NumColumn1 <add as new column> (DT_NUMERIC,18,0)TRIM([Column1]) numeric [DT_NUMERIC] 18 0

NumColumn2 <add as new column> (DT_NUMERIC,18,0)TRIM([Column2]) numeric [DT_NUMERIC] 18 0

Error:

Derived Column [20]] Error: The "component "Derived Column" (20)" failed because error code 0xC0049064 occurred, and the error row disposition on
"output column "NumColumn1" (143)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (20) failed with error code 0xC0209029. 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.

|||Are you sure you have numeric (and nothing else) in those columns? I'm expecting to see some alpha characters or something in your columns that can't convert. Do you have decimal points in the raw data? Better yet, can you post some sample data from column1 and column2?|||

Column1 Column2
Row1- 21 34
Row2-
Row3- 28 246
Row4-
Row5- 21 223
Row6-
Row7-
Row8- 30 206
Row9- 21 145
Row10-
Row11- 25 236

|||New expression:

ISNULL(TRIM([yourColumn])) || TRIM([yourColumn] == "" ? NULL(DT_NUMERIC,18,0) : (DT_NUMERIC,18,0)TRIM([yourColumn])
|||

Added new Expression

error:

[Derived Column [20]] Error: The "component "Derived Column" (20)" failed because
error code 0xC0049063 occurred, and the error row disposition on
"output column "Derived Column 1" (1236)" specifies failure on error.
An error occurred on the specified object of the specified component.

|||

I think you will need some extra parens to get what you want (both the isnull test and the empty string test as the condition):

( ISNULL([yourColumn]) || TRIM([yourColumn]) == "" ) ? NULL(DT_NUMERIC,18,0) : (DT_NUMERIC,18,0)TRIM([yourColumn])

The error you saw, though, was that the conditional operation failed, so this might not fix that still.

|||

Yah u're right, I've added the extra parenth but still no luck.

Error: The value was too large to fit in the output column "colName" (60).

Hi All,

I'm trying to transfer data from DB2 Database to SQL Server 2005.

Well, i used the OLE DB Source, the Data Conversion Component and the OLE DB Destination component.

I have five Data flows with this configuration above. But I am receiving an error message from one of them.

Please check below the error message:

"[Source Table TARTRATE [1]] Error: The value was too large to fit in the output column "ADJ_RATE_PCT" (60). "

"[Source Table TARTRATE [1]] Error: The "component "Source Table TARTRATE" (1)" failed because error code 0xC02090F8 occurred, and the error row disposition on "output column "ADJ_RATE_PCT" (60)" specifies failure on error. An error occurred on the specified object of the specified component."

Could you please help me with this issue?

Thanks in advance.

Thiago

The ADJ_RATE_PCT column in your data flow has a defined maximum width. You'll be able to find out what it is by double clicking the data-path leading from the source component.

What has happened is that a value has come in that is bigger than that defined maximum. So, you'll have to increase that maximum in the source component.

-Jamie

|||

Hi Jamie,

Thanks for your answer, but i still dont have a solution for this.

I tried to change the field width but i still have the same error message.

I think it is related with the field type. I received the same error message in another package and the field type is the same ( numeric , Precision 4 and Scale 2 ) as the first one.

I will continuing trying to figure out what is happening.

Thanks in advance.

Thiago

|||My guess is that, like Jamie said, a value is coming in that is bigger than the type. Based on the error message, and the type, it sounds like the value probably has a precision larger than 6. Have you tried increasing the precision (and possibly scale) of the output column in the advanced editor (inputs and outputs tab) of the source component?|||

Hi All,

Guys, thanks for your answer but I just don't know what to do now.

I changed the Precison (to 5) and Scale (to 3) in the Adavnced Editor following this sequence: Advanced Editor --> Input and Output Properties --> DataReader Output --> External Columns --> Select the column then change these two Data Types Properities: Precision and Scale. I tried to re-run the package and i still have the error message.

I saw that there is another "option" in the DataReader output menu called "Output Columns". I can't change any property in this menu. I guess these columns ( in the Ouptut Columns menu) are the return types from the source table ( im using a "select" command in a DB2 table ).

Another detail: in the "Output Columns" menu, the field that is returning error, has the precision 4 and Scale 2 ( remember: i cant change these values, they are automatically assigned by the component )). I think these values are comming from the source table. Am i right?

Thanks in advance for your help and atention.

Regards.
Thiago

|||

Hi Thiago,

Yes, in the DataReaderSrc you cannot change the properties of the output columns -- they are fixed at what the provider said the metadata was for the source column. Changing it in the External Columns will have no effect...

Your original post said you used the OLE DB Src -- if that is correct, you CAN change these properties there... Can you give that a try?

Thanks,
Mark

|||

Hi All,

Finally I find out what were happening in my application.

It was not related with the SQL2005 but with the DB2 ODBC Driver.

Follow below the instrunction how to fix this error in your DB2 ODBC Driver:

When you reconfigure your connection ( in the ODBC ), you have an Advanced button. So, you should click there and look for a Service Tab. In this service tab you have some "know workarounds".
There are two workarounds in this tab ( called PATCH 1 and PATCH2). In the second patch you have an option with this description: "Always use period for decimal separator in character output". You MUST select this option then type "15" in the value text box.

After that, just click ok and your DB2 ODBC driver will be configurated.

Thank you all for your answers.

See ya.

Thiago

Error: The component has inconsistent metadata

I have a Source Query with this sql set as a property expression:

"SELECT Category, Server_Name, Entitle_UserID,User_SubID,Start_Time,End_Time,Entitle_User_Name,Stat_Name,Stat_Count,Stat_Type,pk,create_date,run_num,Average,Median,Maximim FROM tbl_ws_stats WHERE pk > " + (DT_STR, 100, 1252)@.[pk_var]

There is a message : 'The component has inconsistent metadata.'

Then when I click on the Source Query: 'The component is not in a valid state. Do you want the component to fix itself automatically?'

I also notice that there are no columns on the Column Mappings tab and no way to add columns.

How can I correct?

Thanks

Additional info, also receiving these errors when executing the package:

[DTS.Pipeline] Error: input column "" (94) has lineage ID 12 that was not previously used in the Data Flow task.

[DTS.Pipeline] Error: "component "Destination - tbl_WS_Stats" (60)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

|||

You could do worse than to (a) back up your package, and (b) let SSIS fix up the metadata automatically ... and see what results.

Donald

|||

I tried responding 'yes' when prompted to automatically fix the metadata. That did not work. Is there anyway to manually fix or another workaround?

|||What were the symptoms of the automatic fix not working? No columns?|||

That's correct. There are no columns. Is there any way for me to fix this?

Thanks

|||What value is PK (Sample values)
What value are you passing pk_var?

WHERE pk > " + (DT_STR, 100, 1252)@.[pk_var]

What happens when you remove your where clause? I suspect it's something with the where but can't tell without more information.

|||

pk is a numeric value
pk_var is a package variable with a value of 1051062000

Tried changing the sql statement to the following without the where clause, to rule out issue with pk_var. I had the same issue as before.

"SELECT Category, Server_Name, Entitle_UserID,User_SubID,Start_Time,
End_Time,Entitle_User_Name,Stat_Name,Stat_Count,Stat_Type,pk,
create_date,run_num,Average,Median,Maximim FROM tbl_ws_stats"

Thanks

|||Compare each column's source datatype with the target datatype. That is the only way you will fix this issue. One of them must be inconsistent.

(P.S. that query is structured very inefficiently and will be slow if you have many rows)