XML Source And XML Changes For Wrong URL Or Changed URL
May 14, 2007
Hi everyone
I need help with my data reading source which is XML Source. I have a variable which specifies the URL that the XML needs to be grabbed from and I am generating the xsd based on the contents of the URL.
Now as long as the URL has the data which is coming from webservice, it works fine. But if the URL is not correct or need additional information. The XML Schema changes to let the user know that "city" information for example is needed. This message and XML is completely different if the URL sends the required data.
I want to know how to send an error message or some kinda of message so that another activity would be done if the URL needs the city information.
I tried placing an XML source another one, for that error message. But because thats one line, just asking for city information. XML souce has no columns in it. So I have a feeling that even before getting into the XML source... data in the XML file needs to be checked. And how can I do taht I dont know..
I am trying to edit an SSIS package due to a change in the schema of one of the source tables. When I open the package in Visual Studio, I see the exclamation mark on the object but before I can make change or go to edit anything in the package, I hear the "dumphhh" sound that indicates an error. There is no dialogue box but the program is now locked up. Then after 10 - 20 seconds the program closes.
How am i supposed to add the new column to the data source metadata if I can't edit the package?
Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:
we have a table in our ERP database and we copy data from this table into another "stage"Â table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.Â
Hi, I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task). After that, in an xml task, I set the input properties like that: Operation type: xslt SourceType: File connection Source: ras.xml
All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that:
Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90 Me.VariableDispenser.LockOneForWrite("RAS", v) v("RAS").Value = doc.OuterXml v.Unlock()
and in the xml task, I set configuration like that:
But when I execute, I got this following exception:
Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.". Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed.
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
I am having a huge xml file with nested section. i also have a xsd file for that xml. i have a destination table where the data from the xml should be loaded into. i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination. on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files. also i would like to know any other better way of getting the data from xml source without using the joins.
I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table - Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
hi all im really have a problem in my project. i have server and client side each side contain SQL Server DB. and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed.
now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).
my problem :
u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).
so i do this to know changed cell range: when run the program saved all excel tabel into SQL server table. and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.
and rise event to send this row to client by socket over internet to update the row in client side too.
but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.
this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)
and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)
Maybe somebody has asked this question, but I could not find it....
I used SSIS to transfer data from Oracle database to SQL Server 2005 (with Microsoft OLE DB Provider for Oracle). It worked fine with the TNS alias name said oracle1. Then I changed the TNS alias name to oracle2. I could connect to Oracle database with oracle2 using Oracle's tool. But SSIS could not find (or solve) oracle2, if I changed the name back to oracle1 in SSIS, then SSIS worked.
I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.
I would appreciate, if anybody has done so and can give me some steps for me to work out.
I have a page that uses an SQLDataSource web control to populate a GridView control. The SQLDataSource control uses three parameters, one of which is a text string (varchar(50)). I get timeout expired exceptions when I run the page with two specific string values, yet when I run the stored procedure in SqlSvr Management Studio, the procedure runs in 5 seconds and under 1 seconds. Being rnew to ASP.NET 2.0 and .Net in general, I have no idea where this lag is occurring. I would like to know two things: 1. What is causing the lag that causes the timeout in ASP.NET 2.0. 2. What can be done to adjust the timeout period SQLSVR, IIS, ASP.NET). The underlying query is running as desired (considering the data it has to chew on). For all other values, data is returned almost instantanious. I can provide a copy of the query id need be. Truly appreciate any insight you can give me!
i changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename I tried entering it with the trim command still no good i looked a the sql command made in vs and it says that the variables are varchar is there something i am missing with varchar does it only work properly if it is over 50 or does it not change completely when you change from char to varchar
i have table Table1 with column lastname ,firstname ect.The Data type is varchar. I have inserted values. I have selected records from this table and assign this records to the TDC control of the HTML .The query returns 130 records.The TDC control has the Unicode delimiter. Whene we select top 7 ,seven records are displayed ,when it is top 8 it does not show any records. If we delete the Lastname and Firstname values for this 8th record then all the 130 records are displayed. Why is this happning.Any great minds can help me in this pls
First of all, let me apoligize if this is a stupid question, but just a few days ago I decided to change my FE/BE database from Ms Access to SQL Server Express. Now I have a FE in ACC2002 and a BE in SQL SERVER (the two connected via ODBC). Anyway, this is my question:
When everything was in Access I had a form retreiving a series of records based on a filter - for understanding purpose let's say that a field must have been 'Not Null'. The operator then filled that field with data. With Access everything was static, and the operator could see all the records, the ones already changed and the ones not changed. With SQL now: everytime the operator fills in the field, the record changes to #Deleted. I assume that that is happening because the query - someway - is "alive" and is "filtering data alive"... or is updating records instantly. Just to make things more clear: is the record changed immediately so it is not considered part of the filtered data by the query, thus being change to #Deleted? What could I do to prevent this?
CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10)); INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2'); INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2'); INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2'); INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3'); INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2'); INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3'); INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4'); INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');
Here based on latest revision and refid I should get the fieldnam and value. Expected output: REFID FIELDNAM VALUE REVISION 1001 A A4 1 1001 E E4 1 1002 B B2 0 1001 C C2 0
I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table. THX Inon
First I want to query everyone with a date greater than today in the field EXPIRATION DATE. I know I can say > 2006-03-19 but I always want it to be TODAY.
Next, Is there a system field that I can search for the date a record was last updated?? I make frequent changes, and I want others to see what date a record was last updated. If not, can I tell the system to mark a field "YES" when I make changes?
I use phpmyadmin. I do not have server access, just through PHP.
Hi experts,I have been trying to limit the table rows in the following situation,any suggestions will be appreciated.we have table called tempTb has columns id, c_id, c_name, rating, datecolumns.id is an identity column.date is a datetime column, the rest are varchar datatype.Here is the table structure with sample data,idc_idc_nameratingdate1aoamer onli11/1/20022aoamer onli13/1/20023aoamer onli16/1/20024aoamer onli39/1/20025aoamer onli312/1/20026aoamer onli33/1/20037aoamer onli36/1/20038aoamer onli39/1/20039aoamer onli212/1/200310aoamer onli16/1/200411aoamer onli112/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200214xyxabs yasd26/1/200215xyxabs yasd29/1/200216xyxabs yasd112/1/200217xyxabs yasd13/1/200318xyxabs yasd36/1/200319xyxabs yasd39/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/200422xyxabs yasd112/1/2004[color=blue]>From this table I need to select the rows with rating changes only,[/color]i.e if two or three consecutive rows have same rating only the firstrow should be selected.the selection should look like...idc_idc_nameratingdate1aoamer onli11/1/20024aoamer onli39/1/20029aoamer onli212/1/200310aoamer onli16/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200216xyxabs yasd112/1/200218xyxabs yasd36/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/2004I was trying to do this by self-joining the table like....select t1.* from tempTb t1, tempTb t2where t1.id!=t2.id,t1.c_id=t2.c_id,t1.c_name=t2.c_name,t1.rating!=t2.rating.But this is generating cartesian products,I have tried some other combinations after where clause with date colmnwtc,but none seems to give the required result.so if anybody can guide me in the right direction I would appreciateit.Thanks alot,Remote
I have a stored procedure that inserts values into five columns of atable. I need another stored procedure that will allow the user to passone or more of those parameters and update only the column for the datathat was passed. In other words, the update may only have one or two ofthe parameters that was originally provided in the insert. Therefore, Ido not want to update the columns that did not change. What is theproper way to handle this situation?
I need to create a table that would be the result set of a comparisonbetween table a and table b? Table a and b first 2 fields will always bethe same (CustomerName and CustomerNumber). But if the Address1 fieldchanges in table a, I would like to throw that whole row into mycomparison table. Almost like a Select Into with a sub query that wouldinclude a WHERE TableA.field <> TableB.field. I would need to do thiscomparison for about 8 fields. Help appreciated for my syntax is prettybad. Thanks.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have never written a trigger before and now am seeing the light. Isthere a way to write a trigger so that if a user changes any column ina single row on one table then the trigger will write the value ofthis (these) rows to a second table. I don't want the unchangedcolumns, just the changed columns (with column names...)Thanks,lq
When I create tables in SQL I can specify dbo as the owner using thesyntax below but when using the upsize wizard in Access 2000 I owneverything. Is there a way that the system administrator can changeownership after the fact?CREATE TABLE dbo.mytable (c1 int not null)Thank You,Randy KJoin Bytes!
Hello!We are running MS SQLServer 7.0 with ASP on Win 2k Server.Today suddenly the Web server couldn't connect to the DB Serverbecause the password for sa was wrong.We found out that the password was changed.Is it some kind of hacking thing or just normal password expiration?The same thing happended several month ago.Thanks.Sam
