Foreach loop with XML Source failure

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

That error says INPUT column, so I doubt its coming from the XML source adapter. An XML source adapter has output columns (and external metadata columns). Are you certain the error is with the source adapter and not some other pipeline component?

As an aside, that XSD and xml will work just fine in without regard the surrounding container. The XSD is not broken so far as use in the SSIS source adapter is concerned, although it does not contain the <computerCount> element.|||

Thank you for the feedback, but I think that I failed to mention that yes, the next thing that I send the XML Source to, whether it be a sort, derived column, an ole db destination, etc... is where the failure shows up.

Take for instance the case where I put the XML Source to an OLE DB Destination. I use a file and set the columns via regular mapping. Then I go back and set the XML Source to another file to be sure it continues to work and I get the error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 4776 that was not previously used in the Data Flow task. Package.dtsx 0 0

Then I go back into the Ole DB Destination and have it map using Column Names. And everything is okay again. Then go back and switch to the next file and get this error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 5265 that was not previously used in the Data Flow task. Package.dtsx 0 0

It's a vicious cycle.

An aside, to your aside, I was messing with the XSD and took out the ComputerCount during debug.

Thank you for your help.

sql

ForEach Loop or For Loop?

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

Is the metadata the same for each data transfer? If not then you're going to have to build as many data-flows as there are rows in your lookup table and if that is teh case - what's the point in looping over them?

-Jamie

Foreach Loop and Package Configuration

I am trying to build a package that moves data from one server to another. My plan is to make the package dynamic in that the source and destination connection and sql statements are strored in the package configuration.

Is it possible to have a foreach container loop through each configuration?

Thanks,
Russ Jester

And do what? Why do you want to loop through a configuration (I presume you mean a configuration file)?

-Jamie|||I wanted to set configuration items and store them in a configuration database. Idaally I would like to loop through each configuration item to load data from source tables to my target.

I saw this as a way to build a package that would load data based on the source table, source sql, and target table pulled from the configuration. Instead of having to create a package for each table.

Am I not understanding the purpose of the package configuration?

Foreach ADO Enumerator?

my setting in foreach component like below shown

Enumerator: Foreach ADO Enumerator

ADO Object source variable: user::strsql

strsql like "select distinct name from table"

Enumeration mode: Rows in the first table

my goal: transform data according to the distinct every name

however execute error

prompt: Variable "User::strsql" does not contain a valid data object

pls help,thanks in advance

The foreach ADO Enum, needs and OBJECT type.

You need to create a Execute SQL Task with the SQL you want, then asign the return to the object variable. Then use the object as the ADO Object.

See this example: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

Force invalidate snapshot - how?

Hi,

In SQL 2005 SP1 - In my merge replication RMO C# script, I am getting the error that the merge snapshot is obsolete:

Source: Merge Replication Provider
Number: -2147201021
Message: You must rerun snapshot because current snapshot files are obsolete.

I am new to SMO/RMO and C#. I presume that I need to set the force-invalidate flag somewhere before generating another snapshot. I'm guessing that I can "try/catch" the error and handle it then (am I correct?) My script is running each step synchronously including synchronizing the subscription synchronously - meaning: subscription.SynchronizationAgent.Synchronize();

I presume the error occurs when I synch the subscription and there is an existing, but obsolete, snapshot file.

I would really appreciate it if someone could show me how to set the snapshot force_invalidate flag in RMO (preferably in C#). A fallback might be to delete any existing merge snapshot files, but I presume that would take much more time than setting the switch and calling for another snapshot to be generated. I wish I had some code to look at to show me an example of handling this error. If you can point me to such an example or can help me get around this error, I thank you.

I do have the O'Reilly book - Programming Sql Server 2005, but am not aware of any code examples that pertain to this problem. Let me know if you know of one.

Thanks for any assistance you might be able to provide :-)

Paul

More -

I had assumed a snapshot had been generated and it hasn't. The question of a code example of how to force invalidate a snapshot is still of interest to me though.

|||

I found it:

// If the pull subscription and the job exists, mark the subscription
// for reinitialization and start the agent job.
if (subscription.LoadProperties() && subscription.AgentJobId != null)
{
subscription.Reinitialize();
subscription.SynchronizeWithJob();
}

