I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:
<ROOT><Accounts>...all my elements...</Accounts></ROOT>
I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?
hi my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.
i want to read the file in my root folder excluding the bin path
i have this query: SELECT top 1 DATEADD(year, -2, @sDate)AS ydate which returns 2005-01-01 00:00:00.000,how can i remove the time values and only display the date?
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?> <ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
Remove Fields From results from Innerjoin In Sql --------------------------------------------------------------------------------
Hi, I' ve been struggling for a few days with following problem. I have a query with 2 tables (below). They are joined by one key field checknr. I would only like to see field comm from table OTCUser.checklistcomm, but only the last result which are conform to the condition. So the fields in bold shouldn't appair.Could someone help me ?
SELECT MAX(p.Modifdatum) AS modifdatumv, OTCUser.checklistcomm.comm, p.Status, p.station, p.checkitem FROM OTCUser.checkliststat p INNER JOIN OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr GROUP BY p.station, p.checkitem, OTCUser.checklistcomm.comm, p.Status HAVING (p.Status = '2') AND (p.station = '00BE104740') ORDER BY p.station, p.checkitem
I have been playing around with the enterprise manager and selected Backup Database from the action menu and schedule this task for an odd time for testing.
How do I go about removing this task?
Whenever I go to the same menu item again, it just starts over .. like I don't have the task already setup.
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2.
this is transact sql running in the back ground. EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'"bak" & "trn"',N'2007-03-26T22:21:14',1
This DOESNT WORK.
It works if I just try to delete only one of the things ie trn or bak files.
On the main BIDS menubar, there is a menu for Debug. If I select Debug > Delete All Breakpoints, any breakpoint in a script task is still there afterwards. Is there a way other than opening the script task and removing it there?
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
I'm trying to use an execute SQL task with a simple select to get results and scan them. I have to create a variable for each column to get results? or may i create something like a resultset variable? Does this task return only one row and i have to loop manually? (maybe on al script task..) or can i get all returned data on a result set to be the input for next step?
How do I pass the results of a sql 'FOR XML' query to an XML Task? I tried using a user variable of type string. In my Exec SQL Task on the General tab I set the ResultType to XML. Then on the Result Set tab I map it (Result 0) to my user variable (string). Then in the XML Task I use that variable as the Source. However, it fails in the Exec Sql Task with error "End tag 'CustomJob秱孒秱怈пField' does not match the start tag 'CustomJobField'
It looks like some extraneous chars are getting inserted into my tags? I also tried setting the ResultType to SingleRow but that failed with error:
"The type of the value being assigned to variable "User::ForXmlResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag..Is it possible to fill two variables from a single SQl Task,.
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
This algorithm can be used to strip out HTML tags too. With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89973 and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90000CREATE FUNCTIONdbo.fnParseRTF ( @rtf VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE@Stage TABLE ( Chr CHAR(1), Pos INT )
INSERT@Stage ( Chr, Pos ) SELECTSUBSTRING(@rtf, Number, 1), Number FROMmaster..spt_values WHEREType = 'p' AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')
WHILE 1 = 1 BEGIN SELECT TOP 1@Pos1 = s1.Pos, @Pos2 = s2.Pos FROM@Stage AS s1 INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos WHEREs1.Chr = '{' AND s2.Chr = '}' ORDER BYs2.Pos - s1.Pos
Is it possible to skip all steps following the script task results (Step 1) in a For Each container. I am iterating thru all the files in a For Each container and parsing a few lines of the file and based on the result I want to force the For Each loop to get to the next file instead of executing the next steps. Is it possible to force the for each loop to get the to the next file if the test criteria in the very first step (Script Task) fails. Any inputs will be much appreciated.
I have generated a database for my website, I intend on using software that will convert the database into static web pages. Big problem I have I am not a programmer, but I know a tiny bit about tags etc. for search engines. The meta tag description is what I want to create using a field in this database. The software I am about to use has a sql builder is there anyway it could be done be highlighting the relavent field and using sql language. PLEASE someone Help This problem has been driving me around the twist.
I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).
With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.
I used two different flat file sources and got the following:
There are two tables A and B where asset tags are present, but in one table in rows and in another in column wise.
for eg ASSet Tag SR-062009-00032966 SR-062009-00032962 SR-072009-00020572 SR-072009-00020571 SR-072009-00020585 HH-092009-00038342
Table B field 1 -->Asset TAG Record 1-->SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617
field 2 --> Material code REcord 1-->121 REcord 2-->123
What is the query so that asset tag of A matches with each and every asset tag table of B and output comes as
Output Asset TAg -------- MAterial Code SR-062009-00032966 SR-062009-00032962 SR-072009-00020572 ------121 SR-072009-00020571 -------121 SR-072009-00020585
I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..
I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
Dim conn As New OleDbConnection Dim cmd As New OleDbCommand Dim dr As OleDbDataReader Dim QSQL As String
declare @xmldoc as xml select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>' select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
