XML Source - No External Columns Avail-- Syntax Error In XML/XSD?
Jan 28, 2008
Hi All,
Hopefully someone can help me with what I'm sure is a very simple question (new to the XML thing). I receive an XML file from "someplace" that I need to parse out using the XML Source in SSIS. I have SSIS generate me an XSD document, as one isn't provided for me. However, after I do this, SSIS does not show any available external columns to pull data from- the "Columns" section of the source is just blank. I'm pretty sure this has to do with a syntax error in either the XML file that is being provided to me, or the XSD doc that SSIS is generating. Below are both (obviously with data dummied up). Can someone take a look and let me what needs to be changed in either file to get this up and running? I'm looking to grab the AccountNumber, RecordNumber, ProcessedDate, Status, and StatusMessage elements.
I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.
I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.
On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:
Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....
What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.
Is possibile configure as expression the properties XMLData and XMLdataDefinition of a "XML Source"? looking the editors it seems it is not possibile... I think it is a great limit of the component because I must hardcode the path!
Regards,
M.rkino
-- Marco Barzaghi - [MVP - MCP] http://mvp.support.microsoft.com - http://italy.mvps.org UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org Read my WebLog: http://blogs.ugidotnet.org/markino
Hi all,I am having real problems allowing a client to connect to sql 2000through enterprise manager.. I have tried all sorts but to no avail:From our local network all works fine.. Using the adsl line.. Even ifi try and connect to the external IP and use login details..(Asopposed to connecting local and using windows accounts) I can ping andtelnet the server and port.The server is behind a firewall so i opened up port 1433 and port1434(? is it needed).. To replicate the client i took the same laptophome that i use to connect in the office.. However using the exactsame config on my dial up at home i can ping the server but cannottelnet onto port 1433.. I think that this is the vital missing stepbut do not know hpw to resolve it..Any ideas anyoneCheersAnt
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
How can I move data from an external source to SQL-server 6.5.
If you for example has an oracle database on a UNIX or VMS-system and want to move table data from a table in the oracle database to the SQL-server database.
There must be plenty of tools to do that via ODBC.
We run a weekly update on one of our databases from a download (FTP) from our mainframe. What I want to do is start the update job automatically after all of the files are downloaded.
I have a similar process in another database where the users set a flag in an application after they've made certain changes. The job fires daily at 18:00 and checks the value of the flag in the table. It then processes or goes back to sleep. This works great but there is no flag that the users can update in this application.
I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute. The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this.. thanks
I have a knowledge of SISS and CRM 3.0 but don't get any proper steps to create such Integration Service in SQL Server 2005 using CRM 3.0 as external data source.
I don't get any option of external data source for CRM.
So if anybody know about this then let me know steps to do that.
I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors).
I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message:
Property Value is not valid.
The details are:
Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed. Error at DataReader Source: System.Runtime.InteropServices.COMException (0xC020837D) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
I have an XML file that my XML Source component is accessing. I have noticed that is possible to set a column in the external metadata collection to a certain datatype and the matching output column to a different datatype and this doesn't not generate a warning like it does with other source components (e.g. Flat File Source Adapter).
Try it. Set a column in your external metadata to have a datatype of DT_WSTR. Set the matching output column to DT_UI8. You will NOT get a validation error. I think you should.
This behaviour was noticed on RTM (i.e. no service pack installed) by the way.
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution: - I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem: This configuration gives me only the first erroneous column in the row being processed.
Question: Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
I have a OLE DB Data Source Component that uses an SQL command as the data access mode. On the Connection Manager "tab" of the OLE DB Source Editor I can sucessfully parse my query and produce a preview of the data.
However, when I go to the Columns tab I see no available external columns? Apart from the fact I'm using a union I can't think of any reason why I can't see any columns, it doesn't make sense.
I have a SSRS 2012 report which references a custom c# assembly.  This report exists in multiple environments (alpha, beta and  production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.
1) DelayedValidation. I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).
2) Using a variable that changes during runtime. I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.
3) Manually updated the external/output columns list. Was very painful and gave me the same errors.
It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.
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.
Does anybody know a reliable source for common Expressions and Syntax rules used in RS2000 or RS2005? This is my third day chipping away at RS and it's been challenging so far. thx, John
I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!
I am using SQL2014 with HA/AG groups and was adding an extra listener when I used the incorrect IP address. i had thought the 'remove' button would work, but it does not. Also I tried deleting it manually:
delete from sys.availability_group_listener_ip_addresses where listener_id = '26d37cdb-5a6f-43a0-ab0c-f4343eeac0f2'
delete from sys.availability_group_listeners where group_id = '6B060505-CDDC-44B6-A9AD-38FC6AD4A7AA'
Ad hoc updates to system catalogs are not allowed.How can I remove the invalid IP?
Dallas: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster Wash: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
and I'm mirroring (synchronous, no witness) a database from Dallas to Wash.Crappy set up. I know.Now customer wants to have the database mirror to another server in Dallas.What are the high level steps to transform my two clusters to use Availability Groups and Always On?Do I need to basically start over and build a new environment?or can I transform my two disconnected cluster?
Im trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
Comment ReadThe Job 157 Comment ReadThird 159 Comment ReadMy Job 164 On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID @UserID INT AS
BEGIN IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact, Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND BackUp_Read = 'UnRead')
I keep getting the following error in SSIS. Also, I don't get the error on every server the package is run on, but less than 5 (the package is run on over 100).
"The external metadata column collection is out of synchronization with the data source columns. The column "Timestamp" needs to be added to the external metadata column collection"
Please tell me where I need to remove Timestamp from. Thanks
I keep getting the following error in SSIS. Also, I don't get the error on every server the package is run on, but less than 5 (the package is run on over 100).
"The external metadata column collection is out of synchronization with the data source columns. The column "Timestamp" needs to be added to the external metadata column collection"
Please tell me where I need to remove Timestamp from. Thanks -Kyle
I am using SQL Server 2000. I have some files with SQL-Statements.The SQL-Serveragent runs jobs which execute the SQL-Files:(e.g. osql /E /n /i \serverd$lager_pool.sql)How can I implement an error handling.If an error occurs, the script stops, and I can't read the variable@errorMy script - table xy doesn´t existSELECT * FROM XYSELECT @@errorSELECT 33The execution stops with an error after the first lineThanks for your help.aaapaul
Dear GroupThe scaenario is as follows:FirstName and LastName are separate columns in the contact table and Iwant to be able to search e.g. for the FirstName and part of theLastName at the same time e.g. 'John A' should return 'John Adams'.Would be grateful if you can give me some hint as I don't seem to getit work.SELECT FirstName, Lastname FROM i2b_contact WHERE (SELECT Firstname +Lastname AS CName) LIKE 'John A%'Thanks very much for your help and efforts!Martin
Im connecting to an AS400 to retrive data with SQL Server 2005. Until now i have not pulled large amounts of data from there. My queries run fine on my local machine in SQL Server 2005 Management Studio. I have run queries for up to an hour or more. The minute i put them in a SP and execute them they time out at 90 sec.
I have a linked server set up to the 400 with the connetion timeout set at 600 and the Query timeout at 600 through port 8471. Is there a port timeout on the server I need to change?
What is the syntax when creating a SQL statement and using a column that has a multiple word name?
I know that in query analyzer it should be delimited? by double quotation marks like such: SELECT Item, "Item Description" FROM ComponentList WHERE Item = 'CPU' The above works great in Query Analyzer on the device and in the device emulator.
but I'm attempting to build a SQL string in code...VB.Net using CF and PocketPC SDK, SQLCE on the PocketPC device....
Here's the snippet that does not work: sql = "SELECT Item, 'Item Description' FROM ComponentList WHERE Item = '" & selectedItem & "' "
While drItems.Read() .....More code here not copied
I'm trying to fill a listview control and when I use the above syntax one of the columns is filled with the text Item Description instead of pulling the actual Item Description from the table...So what is the correct way to construct the SQL string?