Force invalidate snapshot - how?

Hi,

In SQL 2005 SP1 - In my merge replication RMO C# script, I am getting the error that the merge snapshot is obsolete:

Source: Merge Replication Provider
Number: -2147201021
Message: You must rerun snapshot because current snapshot files are obsolete.

I am new to SMO/RMO and C#. I presume that I need to set the force-invalidate flag somewhere before generating another snapshot. I'm guessing that I can "try/catch" the error and handle it then (am I correct?) My script is running each step synchronously including synchronizing the subscription synchronously - meaning: subscription.SynchronizationAgent.Synchronize();

I presume the error occurs when I synch the subscription and there is an existing, but obsolete, snapshot file.

I would really appreciate it if someone could show me how to set the snapshot force_invalidate flag in RMO (preferably in C#). A fallback might be to delete any existing merge snapshot files, but I presume that would take much more time than setting the switch and calling for another snapshot to be generated. I wish I had some code to look at to show me an example of handling this error. If you can point me to such an example or can help me get around this error, I thank you.

I do have the O'Reilly book - Programming Sql Server 2005, but am not aware of any code examples that pertain to this problem. Let me know if you know of one.

Thanks for any assistance you might be able to provide :-)

Paul

More -

I had assumed a snapshot had been generated and it hasn't. The question of a code example of how to force invalidate a snapshot is still of interest to me though.

|||

I found it:

// If the pull subscription and the job exists, mark the subscription
// for reinitialization and start the agent job.
if (subscription.LoadProperties() && subscription.AgentJobId != null)
{
subscription.Reinitialize();
subscription.SynchronizeWithJob();
}

For Each Container for XML Source Adapter

I know how to For Each Loop a Flat File... But there is not File connection to reference for the XML Source adapter.

How do I run XML files through my DataFlow from a directory with the For Each Loop?I am going to assume two things:
1. the schema is the same for all of the files, and you have already set up the XmlSrc using valid data and schema to get the metadata initialized
2. you have, in your for loop, a variable with the name of the current file

In your XmlSrc, in the advanced editor, Component Properties, under Custom Properties, set the AccessMode to 1 (this means the file name is in a variable), and set the XMLDataVariable property to the name of the variable with the file name.

Let me know if that works for you.
Thanks,
Mark|||Marc,

Thank you for the Late Night answer. It worked perfectly, and I would have not figured that out without you.

Your Money!

for dgk and anyone else who knows

Thanks a lot dgk. You covered both cases and the second was the case
needed. Although an other problem arised. I got this error message :
"The source of the report definition has not been specified"
I thought that when you set the report in the report viewer control
the work is done but unfortunately this is not the case.
Even more, I don't have a clue of what I am supposed to do.
(I know I have to set the reportviewer to load the report but
I do not know how to do that)
Rookie in RS I suppose...
>
> If you're using a reportviewer control, parameters are done like this:
>
> Dim RptParm As New
> Microsoft.Reporting.WebForms.ReportParameter("FileNumParam",
> lblFileNum.Text) ' name/value pair for a parameter
>
> Dim RptParms As New Generic.List(Of
> Microsoft.Reporting.WebForms.ReportParameter)
>
> RptParms.Add(RptParm)
> ' add more parameters as necessary to RptParams
>
> Reportviewer1.ServerReport.SetParameters(RptParms)On Thu, 12 Oct 2006 11:18:18 +0300, "Nick Hatzis"
<boghatzisn@.panafonet.gr> wrote:
>Thanks a lot dgk. You covered both cases and the second was the case
>needed. Although an other problem arised. I got this error message :
>"The source of the report definition has not been specified"
>I thought that when you set the report in the report viewer control
>the work is done but unfortunately this is not the case.
>Even more, I don't have a clue of what I am supposed to do.
>(I know I have to set the reportviewer to load the report but
>I do not know how to do that)
>Rookie in RS I suppose...
A really good source of info for ReportViewer is
www.gotreportviewer.com. Documentation for RV is not very good and the
tutorials on that site help a lot.
I'm not sure what the error message is about though.