Ambiguous Complex Type Definition With XML Source. Workaround?
Jan 19, 2006
Hi,
This could well be down to my _limited_ knowledge of XSD.
I have a document and SXD supplied by 3rd party.
Both documents are valid, according to XMLSpy.
When I give the document and xsd to SSIS XML Source it complains about ambiguous complex types.
In the XML doc there is an element called Allowance that has child elements.
There is also a group which references many other elements including Allowance.
When I remove the group, SSIS stops complaining about allowance.
Would the problem stem from SSIS creating an output called Allowance ('cause of it's children), getting to the group and again, 'cause allowance has children, try create another output called Allowance.
Is my understanding of this correct? Is there a work around for a situation like this?
The only thing I can come up with is deleting the group....
Possible to alias an element? Could alias the Group > Allowance g_Allowance.
I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not: <asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>' Enabled="False" /> This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display. I checked the SQL statement, and sure enough, it has theUpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @Shipping... WHERE [PartID] = @original_PartIDAfter fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.Any insight as to how to get this to work is much appreciated. Thanks in advance.
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected we need to store values as is
Error: base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range. --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at MS.Internal.MS COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default); DataRow dr; DataTable dt = new DataTable(); DataColumn dc;
try {
dc = dt.Columns.Add("Availability", typeof(decimal)); €¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal €¦€¦€¦.
Hello everyone, I am newbie in SSIS and I have one problem. I have defined in Control Flow one WebService Task, specified the WSDL-File. I have chosen one method from WS as Input. But this method requires one parameter of the ArrayOf_xsd_string type. If I try to choose it, I receive following error:
=================================== This complex type does not contain any properties. (Microsoft.SqlServer.WebServiceTaskUI) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.CustomGridControl.HandleEllipseBtn(Int64 row, Int32 col)
I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
I am trying to make a call to a third-party web service in my SSIS package. The request has custom complex data type as the parameter. As has been pointed out in this forum before, the Web Service Task only lets you assign the outside parameter from a variable, not the internal parameters needed to create the complex data type.
To be more specific, the web service input wants a 'ContactSearchRequest' parameter. I can assign this from a variable. If I click on the 'value' field under the 'Input' section for the web service task, it shows me that the 'ContactSearchRequest' data type is made up of the following:
contactId - long numResults - int offset - int passKey - string searchParam - string sortType - int
Unfortunately, I can't assign these internal parameters from a variable, at least not through the web service task interface.
My next thought was to create a variable of type 'object' and then set it in a script task prior to calling the web service task. However, I'm not sure exactly how to do this. How will my script know about the class definition of 'ContactSearchRequest'? Do I just create a class called 'ContactSearchRequest'?
I've used this same web service in a .NET C# project and after I imported the web service, visual studio knew all about the custom data types. How do I do something similar in SSIS?
Of course, the easiest solution would for Integration Service to allow me to set those internal parameters via variables, but we're apparently not there yet.
Any recommendations for good advanced t-sql books/articles? I find myself involved with writing increasingly more complex queries and after spending a few hours on some, and then searching on this site for potential answers/help, I am wondering if there might be some good books on creating more advanced/complex t-sql for real world scenarios.
Need a guidance on consuming the WCF service with complex type in the SSIS package. I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".
I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.
For example:
The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:
Employee { FName String; LName String; Age int }
It is possible to consume this WCF service and pass the arguments.
But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:
Employee { FName String; LName String; Age String Type EmployeeType; }
EmployeeType { type string; }
Now I get the error Web Method has unsupported arguments.
When you pass a complex type (the one represented by class) to a web service the BIDS UI allows you to enter values for every field of that type as constants. But what if you want to pass a variable? Once again the UI allows you to specify a variable for that complex type parameter. But how to make this variable in SSIS?I understand it should have the type of Object. But how to specify what the runtime type of this object is? And how to assign all fields to that object?
I've been working on an application that uploads an RDL to Reporting Services programmatically. I'm having problem with the data source properties for my uploaded report. The datasource for my report is custom datasource. It work s fine if the report still did not exist. But if the report already exist, after I upload the report to Reporting Services, the function will overwrite the RDL but it did not overwrite the Data source type correctly. Other setting like connection string is overwritten correctly.
For example, I already have RDL ABC with Data source type SQL, when I try to overwrite the existing RDL ABC which have Data source type Assembly, it still hold the SQL value for the data source type. But in the RDL itself (I checked in the rdl script in <DataProvider> tag), the value already overwritten to Assembly.
This problem also happen when I try to upload RDL using Report Manager. The Data source type are not overwritten by the action.
What should I do? Why it did not overwrite the datasource type? Maybe I do anything wrong here. I dont know what. Hope someone can help.
I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.
Is there anyway around this? Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
Hi Using ASP.NET 2.0, Sql Server 2005. I have a simple page (NOT a formview) with some entries textbox's , checkbox and dropdownlistbox's I want to link a datasource to the 'Item Page' and bind the datasource's values to the page The select statement is Select a.IssueID, a.ProjectID, a.VersionID, a.toincludeversionid, a.Version, a.toincludeversion, a.TypeofEntryID, a.PriorityID, a.WorkFlowID, a.Title, a.Area, a.Details, a.Question, a.Answer, a.HowToRepro, a.DevelopersNotes, a.TestersNotes, b.ProjectID, b.ProjectName, OldVersion.Version, ToIncludeVersion.Version, d.DESCRIPTION, e.DESCRIPTION,
x.TaskID as TaskID, x.DESCRIPTION as TaskDescription, z.Taskdone, CONVERT (char(9),z.TaskAssignedDate, 3) AS Workflowdate, z.StaffID as StaffID, w.username, y.latest_workflowid from issue as a Inner join ProjS b on b.ProjectId=a.ProjectID Left Outer join Version OldVersion on a.VersionID=OldVersion.VersionID Left Outer join Version ToIncludeVersion on a.VersionID= ToIncludeVersion.VersionID Inner join TypeOfEntry d on d.TypeOfEntryID=a.TypeofEntryID Inner join Priority e on e.PriorityID=a.PriorityID
inner join workflow z on z.issueid=a.issueid Inner join (select issueid,max(workflowid) as latest_workflowid from workflow group by issueid) y on y.latest_workflowid=z.workflowid Inner join task x on x.taskid=z.taskid Inner join staffls w on w.StaffID=z.StaffID
Where a.IssueID= @IssueID
I hope I have made query clear, if not I don't mind explaining more.
Running SQL 2008. Trying to copy data from one table into another table using SSIS Import/Export Wizard. Now, when I do a straight "Copy data from one or more tables or views", no problems. But when I use the "Write a query to specify the data to transfer", it will not let me get anywhere.
My source table has a field that is setup as "time". It has data, and no problems with the field. I even replicated my destination table structure exactly. But when I try to use the Import & Export wizard, for that one field I get an error stating the source field is unknown and it is labeled as "-1" instead of "time".
I found a couple of of workarounds. One is to cast the source field "time" as "datetime", and then end up with a "datetime2" field in the destination table. Works, but not what I want to store in that field. Second workaround is to use TSQL and use a "INSERT INTO...SELECT...FROM..WHERE.." statement. This works, and gives me the desired results with all data types being same in source and destination, but is a slight pain in the rear end.
I just want the Import & Export wizard to work. It should work. Why doesn't it know what "time" is? I even checked the MSSQLToSSIS10.XML mapping file the wizard is using. This is what it has for "time":
I'm using the XML Source to process a hierarchical set of XML. As such, the XML Source creates keys to maintain the hierarchy. This is very convenient, and keeps me from having to invent my own keys.
The problem is that the datatype of these keys defaults to DT_UI8. Which SQL Server 2005 datatype should I use to store these values in my staging tables? BIGINT corresponds to DT_I8, which can't accept DT_UI8 values.
My situation is that Excel files are to be downloaded into a SQL Server 2005 table (perhaps as type image or nvarchar), which serves as a document repository. From there, they should be converted to XML. Use of an NT file directory is strongly discouraged. I would like to have SSIS read the Excel from one field in a table and then write the XML into another field in the same (or perhaps another) table. Is this possible? If not, is the a strait-forward way to do this?
Also, I€™m hoping to invoke the SSIS script from a SQL Server INSERT trigger so the conversion is done during the INSERT.
Dear experts, while i'm trying to insert data , i got error like Ambiguous column name...that is related to joins the query is INSERT INTO MATABLE044 SELECT newid(), ledger.COLUMN002, wh.COLUMN001, Loc.COLUMN005, ledger.COLUMN008, ledger.COLUMN009, ledger.COLUMN010, ledger.COLUMN011,0, 1-1-1900, ledger.COLUMN006, ledger.COLUMN007, ledger.COLUMN014, 'Y', ledger.column016,ledger.COLUMN020, ledger.COLUMN021, ledger.COLUMN022,ledger.COLUMN023, ledger.COLUMN024,ledger.COLUMN025,ledger.COLUMN026,ledger.COLUMN027,Loc.COLUMN005,ledger.COLUMN029,ledger.COLUMN030,ledger.COLUMN002EE, 'NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','SNSP_BRANCH','SNSP_BRANCH','SNSP_USER',convert(varchar(19),getdate(),120),NULL,convert(varchar(19),getdate(),120),newid(),'DBMS_REPUTIL.GLOBAL_NAME','en'
FROM MATABLE043 ledger Inner JOIN MATABLE012 wh on ledger.column002 = wh.column002 Inner JOIN MATABLE024 Loc on wh.column001 = loc.column004 WHERE COLUMN002 ='c5a0bcc4-743a-4d9a-80a5-7c2ebbf6deec' and COLUMN006 = '3d42a36e-7df7-41cd-a6ee-189f4e3f2ad5' AND COLUMN009 NOT IN ( SELECT COLUMN006 FROM MATABLE044)
what is the meaning
thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
t1.[Converted_Mortgage_Number], t1.[Converted_Mortgage_Advance_No], t1.[Original_Account_Number], CASE WHEN t3.description IS NOT NULL THEN t3.description ELSE t1.CACS_Location_Code END AS CACS_Location_Code, t1.[Original_Account_Number_1], t1.[Payment_Amount], t1.[Letter_ID], t1.[Batch_Activity_Date], t1.[Activity_Date], t1.[Activity_Time], CASE WHEN t6.description IS NOT NULL THEN t1.[Collection_Activity] + ' - '+ t6.description ELSE t1.[Collection_Activity] END AS [Collection_Activity], CASE WHEN t5.description IS NOT NULL THEN t1.[Party_Contact_Code] + ' - ' + t5.description ELSE t1.[Party_Contact_Code] END AS [Party_Contact_Code], CASE WHEN t7.description IS NOT NULL THEN t1.[Place_Called] + ' - ' + t7.description ELSE t1.[Place_Called] END AS [Place_Called], t1.[Promise_Amount_1], t1.[Promise_D_ate_1], t1.[Promise_Amount_2], t1.[Promise_D_ate_2], CASE WHEN t8.description IS NOT NULL THEN t1.[Non_Pay_Excuse_Code] + ' - ' + t8.description ELSE t1.[Non_Pay_Excuse_Code] END AS [Non_Pay_Excuse_Code], CASE WHEN t2.description IS NOT NULL THEN t1.CACS_State + ' - ' + t2.description ELSE t1.CACS_State END AS CACS_State, t1.[User_ID/Transaction_Type], t1.[Arrears_Amount], t4.[Sequence_Number], t4.[History_Text], ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber
FROM format_cacs_cch AS t1
LEFT OUTER JOIN format_cacs_history_1 AS t4 ON t1.Converted_Mortgage_Number = t4.Converted_Mortgage_Number AND t1.Activity_Date = t4.Activity_Date AND t1.Activity_Time = t4.Activity_Time
LEFT JOIN sd_cacs_states AS t2 ON t1.CACS_Location_Code = t2.location AND t1.CACS_State = t2.state
LEFT JOIN sd_cacs_Location_details AS t3 ON t1.CACS_Location_Code = t3.code
LEFT JOIN sd_party_contacted_code AS t5 ON t1.[Party_Contact_Code] = t5.code
LEFT JOIN sd_collection_activity_code AS t6 ON t1.[Collection_Activity] = t6.code
LEFT JOIN sd_place_called AS t7 ON t1.[Place_Called] = t7.code
LEFT JOIN sd_non_pay_excuse_code AS t8 ON t1.[Non_Pay_Excuse_Code] = t8.code
WHERE (t1.Converted_Mortgage_Number = '$mortgage' $additional_date_params)
I have a package that I migrated from DTS. There are several "execute SQL" tasks that take an Input parameter of "myVariable", which is a string defined at the package level and has a default value assigned to it.
However, the package is throwing this error:
Error 1 Validation error. Err LogComletion : Failed to lock variable "myVariable" for read access with error 0xC001F032 "The variable name is ambiguous because multiple variables with this name exist in different namespaces. Specify namespace-qualified name to prevent ambiguity.".
How come I cannot use this variable when defined at the package level? If I move the variable to the task level, it works fine, but I want to re-use the variable in several places in the package.
The very simplified version of my problem is that these
Select DISTINCT Cast(KWID as NUMERIC) FROM OV_MID
Select DISTINCT Convert(Numeric,KWID) FROM OV_MID
should work, but don't because KWID is a varchar and somewhere in there is something that won't convert.
I get this error: Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.
I would love to find out which rows are causing the error, but more importantly I would like to have a Null value where the conversion doesn't work and the numeric values where it does work.
I have already deleted all obvious non-numeric characters, but I believe there are some line terminators being read as carriage returns in this table. :confused:
Any workaround or way to determine which rows have KWID that cannot be converted to numeric would be most appreciated.
Hi All,I have read about deadlocks here on Google and I was surprised to readthat an update and a select on the same table could get into adeadlock because of the table's index. The update and the selectaccess the index in opposite orders, thereby causing the deadlock.This sounds to me as a bug in SQL Server!My question is: Could you avoid this by reading the table with a'select * from X(updlock)' before updating it? I mean: Would thisresult in the update transaction setting a lock on the index rowsbefore accessing the data rows?Merry Christmas!/Fredrik Möller
I'm planning an upgrade to SQL Server 2012 and noticed the following.
We have a query that includes a duplicate column name in the SELECT statement. This query happens to work in a db we have that is set to SQL 2000 (80) compatibility level, but the same exact query fails when I set the db to SQL 2008 (100) compatibility level.
I'm brand new to this forum and I'm stuck. We've got a query which queries three tables: booking, catheter and patients. The following (dodgy) query has pulled off the data for years, without problems:
SELECT CARDIAC_CATHETER.CARDIAC_CATHETER_ID, CARDIAC_CATHETER.OPERATOR1, CARDIAC_CATHETER.TEST_DATE, BOOKING.STATUS, BOOKING.TEST_DATE AS BOOKING_TEST_DATE, BOOKING.PROCEDURE_DATE_TIME, CARDIAC_CATHETER.ITEM_NO, CARDIAC_CATHETER.BILLING_CATEGORY, PATIENTS.REFERRING_PHYSICIAN1, PATIENTS.HOSPITAL_UNIT_NUMBER FROM (CARDIAC_CATHETER INNER JOIN PATIENTS ON CARDIAC_CATHETER.PATIENT_ID = PATIENTS.PATIENT_ID) LEFT OUTER JOIN BOOKING ON PATIENTS.PATIENT_ID = BOOKING.PATIENT_ID AND 'CATHETER' = BOOKING.CLINIC AND BOOKING.STATUS <> 'CANCELLED' AND BOOKING.PROCEDURE_DATE_TIME >= (CARDIAC_CATHETER.TEST_DATE - 1) AND BOOKING.PROCEDURE_DATE_TIME < (CARDIAC_CATHETER.TEST_DATE + 1) WHERE CARDIAC_CATHETER.TEST_DATE > '2005-04-01' AND CARDIAC_CATHETER.BILLING_CATEGORY LIKE '3%'
About 2500 perfect rows and 12 rows which have duplicate catheter id values. There are six pairs of duplicated records. It's only the cardiac_catheter.catheter_id and cardiac_catheter.test_date which are duplicated - the first row shown below contains correct values, the second doens't. The other values returned, on both rows, are correct. Here is some sample data:
