XML Source Not Reading Any Records
Sep 19, 2006
The problem started off as:
I have a webservice written in VB.NET that simply returns a System.Data.Dataset. I want to use the dataset in SSIS.
So i decided to do this:
Save the returned dataset returned by the WS to an XML file. so it is sitting there, and looks like a fully valid XML file
<?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="Name" type="xs:string" minOccurs="0" />
<xs:element name="SourceRecord" type="xs:string" minOccurs="0" />
<xs:element name="CombinedTitle" type="xs:string" 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">
<Name>Member</Name>
<SourceRecord>99998</SourceRecord>
<CombinedTitle>Mr</CombinedTitle>
<CombinedFirst>Sample</CombinedFirst>
........
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
I then use the XML source connection to connect to it. It sees all of the columns correctly, but when i run and put a watch on it, or try to output the results to a .csv file, no records come through.
Any ideas on why there aren't any rows comming through?
i'm using SQL2005 with no SP1
View 10 Replies
ADVERTISEMENT
Mar 22, 2006
I am trying read certain attribute from a XML Source based on a particular attribute.
My XML source has the following Structure:<Database ID = "1" Source = "ABC" ></Database><Database ID = "2" Source = "ABC" ></Database><Database ID = "3" Source = "DEF" ></Database>.
I would like to fetch the list of IDs filtering upon Source = "ABC". (which should idealy give me 1,2)
I tried using the For Each Nodelist Enumerator By specifying the Enumeration type as Node and IDrect Input for the OuterXpathString as DATABASE[@Source="ABC"].
The for each loop fails giving the following error. Data is invalid. line 1 position 1.
Can anyone help me out with the same. Is there any other way around to obtain the required result?
Pranathi
View 3 Replies
View Related
Apr 9, 2008
Hi,
I have a commercial data source 3 party one for me to extract data from , i have been provided the exe and the DLL how i can use this in SSIS as a datasource please let me know.
Thanks,
View 4 Replies
View Related
Oct 30, 2007
I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field.
The XSD defines the field as:-
<xs:element minOccurs="1" name="DateTime" type="xs: string" />
The XML looks like this:-
<DateTime>12/31/2007 10:12:14.123</DateTime>
And the database table column is defined:-
[DateTime] [datetime] NOT NULL,
So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP].
However, when I run the package I get:-
Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
So what how do I fix it?
I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.
I'd be happy to change the XSD to:
<xs:element minOccurs="1" name="DateTime" type="xs:dateTime" />
But the "XML Data Flow Source" doesn't seem to support the dateTime data type.
Any suggestions appreciated. Thanks.
View 1 Replies
View Related
Nov 27, 2007
Hi,
I have an Excel spreasheet - the first column is text, the second numbers, the third a mix of the two. If I point An Excel Source at this in my data flow, it will import the first two columns without problem, but not the third: all cells containing text are being imported as nulls, but those containing numbers are imported just fine.
Even if the numbers are stored as text, they are converted into numbers at import and genuine text is still discarded. It's treated as if the entire column is numeric if there's just one numeric value in it.
I can get around this by creating a .csv or .txt file from the excel file, but that will add an extra layer of admin to this process and I'm tryuing to make it as seamless as possible
View 3 Replies
View Related
Feb 9, 2012
I have a simple SSIS package -> It reads a local text file which has 10 rows of data ( id, name, telephone # ) and puts it into a table.
It uses the "SSIS Flat File source" to read and a "SQL Command" to insert into the table. I can see that it reads line by line and puts each line into one row in my table.
Now, my production data is over 5 GiG of mainframe data and it seems their data is arranged in some hierarchical form.. so the position or arrangement of data in that file is important.
I pulled the data using my package and as far as I can see , my SSIS package pulled one line at a time ( from the flat file) and pushed it into my table. For each row, I also created an identity column in my table to be able to identify the positional arrangement of the hierarchical data and then use relational mappings to suit our business needs.
In all of this, my assumption is -
"SSIS reads one line at a time, inserts to my table and goes down to the next line .
It does NOT read a snapshot of rows from the flat file so as to write them into the table using internal ordering methods based on that particular snapshot "
My question is .. is my assumption correct ?
View 1 Replies
View Related
Aug 28, 2006
Hi,
I'm using a script task as source and I need to read a global variable.
I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)
This gives an errero on 'Dts.' saying it can not resolve it.
View 2 Replies
View Related
May 2, 2007
I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.
Any idea why ?
View 9 Replies
View Related
Aug 3, 2005
I have a tab delimited flat file with say 60 columns. All columns can have null values. The file contains a blank tab for nulls.
View 25 Replies
View Related
Nov 1, 2007
Hello all,
I have few data in excel files .. I have to export the data to an sql server database ..
I tried using and import/export wizard from the sql server .. the problem is :
My excel has a field called PCode it contains alphanumeric data .. the rows which has alphanumeric data return "" where as if the Pcode is only numeric 4546 it takes it ..
Here is the structure of the Target Sql Server Table
and here is the Excel file http://www.zuhaib.in/test.xls
Can u figure out what am I missing?
imp0steur a.k.a { Zuhaiß }™
http://www.Zuhaib.in/
http://www.Igniteds.net/
View 3 Replies
View Related
Nov 1, 2006
Hi all,
Whilst reading in records from an excel source via the SQL command method I've stumbled across a problem.
my SQL query takes in all records where the date column is not NULL, this ensures that only populated rows are obtained. If the date is in a format that is incorrect i'd really like the whole data flow to fail. However... What seems to happen is that any rows with a fault in the date column are just missed out and not pulled through the pipeline. I have tried changing the error output from fail component to ignore error and redirect row but nothing seems to catch it.
Does anyone have any suggestions as to why this may be the case?
Many thanks,
Grant
View 5 Replies
View Related
Nov 29, 2006
Hello,
I am making my first attempt at creating a script for a Script Task. The script needs to do the following;
1. find the length of each record in a single fixed width flat file
-file location; C:LearningSettlementDataTestSC15_CopiesSingleFile
-file name; CDNSC.CDNSC.SC0015.111062006 (no file extension)
2. if a record is found that is longer than 384 characters;
a. copy the record out to a text file
-location;C:LearningSettlementDataTestSC15_CopiesErrantRecords
-file name; ErrantRecords.txt
b. delete record from the flat file where the record length is > 384.
If I can get this to work on a single file, I want to implement it with multiple files. I would imagine that using a ForEachLoop container with the script task 'inside' would be the way to go for multiple files. I have a connection manager set up for the single file and a MultiFlatFile connection manager set up for the whole collection of files. All of the files have the same schema. I don't know if the connection managers are going to be useful to me with what I'm trying to do, but I have them set up.
If you have some input on where I can find resources on how to do this, or have some code to pass along, please share.
Thank you for your help!
CSDunn
View 7 Replies
View Related
Apr 12, 2007
I have a simple data flow where I am trying to import data from an xml file into a SQL Server table. I have an xsd that seems to work because the XML Source can pick up all the elements in the xml file. The problem is that the process executes successfully without any rows being imported. The xml file has lots of data. But no messages or warnings are given to suggest why no rows are being written to the table.
View 21 Replies
View Related
Oct 18, 2007
All,
I am having a brain-fart or something. I need help!!!
I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data.
Any sample code or blog links would be greatly appreciated!
Thanks for the help!
-Scott
View 6 Replies
View Related
Sep 10, 2006
I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.
There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.
How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?
View 4 Replies
View Related
Dec 11, 2007
Hi All,
I have a XL source file which contains the 1st column value is some of Numeric and alpha numeric (ex. 1,1A,1B,2,2A,2B),
i have fired the select statement (select * from [sch 1 a$A9155] where f1 is not null ) in XL source editor, its not showing the numeric records means 1 and 2 were excluding from the above select statement i think it was due to combining numeric and alpha numeric.
I need both records should select , please anyone can help me to sort out the issue.
Thanks in advance
Thanks,
syed
View 6 Replies
View Related
Jan 29, 2008
Hi,
I have an SSIS package that runs each day from a live data source to create a data mart, which is then used for various things including SSAS and SSRS.
The problem is that certain records that will eventually go on to form fact tables are deleted from the live system (not a very robost database in the first place, hence the SSIS!) but these are not reflected in the SSIS transformation, creating plus figures when compared to the live system.
I currently use type 1 slowly changing dimension processes in each data flow (of which there are about 35) but I realise that this only updates records and does not delete.
The solution I have in place is to truncate the fact tables in the mart before the run starts using an Execute SQL task. This solves the problem though to me seems a little heavy-handed and renders the slowly changing dimension processes redundant (as it is currently only run once a day).
My question is, is there a better method of dealing with the above scenario? If there isn't, it would be a nice feature to add to future versions (*nudge nudge*).
Thanks in advance :-)
View 1 Replies
View Related
May 14, 2008
Hi
I am using a XML source and an oldedb destination to load an XML file into a database, the XML and corresponding XSD are provided by a different system and I dont have any control on them.
In the data flow I can see all the tables in the schema and I can map the columns, however when I run the package it validates fine, and then completes without going into the execution mode, its all green but doesnt load any data. Its weird and I cannot figure out if it is an XML or XSD problem, any idea?
Thanks
View 5 Replies
View Related
Aug 27, 2007
Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?
I tried the following, but it doesn't seem to work:
INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @number
View 2 Replies
View Related
Jul 20, 2005
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
View 3 Replies
View Related
Jun 27, 2007
Iam using Sql Server Integration Service to transfer the data. I have to methods to transfer the data.
Method -1
Daily cleaning Dataware house Data base and transfering the data from Source database.
Method -2
Only new rows transfering from the Source database to dataware house data base.
If i use first method, Any performence issues's will come in the future?. In future my source data will have upto 6 lacks records.
If i use second method, daily date based i can transfer the data. But if they delete any previous records from the source database how can i reflect the same in Data ware house Data base.
Can you please provide the solutions.
Regards
Hanu
View 3 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Jun 18, 2015
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.Â
View 4 Replies
View Related
Dec 20, 2005
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:
Operation type: xslt
SourceType: Variable
Source: user::RAS
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.
<?xml version="1.0" encoding="UTF-8"?>
<root> €¦
</root>
(When I store it in a file, I can see it in IE)
Have you got an idea ?
View 3 Replies
View Related
Dec 13, 2007
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?
View 13 Replies
View Related
Sep 14, 2007
Hi
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.
Hari
View 7 Replies
View Related
Mar 13, 2008
hi ,
i am trying for a drill through report (rdlc)
ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like
An error has occurred during report processing.
A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".
the code is
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;
using DAC;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ReportViewer1.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
DataSet ds = new DataSet();
ds = obj.get_order();
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";
ReportViewer1.LocalReport.Refresh();
ReportViewer1.Visible = true;
}
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
ReportParameterInfoCollection DrillThroughValues =
e.Report.GetParameters();
foreach (ReportParameterInfo d in DrillThroughValues)
{
Label1.Text = d.Values[0].ToString().Trim();
}
LocalReport localreport = (LocalReport)e.Report;
string order_id = Label1.Text;
DataSet ds = new DataSet();
ds = obj.get_orderdetail(order_id);
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");
ReportViewer1.LocalReport.Refresh();
}
}
the code in method get_orderdetail(order_id) is
public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.
View 1 Replies
View Related
Feb 13, 2007
Hi,
I am trying to create a program that transfers tables to flat files.
At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
View 5 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Aug 6, 2007
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
View 7 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Jul 16, 2007
hi guys, Can anyone advise me how how to read an excel doc i have stored locally? I need to be able to start the read from say row number 6 and finish the read once i get to a row that contains a pre-determined word signifying the end of processing. I intend to store the parsed data in an array that will be used as the data source for a gridview or repeater object on another asp page. I'm using ASP.net 2.0 and C# by the way. thanks in advance for any help!!!
View 12 Replies
View Related