Is it possible to have SSIS flatten XML data?

Hi,

I have to take a hierarchical XML file and store it into a table.

I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.

I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.

Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.

Thanks,

-dhideal

The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.

-Jamie

|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.

4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)

1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.

2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.

3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb

4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb

5. Load it into SQL server and use XQuery (not an SSIS solution)|||

jaegd,

Thanks for the pointers.

It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):

1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.

2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.

3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.

4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.

5) We're trying to avoid this particular solution for now if we can.

Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.

-dhideal

|||It seems you understand it quite well. To flatten, though must join and/or aggregate.

Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.

Now, I have tested all five of these approaches, and used two of them

(XPath,XmlSerializer, leaving out XQuery for the moment). They all

require you to essentially do XML "joins" via custom code, whether that

code is a set of XPath expressions in a custom adapter, a set of

property "gets" in in a custom adapter.

Had Microsoft shipped a client side XQuery implementation, I would have

recommended that, since XQuery allows you to do XML joins, which is

basically what you're looking for.sql

Is it possible to have SSIS flatten XML data?

Hi,

I have to take a hierarchical XML file and store it into a table.

I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.

I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.

Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.

Thanks,

-dhideal

The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.

-Jamie

|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.

4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)

1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.

2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.

3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb

4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb

5. Load it into SQL server and use XQuery (not an SSIS solution)|||

jaegd,

Thanks for the pointers.

It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):

1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.

2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.

3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.

4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.

5) We're trying to avoid this particular solution for now if we can.

Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.

-dhideal

|||It seems you understand it quite well. To flatten, though must join and/or aggregate.

Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.

Now, I have tested all five of these approaches, and used two of them

(XPath,XmlSerializer, leaving out XQuery for the moment). They all

require you to essentially do XML "joins" via custom code, whether that

code is a set of XPath expressions in a custom adapter, a set of

property "gets" in in a custom adapter.

Had Microsoft shipped a client side XQuery implementation, I would have

recommended that, since XQuery allows you to do XML joins, which is

basically what you're looking for.

Is it possiable to store password encrypted to an external config file?

I got a problem when developing SSIS packages.

For security reason, the sensitive information must be encrypted, and they should be configurable dynamically (by an ASP.NET application).

so I tried several ways to achieve this,

1. use SSIS Package Configurations to generate an XML config file
It's a convenient way to generate config file. but the passwords are not encrypted (or I don't know how).

2. use Variables and Property Expressions
I can set variables by reading from an external custom xml config file which was content encrypted (read and decrypt the custom config file in a script task). but in a FTP Connection Manager entity, the Password property can not be set via Property Expressions.

Is any way to store password encrypted to an external file?
OK, It was resolved, I try to set Variables from a custom XML config file. Most of Properties of Connection Managers can be set via Property Expressions but password. So I set the password in a Script Task programatically. It will like this:

Public Sub Main()
'a custom class to read my custom config file
Dim c As Config = Config.GetConfig()

Dts.Variables("FtpServerIP").Value = c.Settings("FtpServerIP")
Dts.Variables("FtpServerPort").Value = Convert.ToInt16(c.Settings("FtpServerPort"))
Dts.Variables("FtpServerLogin").Value = c.Settings("FtpServerLogin")
Dts.Variables("FtpServerPassword").Value = MyDecryptMethod( c.Settings("FtpServerPassword") )

Dts.Connections("FTP Server").Properties("ServerPassword").SetValue(Dts.Connections("FTP Server"), Dts.Variables("FtpServerPassword").Value)

Dts.TaskResult = Dts.Results.Success
End Sub

Now I can store and encrypt my password or other sensitive information in a custom XML config file, and change it dynamically. It looks quiet complex, but it's the only way I know.

Any good suggestion?

Is it easy to store phots and PDFs in SQL Server?

Is it easy to store and retrive photos and PDFs in SQL Server? Is any
programming involved or is it transparent to the user?
Thank You,
Randy K
wawork@.hotmail.com
There are SQL data types that allow storing such data - binary, varbinary
and image. But it's not very efficient - much more efficient to store the
paths to the actual files and retrieve them via code. Not sure what you mean
by "Is any
programming involved or is it transparent to the user?". There is always
*some* programming involved...
"Randy K" <wawork@.hotmail.com> wrote in message
news:40aa8ab3.1102812@.msnews.microsoft.com...
> Is it easy to store and retrive photos and PDFs in SQL Server? Is any
> programming involved or is it transparent to the user?
> Thank You,
> Randy K
> wawork@.hotmail.com

Is it easy to store phots and PDFs in SQL Server?

Is it easy to store and retrive photos and PDFs in SQL Server? Is any
programming involved or is it transparent to the user?
Thank You,
Randy K
wawork@.hotmail.comThere are SQL data types that allow storing such data - binary, varbinary
and image. But it's not very efficient - much more efficient to store the
paths to the actual files and retrieve them via code. Not sure what you mean
by "Is any
programming involved or is it transparent to the user?". There is always
*some* programming involved...
"Randy K" <wawork@.hotmail.com> wrote in message
news:40aa8ab3.1102812@.msnews.microsoft.com...
> Is it easy to store and retrive photos and PDFs in SQL Server? Is any
> programming involved or is it transparent to the user?
> Thank You,
> Randy K
> wawork@.hotmail.com

is folder App_data uses to store database by .net 2.0 hosting companies?

I am workin on a website for me and my friend. I want to host it at a hosting company. In asp.net 2.0 projects are build with the App_data folder, which can stores data source such as a SQL Server 2005 database. But when i want to host the site at a hosting company, will the database goed into this folder?

I also have a hosting company and dealing with the same problem.

The company has a SQL server that is different than the web server so; you have to create the database in the SQL server and then have your asp codes connect to that server by a connection string. You have to have the IP address of the server, your username and password to make that work.

Hope this helps.