SSIS XML Source in foreach loop container

Question

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).

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

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

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.

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
– Tab Alleman, Commented Feb 2, 2018 at 19:04
@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
– Fabio Bruna, Commented Feb 3, 2018 at 7:54

Tab Alleman · Accepted Answer · 2018-02-05 14:45:36Z

0

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.

answered Feb 5, 2018 at 14:45

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fabio Bruna Over a year ago

This sounds quit logical. However,my files don't contain a TKey, The file schema definition in the header doen not contain a TKey. Nor does the xsd. In the source of the SSIS there is no TKey. I really am at a los here :)

Tab Alleman Over a year ago

Right-click on your XML Source (in the dataflow where the error occurs) and click "Show Advanced Properties" > Input and Output Properties > Output columns. If you don't see "TKey" in the columns there, then it's possible your package is corrupted and you may need to drop and recreate the dataflow, if not the entire package. Next step would be to open the script view of the package and do a text search for "Tkey".

Fabio Bruna Over a year ago

Rebuild the package, same error. There is no TKey in source view. I've given up for now, will try loading outside SSIS.

Collectives™ on Stack Overflow

SSIS XML Source in foreach loop container

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related