none
Xml Source Data Flow Component-Specify Xsd loaction from a varaiable in SSIS 2008

    Question

  • Hello

    Is there a way to configure the xsd Location from a variable for an Xml Source Data Flow Component in SSIS 2008.

    I have tried setting the [XmlSource].[XMLData] and [XmlSource].[XMLSchemaDefination] of the parent Data Flow Task to a user defined varaible that has the path.But this does not work  .

    Hope to get some help from the forum.

    Thanks

    Anand

    Tuesday, June 15, 2010 12:15 PM

Answers

  • you could employ the .net system.xml namespace within the script task to programmatically configure the xsd location of the xml file to be implemented in the xml source component via an ssis variable.

    hth


    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

     

    SSIS Business Intelligence

    Wednesday, June 16, 2010 6:53 AM
    Moderator
  • Another solution would be to use a series of File System tasks to copy and overwrite the one XSD location when you need to switch it.

    I really don't like working with XML in SSIS. To me, it's a four-letter word. ;-)


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, June 16, 2010 11:31 AM
  • Hi Douglas

    Sorry for the late reply

    Finally it worked for me in the way i was trying earlier i.e by setting the  [XmlSource].[XMLSchemaDefination] of the parent Data Flow Task to a user defined varaible .The only change i did was to change the DelayedValidation property to true for the parent Data Flow Task.

    In the child XmlSource Editor you still give a valid path of an existing xsd for the Xsd Location property.

    The varaible value which is assigned for the parent Data Flow Task ,when changed ,will override the child xmlsource editor'd Xsd Location property.

    Thanks

    Anand

     

    • Marked as answer by MDX Novice Thursday, June 24, 2010 4:14 AM
    Thursday, June 24, 2010 4:14 AM

All replies

  • No.

    EVERY other property of EVERY other object is configurable by an expression, except the XSD Location of the XML Data Source adapter. (Well, OK, there might be a few others, but I have not seent them yet.)

    You are going to find that working with XML in SSIS is a pain. For example, your XML can have multiple data stream outputs from a source depending on the schema. But if it does, EACH one of those streams MUST have at least ONE column checked off in the Source adapter, EVEN THOUGH YOU DON'T EVEN USE THAT PARTICULAR STREAM.

    Set yourself up for frustration right now, then when you finally get it to work, you can be happpy!


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Tuesday, June 15, 2010 12:39 PM
  • Thanks Todd

    But is there a work around to achieve it ?

    This seems to be a common issue which even others wud hv faced.

    So there must  be some other way to achieve this objective.

    Anyone on the forum?

    Cheers

    Anand

    Wednesday, June 16, 2010 4:10 AM
  • you could employ the .net system.xml namespace within the script task to programmatically configure the xsd location of the xml file to be implemented in the xml source component via an ssis variable.

    hth


    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

     

    SSIS Business Intelligence

    Wednesday, June 16, 2010 6:53 AM
    Moderator
  • Another solution would be to use a series of File System tasks to copy and overwrite the one XSD location when you need to switch it.

    I really don't like working with XML in SSIS. To me, it's a four-letter word. ;-)


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, June 16, 2010 11:31 AM
  • Thanks guys I'll try to figure out some work around or may do some out og box thinking :))

     

    Cheers

    Anand

    Thursday, June 17, 2010 5:34 AM
  • Thanks guys I'll try to figure out some work around or may do some out og box thinking :))

     

    Cheers

    Anand


    we already suggested two possible work-arounds.  are they inadequate for your business requirements?
    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Thursday, June 17, 2010 5:51 AM
    Moderator
  • Hi Douglas

    Sorry for the late reply

    Finally it worked for me in the way i was trying earlier i.e by setting the  [XmlSource].[XMLSchemaDefination] of the parent Data Flow Task to a user defined varaible .The only change i did was to change the DelayedValidation property to true for the parent Data Flow Task.

    In the child XmlSource Editor you still give a valid path of an existing xsd for the Xsd Location property.

    The varaible value which is assigned for the parent Data Flow Task ,when changed ,will override the child xmlsource editor'd Xsd Location property.

    Thanks

    Anand

     

    • Marked as answer by MDX Novice Thursday, June 24, 2010 4:14 AM
    Thursday, June 24, 2010 4:14 AM
  • Hi * ,

     

    Thanks for your solution.

     

     

    Thank you,

    -Vyas

    Thursday, February 10, 2011 10:48 AM