OLEDB Command Parameter Type Is Different In Different Situations
Dec 3, 2007
Hi All,
I am using OLEDB Command transformation in a data flow to update the table. Find the columns for the table EMP as follows.
EmpID - int
EmpName - varchar(40)
EmpSal - float
Status - varchar(20)
I am using the following command to update the table.
Update EMP set status = 'Disabled' where EmpID = ? and EmpSal = ?
when I use the above condition the type of the second parameter is taking as "double precision float" as the incoming input column type is "double - precision float". This is fine.
But when I use the following condition the type of the param is taking as different one.
Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = 0
It is taking as "four byte signed integer" even though the incoming input column type is "double precision float" thus I am loosing the precision and getting error when the limit exeeds. If I use 0.0 then it is taking as "Numeric" type.
If I use convert function like (EmpSal + ? ) = Convert(float, 0) then it is taking as "double precision float".
Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = Convert(float, 0)
My question is how it behaves in the above situation. Can any body clarify please?
After a slowly changing dimension task I replaced the OLE DB Destination task by an OLE DB Command and created the insert manually. This because I need to work further on the dataset. So I do a union all between the output of the two OLE DB Commands (insert and update). Untill here no problem. But than, because I need an ID further on, I do a lookup in the table in which I just inserted and updated my data for the right ID's. When I run this project I get the error message "row yielded no match during lookup".
I don't understand this beacause I just inserted the data and I've checked, it's there.
I could resolve this by splitting up in two control flows (reselect all the needed data wÃth the ID-field in my selection) but I would prefer to solve it in another way
We are having package which transfers data from AS/400 to SQL server 2005, the source component is a data reader which connects to the AS/400 and pulls the data and some transformation are being made and then finally Oledb command is used which uses a Stored Procedure to Insert/Update the records.
The Packages transfers nearly 2,00,000 records, but after transferring some records we get the following error:
Error: 0xC0202009 at dftJDE_CUSTOMER_ORDER_ITEM, olc_JDE_CUSTOMER_ORDER_ITEM_InsertUpdate [199]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.". Error: 0xC0202009 at dftJDE_CUSTOMER_ORDER_ITEM, olc_JDE_CUSTOMER_ORDER_ITEM_InsertUpdate [199]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".
This same error is getting repeated many times. Anyone have come across this problem, any help appreciated. Thanks in advance.
I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.
I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!
It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?
I know there's no issue with the data, I imported the same file into a test table without problems.
First let me say that I'm new to SSIS, so be gentle with me.
I need to extract a limited set of data from a very large view in Oracle (I know, yuk!). The view contains millions of rows, but I only need the child matches of 343 unique keys in a one to many relationship. In pure SQL the query would look something like this.
Select proj, tn, rn, total FROM Oracle.view WHERE proj in (select distinct proj from MSSQL.dbo.projlist)
As you can see, this is an impossible query on many levels.
My first thought was to get the runtime list into a variable and use that variable as a parameter in the Oracle OleDb Source. Alas, the Oracle source will not allow me to add a parameter.
My second thought was to use a script component and build a SQL_Command string into a variable with all of my keys included. Then use the read_write variable as the SQL Command from variable in the Oracle Source. My attempts to construct such a variable expression have failed.
Table A on Server 1 (3 ROWS) ID Name Address ID1 A B ID2 X Y ID3 M N
There is another table on a different server which looks like
Table B on Server 2 PKColumn ID Details 1 ID1 Desc1 2 ID1 Desc2 3 ID1 Desc 3 4 ID2 Desc 5 ID2 Description
As you can see the ID is the common column for these two tables, I want to get the Query the above 2 tables and the output should be dumped into a new table on Server2.
I am using the following SSIS Package
OledbDataSource-------> OledbCommand(Select * from TableB where ID =?)
From here, how can insert the rows returned from the oledb command into another table. Since, for each row of TableA it will return some output rows...How can I insert all these into the New Table.
Please help on configuring the output of the oledb command.
In order to prevent lookup errors in a lookup transformation, I've decided to go for an OleDb Command Transformation.
This transformation should check the lookup and, if it turns out to be null, ir returns a dummy value. Otherwise, it would return the lookup value.
This should be done by doing something like this:
select coalesce( (select ID_Table2 from ID_Table2 where FK_Table1 = ?), 0)
suposing Table2 has an atribute called "FK_Table1" that should match a column in the data flow.
Now, such command result in this message:
"An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax eror, permission violation, or other nonspecific error".
But, it I remove the coalesce and type the following command:
select ID_Table2 from ID_Table2 where FK_Table1 = ?
It presents me no errors and allows me to continue.
Did i did anything wrong or is this something that is not possible to be done?
I know i have the option to use a script task to do this operation, but that would turn the maintenance process a little more difficult.
Otherwise, i know i could also re-direct the error from the lookup transformation and handle it. Though, my package has about 10 lookups and that would turn my package a lot more complex than
I have a stored proc with 28 commmands but only 24 are loaded. If I move the parameters around the ones that didn't show are visible and the ones after the 24th parameter don't show up. Is there some sort of limit on parameters for OLEDB commands that execute stored procs in the exec procname @p = ? construct?
I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below:
CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end;
In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked.
The error message is :provider can not derive parameter information and setparameterinfo has not been called.
In my mapping i have used one OLEDB command which updates a table. It updates 70 columns of that table. The problem is that it is taking long time to execute that(almost 15-20 minutes) It is updating almost 3k-5k rows. I tried to put the update statement in SP and called that in OLEDB command. Still it is taking same time. Please advice how to solve this problem.
We have SS2K5 source and DB2 Target. I downloaded and installed MS OLE DB provider for DB2. It is configured correctly. The data insert works fine. But I am having problem with update/delete on DB2 when passing string parameters.
The OLEDB command works fine when I hard code the values: delete from TableName where Col1='abc' and Col2='xyz'
But when I use parameters the package executes successfully, but the data is not delete on DB2.
Any one having similar experience or solution for this is really appreciated.
I'm receiving an Error Code: 0x80004005 ... a "non-specific error" when trying to use a fairly simple insert in the oleDB command transform. SQL command works fine if I specify the parameters in the values clause of the INSERT statement. If however, I try to use the parameters in setting a variable, I get the 0x80004005 error. Is this a known issue/limitation, or am I just reallly doing something wrong?
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON
GO -- ============================================= ALTER PROCEDURE [dbo].[spPK] -- Add the parameters for the stored procedure here @varNSC varchar(4), @varNC varchar(2), @varIIN varchar(7), @varIMCDMC varchar(8), @varOut as int output AS Declare @varPK int set @varPK = 0 BEGIN
--This checks Method 1 --NSC = @varNSC --NC = @varNC --IIN = @varIIN begin if exists (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varPK = (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varOut = @varPK if @varPK <> 0 Return end
[There are some more methods here]
Return
END
How do I get at the output value?
I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
OLEDB SOURCE --> LOOK UP TRANSFORMATION | lookup output / error output(for new Inserts) (updated records) / / DERIVED COLUMN DERIVED COLUMN TRANSFORMATION1 TRANSFORMATION1 | | v v OLEDB COMMAND TRANSFORMATION OLEDB DESTINATION (inserting new records to (Updating records in destinationTable) destination)
in this senario new records r insrted properly but though package runs without error records not get updated in Destination. In OLEDB COMMAND my query is like below,
In advanced editor of OLE DB I hv created additional 16 paramater columns though i assign datatype as numeric to tht columns when i press refresh automatically it changes to DT_STR. My destination table columns r numeric .
I though due to this datatype mismatch the error came So i change the datatype of dest to varchar to make compatible with OLEDB Comand Transformation. THN also no Use NO UPDATES
package is running without error but records not get updated.
if change the flow like below
OLEDB SOURCE --> LOOK UP TRANSFORMATION | lookup output / error output(for new Inserts) (updated records) / / DERIVED COLUMN DERIVED COLUMN TRANSFORMATION1 TRANSFORMATION1 | | v v OLEDB COMMANDTRANSFORMATION / (Updating records in / destinationTable) / / / UNION ALL TRANSFORMATION | v
OLEDB DESTINATION
In This Case The updated record get inserted in the target as wel as the old remains as it is means m getting one additional record.
kindly help me to figure out the bug M frusted with this issue please.............
I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table.. but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
Is it possible to access a Derrvied Column from an OLE-DB Command? I have to Update a Table with a join and i need from the source Table columns which have to be pivoted before i can use it in the update Command.
I am having a problem on updating data in DB2 target table.
I followed BJ Custard's (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1058272&SiteID=1&mode=1)(thanks amillion!) post and configured OLEDB destination to insert data. But I have to also update or delete data from the target table based on flag from source.
I tried using OLEDB command which uses the OLEDB connection created by following the steps posted in above link.
Trail 1real requirement): When I used the SQL query:
delete from table where Col1=? and Col2=?
I am unable to map to the parameters. When I click refresh button after writing the query, I get "There is a data source column with no name. Each data source column must have a name." message. Added to before message, there are no parameters to map to.
Trail 2: When I hard code the parameters :
delete from table1 where Col1='abc' and Col2='xyz'
no parameters will come up, so no mapping. So when I execute the mapping I get the following error:
Error: 0xC0202009 at Load .....................................................: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00. Lookup on above error codes show those are related more to target Db2 database.
I am sure some one might have used the OLEDB command task, not only just insert task.
I have created a sample dataflow to parse the employee details (empid,empname,empaddr) from a flat file to oracle 9i database table named employee(columns : empid,empname,empaddress - All are varchar2(15)) using SLOWLY CHANGING DIMENSION transformation for insert/update on the table.
EMPID as Businees key
EMPNAME and EMPADDR as changing attributes.
Connection string is using Microsoft oledb provider for oracle.
TITLE: Microsoft Visual Studio ------------------------------
Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.
Warning at {CF5DCB64-279E-45A4-A9A8-FF2FBB130980} [Insert Destination [1972]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Errors were encountered while generating the wizard results: Error at Data Flow Task [OLE DB Command [1996]]: An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Error at Data Flow Task [OLE DB Command [1996]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.
Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=ScdWizardGenerationErrors&LinkId=20476
I have an OLEDB command in my package that has to execute some SQL script. But when I declare and set a variable at the top of all code, The OLEDB gives an error in column mappings tab.
My DQL script is as shown below DECLARE @Cost AS money SET @Cost=? --Some update statements a table OLEDB Command works if write the declare and set statements after update statements. Like below. But I don€™t need it.
--Some update statements a table DECLARE @Cost AS money SET @Cost=?
I also observer that,Oledb Command gives error for the code given below. Just paste the following Script in OLEDB command, it gives error in column mapping tab DECLARE @Cost AS money SET @Cost=? Any Idea on this behaviour?
I have a dataflow in my SSIS Package that is supposed to transfer over all the rows from a SQL server table. I am selecting columns from the SQL table and there is a total of 1603 rows. I am only getting 354 rows in the DB2 destination table. I have turned on SQL logging and specifically the BufferSizeTuning option for this dataflow. in the Sysdtslog90 table I see messages about the "Rows in buffer type 0 would cause a buffer size greater than the configured maximum. There will only be 1249 rows in buffers of this type" and "Rows in buffer type 3 would cause a buffer size greater than the configured maximum. There will only be 1251 rows in buffers of this type"
I have the following set in the Dataflow properties. DefaultMaxBufferRows = 5000 DefaultBufferSize = 10485760
The server is windows server 2003 x64, SQL Server 2005 sp1 resides on this server where the SSIS packages are running also. 12gb of ram.
Any suggestions how i can get all the rows transferred, what is Buffer Type 0 and 3? this doesnt seem like a lot of rows (1603 total) to hit a DB2 destination with.
Any settings I would need to check in the Connection Manager? I am using Native OLE DBMicrosoft OLE DB Provider for DB2.
We run std 2008 r2. I'm trying out the commandtimeout property of an oledb source. I set it to 30 expecting 30 seconds. if connection and or execution exceed that threshold, will the pkg fail?  Either way is there a way I can detect that the threshold was exceeded?Â
I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..
'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.
I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.
Each DFT have source > Tranformation > Conditionsplit > Rowcount_Transformation >  Oledb Command                                                                                 > Rowcount_Transformation1 > Oledb Command1                                                                                 > Rowcount_Transformation2 > Oledb Command2                                                                                 > Rowcount_Transformation3 > Oledb Command3
All update hapend on diffrent Table.I want to log in Audit table .
My audit table like
Table_Name  Insert_count Update_count
How can I log the package having multiple OLEDB Destination.
I posted this question on the Analysis Services forum ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087288&SiteID=1 ) and Irina asked me to post it over here to get an answer.
I'm trying to run an MDX query against SSAS2005 with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?
The following code works: //using Microsoft.AnalysisServices.AdomdClient;
string MDX = "with member [Measures].[Test] as Str(@Param1) " + "SELECT [Measures].[Test] on 0, " + "[Product].[Category].[Category].Members on 1 " + "from [Adventure Works]";
AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;"); conn.Open(); AdomdCommand cmd = new AdomdCommand(MDX, conn); cmd.Parameters.Add("Param1", "abcde"); System.Data.DataSet ds = new System.Data.DataSet(); AdomdDataAdapter adp = new AdomdDataAdapter(cmd); adp.Fill(ds); Console.WriteLine(ds.Tables[0].Rows[0][1]); conn.Close();
The following code fails: //using System.Data.OleDb; string MDX = "with member [Measures].[Test] as Str(@Param1) " + "SELECT [Measures].[Test] on 0, " + "[Product].[Category].[Category].Members on 1 " + "from [Adventure Works]"; OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;"); conn.Open(); OleDbCommand cmd = new OleDbCommand(MDX, conn); cmd.Parameters.AddWithValue("Param1", "abcde"); //changing it to @Param1 doesn't help System.Data.DataSet ds = new System.Data.DataSet(); OleDbDataAdapter adp = new OleDbDataAdapter(cmd); adp.Fill(ds); //produces error: "The following system error occurred: The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:." Console.WriteLine(ds.Tables[0].Rows[0][1]); conn.Close();
