SSIS XML Source in foreach loop container

2
votes

I've got an SSIS package with a foreach loop. The foreach loops and reads files. Filenames are put in a Variable User::FileName (index 0).

Forech container

My Data Flow Task is just an XML source and Ole DB Destination.

Data Flow task

The User::Filename is passed to the XML source via "XML file from variable"

enter image description here

I'm using the inline schema, which is valid (afaiks).

When running the package, I get this error:

[XML Source [50]] Error: System.Collections.Generic.KeyNotFoundException: De gegeven sleutel is niet aanwezig in het woordenboek. bij System.Collections.Generic.Dictionary`2.get_Item(TKey key) bij Microsoft.SqlServer.Dts.Pipeline.DtsDataTableCollection.AssociateTablesWithBuffers(IDTSOutputCollection100 outputs, IDTSBufferManager100 bufferManager, Int32 localeID, DataSet dataSet) bij Microsoft.SqlServer.Dts.Pipeline.DtsDataTableCollection..ctor(XmlSourceAdapter xmlSourceAdapter, IDTSOutputCollection100 outputs, Int32 localeID, DataSet dataSet, IDTSBufferManager100 bufferManager) bij Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter.PreExecute() bij Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

There is no TKey in my file.

When I run this package with an xsd, no error is generated, but no rows are being read.

When remove the foreach container and just point to a file with "inline schema" all rows are read and no error generated. However, I don't want to make a Data Flow for each file...

I am at a loss. Could anybody help me with a solution? A way to debug this error? A general direction to search?

Visual Studio 2010.

1
sql-serverxmlforeachssis
The reason for the error isn't clear. There is no TKey in the file. Is there supposed to be? Or are you asking how you can import a list of files that may all have different columns? – Tab Alleman
@TabAlleman In this example i'm not using multiple files, so they don't differ. The rows differ obviously, that's why we use XML I guess. The foreach is not working and the error is unclear. A solution would be nice, but any hint as how solve this would be great. – Fabio Bruna

1 Answers

0
votes

Your dataflow task is expecting the column TKey to exist in your XML Source file.

At least one of the files that you are looping over doesn't contain this column. That is why you are getting the error. Since you say the files are all the same, then presumably none of the files have this column.

In that case, one solution would be to take the TKey column out of the columns expected by the dataflow.