SSIS "XML Source Adapter" can't make XSD location an expression
I am using the "XML Source Adapter" in an SSIS package. I notice that although you can specify the XML filename as an expression, the XSD appears to have to be a fixed file path. This is a problem for me since the path for the XSD is different in my development than it will be in production (in production it's on drive E:, which I don't have). I'd like to have the file location specified in the config file, but since I can't make it an expression how can I do that? Also, since they don't have Connection Managers I can't switch DelayValidation on. Thanks.
November 21st, 2007 9:51am

You can set a property expression on the XMLSchemaDefinition property, which is the XSD location. This also means you can set it via a configuration. Properties that support expressions do so by appearing at the (Data Flow) task level, as configuration and expression support only works on container (task) properties. You could set Delay validation on the Data Flow task itself.
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2007 12:10pm

Hello Darren,tried your solution, however I receive following exception:[XML Source [14169]] Error: There was an error setting up the mapping. Illegal characters in path. That's what I've configured in the XML Source adapter:AccessMode = 1 (File path in variable)XMLDataVariable = User::ConvertedXMLFilePathXMLSchemaDefinition = @[user::WorkingDirectory] + "\\SCHEMA\\SSIS.xsd"I've set the DelayValidation of the DataFlowtask to trueAnyone can help me out of this??Thanks in advance
June 17th, 2008 1:03pm

What's the value stored in your variable? And when you populated the expression, did you hit the validate expression button? What did the final value look like?
Free Windows Admin Tool Kit Click here and download it now
June 18th, 2008 12:46am

Hello ChrisThose are the values stored into WorkingDirectory[user::WorkingDirectory] = 'C:\input\xml'Unfortunately I don't have an evaluate button: I have to enter the expression directly into the text box: no button to call the expression editor for this property...
June 18th, 2008 12:21pm

On the Control Flow, when you have the Data Flow Task selected, you can bring up the Expression Builder from the property's window. I selected the [XML Source].[XML Schema Definition] as the property, and then I clicked on the ellipses on the far right to bring up the Expression Builder. My test variable and XSD file parsed correctly. I didn't place any escape characters in the variable. C:\users\me\Downloads\Testing, so my expression looked like @[User::WorkingDirectory] + "\\Schema\Events.xsd" and it parsed normally, and I was able to see the correct path. Not sure, if I can contribute any more.
Free Windows Admin Tool Kit Click here and download it now
June 18th, 2008 8:42pm

Hello Chris, I'm Sorry but i can't find any ellipses for the property [XMLSource].[XML Schema Definition] I'm working with Visual Studio 2005 SP1 And SQL Server 2005...
June 19th, 2008 10:28am

Hi there, If you're still having a problem then follow these steps. In your CONTROL FLOW tab, select the data flow step that contains the XML Source step. Do not go into the dataflow, it must be done in control flow. Bring up its properties (right-click > Properties) Click the elipsis button next to EXPRESSIONS Select the [XML Source].[XMLSchemaDefinition] property then hit the elipsis button to the right of the Expression textbox. Drag and Drop your variable that holds your XSD path into the expression box, you should be able to evaluate expression here. Click OK Click OK again DoneAndy
Free Windows Admin Tool Kit Click here and download it now
June 24th, 2008 5:30pm

Hello Andy Thanks for your reply. That's what i was missing... Alain
June 24th, 2008 6:06pm

hi andythanks this thread is very useful...
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2008 9:03am

Very helpful info! Thanks all for taking the time to post!
April 16th, 2009 11:46pm

Does this work with SSIS 2208? We tried and it does'nt pickup the settings.
Free Windows Admin Tool Kit Click here and download it now
July 8th, 2010 10:38pm

Perhaps the above post by AndyCutler could be marked as the answer on this thread? Thanks...
July 13th, 2010 4:43pm

Thank you so much! You really make my day! :)
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2010 11:45pm

My Name is Srinivas Daram. I was tyring to use dynamic file names for XML and XSD on Data flow task. Saw your posting. Is this working for you. I could not make it work yet, can you tell me please what are all the properties you have set for this to work. I am devloping this SQL 2008. Thanks
August 9th, 2010 8:57pm

Hi Andy Cutler, Apart from setting the file properties, do we need anymore properties need to change. I want to use data flow task dynamically change the XML and XSD files. Base on my FOR each loop values for the DATA FLOW Task u all talking about on this forum.
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2010 9:00pm

Srinivas, what you are trying to do will only work if all your XML files and XSDs have exactly the same format. If they have different formats, the data flow will error at runtime. The metadata used by the data flow is not dynamically, and can't automatically update itself at runtime.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
August 9th, 2010 11:10pm

Hi John Welch, Thanks for the reply, appreciate your inputs. You mean to see, I can not use this for my situation. Once again I would like to explain my issue here. I have about 400 xml files with me and all this data has to be loaded in about 30 tables, Basically have 30 different varities of xml files and matching 30 xsd files. We were trying to load all this using "Data Flow Task" and "XML Source". Using FOR Each Loop passing appropriate XML File and XSD to Data Flow task and load it in one shot. Rather than having 30 different Data Flow tasks. Is there any other way, we can accomplish this task using a loop and dynamically passing the different files. Thanks Srinivas
Free Windows Admin Tool Kit Click here and download it now
August 10th, 2010 5:56pm

Data flows can't change metadata at runtime - it's locked at design time. So if you have 30 files with different formats, you need 30 data flows to move them (or 30 different source -> destination combinations in a data flow, though I don't advise that). You can programmatically create packages to move the data, which is what I would look at in this scenario. There's some example code here for package generation here: http://sqlsrvintegrationsrv.codeplex.com/releases/view/17647 If you want to discuss this further, I'd suggest starting a new thread. John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
August 10th, 2010 9:10pm

Hi John, Here is my pevious post I have about 400 xml files with me and all this data has to be loaded in about 30 tables, Basically have 30 different varities of xml files and matching 30 xsd files. We were trying to load all this using "Data Flow Task" and "XML Source". Using FOR Each Loop passing appropriate XML File and XSD to Data Flow task and load it in one shot. Rather than having 30 different Data Flow tasks. Is there any other way, we can accomplish this task using a loop and dynamically passing the different files. You have suggested to follow the URL There's some example code here for package generation here: http://sqlsrvintegrationsrv.codeplex.com/releases/view/17647 Everywhere I see, they are suggesting to use Flat File or Excel data examples. Do you have any posting that can help me to use XML file as a dynamic input file for XML Source. If anyone has any code samples how we can use XML files as dynamic source for XML Source task to import data into SQL Sever, that would be really appreciated. Thanks
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2010 9:31pm

Hi there, If you're still having a problem then follow these steps. In your CONTROL FLOW tab, select the data flow step that contains the XML Source step. Do not go into the dataflow, it must be done in control flow. Bring up its properties (right-click > Properties) Click the elipsis button next to EXPRESSIONS Select the [XML Source].[XMLSchemaDefinition] property then hit the elipsis button to the right of the Expression textbox. Drag and Drop your variable that holds your XSD path into the expression box, you should be able to evaluate expression here. Click OK Click OK again Done Andy This approach works great except that... you need to specify some default file path under XSD Location: of XML Source Editor. Otherwise, you will get an error message that you need to select an existing XSD or click Generate to generate a XSD file, none of which options is possible since we are dynamically receiving XML file and XSD file paths from those variables. How do you deal with this situation?
February 24th, 2011 10:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics