XSLT Transformation On XML Data Stored In SQL Server 2005
Jun 27, 2007Hi
Does anyone have any information on how I can due a XSLT Transformation on XML Data Stored in SQL Server 2005?
Thanks for the help
Bones
ADVERTISEMENT
XSLT Transformation In XML Task
Sep 10, 2007 Hi,
I've a xml file and I want to substitute some of the values from database in this xml using XSLT transformation making use of XML task. How can this be achieved?
Thanks
Data Transformation Services In SQL Server 2005
May 16, 2008In SQL Server 2000, i'm using Data Transformation Services. What a similar DTS tools in SQL Server 2005.
View 4 Replies View RelatedData Transformation Project Template For SQL 2005
Aug 1, 2006Does anyone know where I can download and install the Data Transformation Project template for SQL Server 2005 Integation Services?
XML, XSLT And SQL Server
Jun 14, 1999I want to delete and recreate the master Database Device for purpose of shrinking the size of the device .
I dont want to loose the Master db.
I thought of transfering the Master db to a different device,
Delete and recreate the master device and then tranfer back the master db .
and this is all done through the MS SQL enterprise Manager (SQL 6.5 )
Is this the safest eay to proceed with this task?
Any thoughts are greatly appreciated.
Thank you
Guss Hasb
Transfer Data To Excel 2007 By Using SQL Server Data Transformation Services
Jun 11, 2007My vendor requires data to be sent in Excel format. Some of my tables have rows over 65,536 so I need to use Excel 2007 (Max of 1,048,576). Right now my data sits in SQL 2000. I am using MS SQL Enterprise Manager 8.0 to prepare the data. Is there some kind of add on or selection I am missing to use DTS to export from SQL to Excel 2007?Thanks in advance.
View 3 Replies View RelatedProblem Using Merge Join Transformation In Sql Server 2005(SSIS)
Aug 10, 2006Hi,
I am pretty new to SSIS. I am transferring some rows from 2 source tables to 1 destination table.
The 2 source tables have 1000 rows.They act as the 2 inputs to a merge join transformation where i perform the join between the 2 tables based on a couple of fields. But for some reason the output of the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows?
How do i solve tis problem?
Thanks in advance
Sat
OLE DB Command Transformation, Stored Procedures + Parameters
Dec 20, 2007
Hi,
When I execute a stored procedure from an OLE DB Command transformation, where the sp takes a parameter and RetainSameConnection=TRUE and DelayValidation=TRUE are set, I get the error
"Syntax error, permission violation, or other nonspecific error"
If I take out the param or set RetainSameConnection=FALSE on the connection, all is fine again?
Has anyone has come across this?
Cheers
XML Task && XSLT
Feb 23, 2007Can I use the XML task in SSIS to create an excel document? If so which operation type is best to do it with, XSLT?
I'm trying create excel files dynamically with nothing more than a SQL statement that I'm passing as a variable which generates XML. I would then like to take that variable and combine it with a template and create an excel document. Any help would be appreciated. I do not want to use the data flow because it requires all transformations ahead of time.
Thanks,
Phil
Displaying Xml With Xslt In RS
Sep 10, 2007
Hi,
I've got an xml and an xslt - I want to get that into reporting services. Right now I have a link to the xml file in a 'report', which will open it correctly and format with the xslt. I'd like it to display without going to an external link.
I know how to use an xml datasource, but I need the xslt applied, since it has some nice formatting in it - so i don't think that will work.
I'm trying to report on the results of a scripted ms baseline security analyzer of several servers - the style sheet lets you drill down and has links to the base reports.
Thanks for your help.
Does A Synchronous Transformation Process All Rows In A Buffer Before Outputting To Next Transformation?
Jun 5, 2006Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
Integration Services :: Difference Between Audit Transformation And Row-count Transformation?
Apr 22, 2015tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
How To Change Xml With Xslt In Ssis
Mar 3, 2008Hi,
I've got a problem with my ssis package.
I have a webservice, which from i am downloading xml file which looks like this:
Code Snippet
<?xml version="1.0" encoding="utf-16"?>
<DataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="lukas_id_nagrody" type="xs:string" minOccurs="0" />
<xs:element name="ilosc" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<Table diffgr:id="Table1" msdata:rowOrder="0">
<lukas_id_nagrody>10</lukas_id_nagrody>
<ilosc>4</ilosc>
</Table>
<Table diffgr:id="Table2" msdata:rowOrder="1">
<lukas_id_nagrody>12</lukas_id_nagrody>
<ilosc>10</ilosc>
</Table>
<Table diffgr:id="Table3" msdata:rowOrder="2">
<lukas_id_nagrody>21</lukas_id_nagrody>
<ilosc>32</ilosc>
</Table>
<Table diffgr:id="Table4" msdata:rowOrder="3">
<lukas_id_nagrody>32</lukas_id_nagrody>
<ilosc>13</ilosc>
</Table>
<Table diffgr:id="Table5" msdata:rowOrder="4">
<lukas_id_nagrody>33</lukas_id_nagrody>
<ilosc>15</ilosc>
</Table>
<Table diffgr:id="Table6" msdata:rowOrder="5">
<lukas_id_nagrody>34</lukas_id_nagrody>
<ilosc>2</ilosc>
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
And i want it to looks like this:
Code Snippet
<?xml version="1.0" encoding="utf-16"?>
<DataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="DataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="lukas_id_nagrody" type="xs:string" minOccurs="0" />
<xs:element name="ilosc" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table id="Table1" rowOrder="0">
<lukas_id_nagrody>J07Z0</lukas_id_nagrody>
<ilosc>1</ilosc>
</Table>
<Table id="Table2" rowOrder="1">
<lukas_id_nagrody>J08Z0</lukas_id_nagrody>
<ilosc>1</ilosc>
</Table>
<Table id="Table3" rowOrder="2">
<lukas_id_nagrody>J09Z0</lukas_id_nagrody>
<ilosc>1</ilosc>
</Table>
</DataSet>
Can You help me?
Thanks in advance
ps. sorry for my english
Data Transformation
Sep 14, 2000We are transferring data between AS/400 and SQL Server 7.0 using DTS. Some of these transfers may need to be very close to real time. It doesn't seem like a continuously running job is the best solution for that.
Do you know any tools or utilities that can help us to move the data?
Thank you,
Anastasia.
Data Transformation
Feb 19, 2003i have something like this:
select * from accounts
name type amount
==== ==== ======
mary saving 123.00
mary chequing 246.00
mary investment 135.00
john saving 678.00
john chequing 987.00
john investment 0.00
what should i do to present the data in the following format?
name saving cheq investment
==== ====== ==== ==========
mary 123.00 246.00 135.00
john 678.00 987.00 0.00
Thanks.
Data Transformation
Jan 12, 2008Hi, newbie here with a simple?(maybe)question.
I have an Access Database that I have imported into SQL Server2000 and that worked great, but now I have to get it into 2005. My question is, How can I get the tables and all info in the tables into an SQL Script so I can run that script on the 2005 server?
The SQL 2000 is on my dev server and I have all the Tools, (Ent Manager, Query Analyzer,etc...) but the 2005 Server is Godaddy's and they only have the basic web interface. I can run Sql files and create databases and tables, but thats about it.
Generate RDL Using XSDObjectGen XSD.EXE XMLTextWriter Or XSLT...
Mar 14, 2006I'm writing some code to generate RDL based on a set of existing tables that define reports. (Headings, columns etc.)
The options I'm exploring so far for doing this are as follows:
Use XMLTextWriter class
Use XMLDocument class
Use XMLSerialiser to serialse a set of classes made with XSD.EXE and
Use XMLSerialiser to serialse a set of classes made with XSDObjectGen
Use XSLT to convert MyReportDefinitionDataset.GetXML into RDL
Has anybody else out there seen other code that does anything like this or in general has any suggestions to help me narrow these options down?
Note:
I am hoping to allow users supplying RDL their own files as templates enabling them to define things like report header and footer etc.
My code will take the Table element from the template RDL and replace it with my generated XML (Headings and columns etc.)
Any suggestions/help/existing sample code appreciated.
Thanks,
Mike G
XSLT Transform Dropping Spaces, CR's
Jun 15, 2006I have an XSLT transform that works perfectly using the msxsl.exe utility.
When the same XSL file is run through an SSIS XML transform, the character spacing and carriage returns embedded in the XSL templates are mostly (but not completely) dropped.
Any comments on why SSIS is behaving differently than msxsl.exe? What to do?
Thanks in advance,
Richard
XML Task: How To Pass Arguments To XSLT
Aug 10, 2006In the XML Task if you set the OperationType to XSLT, how do you pass arguments to the Transform like you would in .Net by using the XsltArgumentList class? Thanks.
View 3 Replies View RelatedMessage Output From XSLT XML Task
Sep 11, 2006Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.
View 4 Replies View RelatedData Transformation Services
Jul 18, 2007HiI was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.I have tried to install DTS with no success. I am running the followingVisual web studio expressSQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra componentsInstalled SQLEXPR_Toolkit.exe with all its optionsInstalled SQLServer2005_DTS.MSI When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.Can somebody please help me.
View 2 Replies View RelatedSSIS Data Transformation
Jan 31, 2008I have begun using SSIS and I am a little taken aback by the complexity of it especially since I just want to do a simple data transformation such as in DTS.
Are there any tutorials for data transformation for SSIS on the web/this forum and what if I want to do a simple transformation from Access to SQL Server?
DTS Error During Data Transformation
Nov 8, 2000I tried transforming data from one server to another using DTS. and then i got an error as below,
-----------------------------------------------------------------------------
Details: Error Source: Pump Data Step
Details: The Data Transformation Services cannot copy or transform data from a Desktop or server to a standard, Enterprise, or small business server version of SQL server unless your destination server is per user licensing mode.
Facility: 4, Severity 1,Code: 1176, HRESULT:0x80040498
Desc: The Data Transformation Services cannot copy or transform data from a Desktop or server to a standard, Enterprise, or small business server version of SQL server unless your destination server is per user licensing mode.
Source : Microsoft Data Transformation Services(DTS) Package
Source : Microsoft Data Transformation Services(DTS) Package
Code: 0x80040428
Description: Package failed because step'Pump data step' failed.
Error Message: IDispatch error #552
--------------------------------------------------------------------------
This is the full description of the error dialog...
Please suggest me some solutions....
Thanks in advance,
Kamalesh D
Data Transformation - Hanging
Jul 20, 2005I'm running a DTS package on SQL Server. The source is MS Access and thetarget is Oracle.On a "Drop Table" command the process just hangs. There are no foreign keys onthe table. Several tables have already been processed successfully by thistime.I think I've ruled out corruption by dropping and recreating the targetdatabase on Oracle.Any ideas?M Man
View 1 Replies View RelatedLookup Transformation (Can It Be Using The Old Data?)
Mar 31, 2008
I have a lookup transformation that retrieves a key for a certain column of values, in this case, a name. So, I go in to the lookup table with a name and come out with its key. I had it working and then I added new entries to the lookup table for a bunch of new names. Now, for some reason, I am not getting the matches for the new names. But I am still getting the matches for the names that existed before I added the new ones.
I'm wondering if the lookup transformation is using the old set of data and some how not picking up the new names. Do I have to trigger something in the lookup transformation to let it know that the lookup table data has changed?
Data Transformation Question
Sep 6, 2006I have a student table that needs some clean up. My first task is to remove all the periods (.) from the middle name column. Some people have two char middle initials with two periods. Can this be done via a SSIS package (I sure it can, but not how)? I can't think of a simple update statement that would accomplish the same thing.
Any direction would be appreciated.
Strip DTD Out; Help With Some XSLT Inside Of SSIS XML Task
Sep 4, 2007I'm reading over this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884062&SiteID=1
and I'm kinda lost as to what to do to strip out the dtd from an XML file I am downloading. I do NOT know XSLT and for that reason, I can't follow his logic.
My SSIS package downloads my XML file just fine, now I need to do a strip of the DTD line in my XML Task.
The person who provided the solution in the above post said to do this...
Code Snippet
Operation Type: XSLT
Source Type: Variable
Source: Variable's name containing the xml text
Save Operation Result: True
DestinationType: Variable
OverwriteDestination: True
Destination: Variable's name which is to contain the original xml minus the DTD.
SecondOperandType: Variable
That stuff I understood. I'll replace variables with my files because they are stored that way, but from what I can tell, that's not my problem.
The stuff he says below this comment is going over my head like a ton of bricks. I can't figure out how to do it.
This is the kind of line of my XML that I want to strip out.
https://www.myaddy.com/pbdr.dtd"[]>
and then he said this...
Code Snippet
Since XSL doesn't know about DTDs, telling it to copy everything strips out DTDs. Then use the Variable specified in the Xml task's SecondOperand as the Source data for the xml source.
Code Snippet
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:copy-of select="." />
</< FONT>xsl:template>
</< FONT>xsl:stylesheet>
A note on how to paste a multi-line xml document into a Integration Services String variable:
Integration Services String variables textboxes are not multi-line, in the Windows sense of a line (CR+LF),
So, in order to paste multi-line text (which xml docs almost always are), save a temporary copy with a unix line ending.
That is, create an xml file in visual studio, and paste your sample original xml in there. Go to File/Advanced Save options, and save the xml with the the settings of Encoding: Unicode (utf-8 without signature) - CodePage 65001, and most importantly, set the Line endings dropdown to "Unix (LF)".
After selecting "OK", copy and paste the text from Visual Studio's xml file editor into the IS variable, and you'll note all the xml data appears.
So can anyone walk me through a dummies version of what he is suggesting to do?
Thanks,
Keith
Problem With Extraction Of A Report In Xml Format Using XSLT
Nov 1, 2007Hi guys,
I have a simple report created with rs2005. I want to get the output exported (using the export options of rs2005) in a specific xml format, so I am using xslt transformation to get it proper.
My problem is that, when I'm doing this using my own machine (SQL Server 2005 32bit installed) everything works OK. BUT when I want to try it on a server that we are supposed to use it gives me a real bad error. On the server, if I have the report without using xslt, it gets exported OK but not in the correct format. I have tested the report on a 32 and on a 64 bit server, I have used either SP1 and SP2 but still can't get through. The error message appearring while I try to export in xml is "Server Error in '/Reports' Application. The XSLT path is invalid. It refers to an external resource, uses invalid syntax, or the XSLT was not found in the catalog."
Has anyone seen something like this before ?
Your help would be much appreciated.
Thanx in advance
Can The OLE DB Command Transformation Support Multiple Values Returned By A Stored Procedure Or Is The Limit One Value Only?
Apr 10, 2007I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
thanks
John
Data Transformation Services Question.
Oct 23, 2002I am creating a DTS package for an import of data from MSAccess97 to SQLServer2000. I am quite new to the DTS so bear with me please.
Everything was fairly simple until I got stumped by the following problem:
In the source database there was a table with multiple fields (let's say A, B, C) for each record containing their values (let's say 1,2,3).
Now, in the destination database the table is built differently. It is a table containing the field data and definition.
So basically I need to turn this
ID A B C
0 1 2 3
1 4 5 6
into this
ID FieldName FieldVal
0 A 1
0 B 2
0 C 3
1 A 4
1 B 5
1 C 6
I am not sure how to go about that... any thoughts? :confused:
Transformation Component Data Store
Jun 8, 2006i am developing one custom transfer component, where i am building one custom object and want the same to be transfered from ComponentUI to component.I explored in this issue and came to know that we can make use of SaveToXML and LoadXML methods of IDTSPersist90 interface. The problem is i could not able to make use of this interface.If any body faced same issue and got the solution, let me know the same.
Thanks in advance
Karun
Transformation Components And Moving Data
Apr 10, 2007Hi,
I am having a simple difficulty regarding my transformation component that I have created.
I followed all of the relevant documentation to create the component, UI and such, and when I run a program that transfers data through my component (without it doing anything to the data) to a flat file source, all I get in the flat file is ,,,,,,,,,,
Meaning that the data is not actually being passed through my component to the destination, but it does recongize the correct number of columns. I get this warning for each column: [DTS.Pipeline] Warning: The output column "PurchaseOrderDetailID" (20) on output "OLE DB Source Output" (11) and component "Source - PurchaseOrderDetail" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
To fix this issue, all I need to do is "check" the boxes in the advanced editor for my component. However, I want to be able have these boxes "checked" automatically.
My question is, when you "check" a box next to a column name in the advanced editor, what does that exactly do that allows you to transfer the data? What do I need to program in order for it to replicate that? I actually want it to happen automatically, so by default, all data in all columns are transfered through my component out the other side, so I just need to know how to do this by code, and not how to replicate the UI.
My component, I thought, did that automatically, as I specified everything that I thought was required based on all of the documentation I read. Obviously, I am missing something. Here are the methods that I believe would all be involved.
Please let me know what I am missing.
int[] inputColumnBufferIndexes; // ...
int[] outputColumnBufferIndexes; // ... used in PreExecute
PipelineBuffer outputBuffer; // used in ProcesInput
public override void OnInputPathAttached(int inputID)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vCol in vInput.VirtualInputColumnCollection)
{
IDTSOutputColumn90 outCol = output.OutputColumnCollection.New();
outCol.Name = vCol.Name;
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage);
}
}
public override void PreExecute()
{
//base.PreExecute();
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
inputColumnBufferIndexes = new int[input.InputColumnCollection.Count];
outputColumnBufferIndexes = new int[output.OutputColumnCollection.Count];
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
IDTSInputColumn90 column = input.InputColumnCollection[x];
inputColumnBufferIndexes[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
}
for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn90 column = output.OutputColumnCollection[x];
outputColumnBufferIndexes[x] = BufferManager.FindColumnByLineageID(output.Buffer, column.LineageID);
}
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
//base.PrimeOutput(outputs, outputIDs, buffers);
if (buffers.Length != 0)
{
outputBuffer = buffers[0];
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
//base.ProcessInput(inputID, buffer);
if (!buffer.EndOfRowset)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
while (buffer.NextRow())
{
// TODO: Examine the columns in the current row.
// Add a row to the output buffer.
outputBuffer.AddRow();
for (int x = 0; x < inputColumnBufferIndexes.Length; x++)
{
// Copy the data from the input buffer column to the output buffer column.
outputBuffer[outputColumnBufferIndexes[x]] = buffer[inputColumnBufferIndexes[x]];
}
}
}
else
{
// EndOfRowset on the input buffer is true.
// Set EndOfRowset on the output buffer.
outputBuffer.SetEndOfRowset();
}
}
CTE In OLE DB Command Data Flow Transformation
Dec 20, 2006I am trying to use a CTE in an OLE DB Command data flow transformation object. However, when I enter the cte and corresponding query in the SqlCommand field of the OLE DB command editor dialog, I get a syntax error. Can CTE's be used data flow objects? I have been able to use them in an Execute SQL Control Flow Item, but not in any data flow item.