Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
..
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ruYou cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.com...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.com...
>|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
>|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>sql

Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
...
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
You cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.c om...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.c om...
>
|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>
|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...[vbcol=seagreen]
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>

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 possible to ftp files using code in a SSIS Script Task?

Is it possible to ftp files using code in a Script Task? I need to read the contents of an xml file and if it has a a specific file name in there then I ftp the corresponding pdf file which is at the same location as the xml file. However I cannot do this using the provided FTP Task in SSIS, I would need to use code to do this as there are close to 50 xml files which I need to read and upload the corresponding pdf file file it meets a certain criteria.

I do not see a way of looping thru all the files in a folder unless I do this in a Script task. Any inputs or alternative comments on doing this will be appreciated.

Thanks,

MShah

In the Control Flow you should be able to use a ForEach loop to loop over all the files. Then you can use a script task (XML task might also work but not sure) to extract the information needed to create the path & filename to FTP.

You would store the create filename in a Variable and then do the FTP task in the loop to upload the files. Anyone know if the FTP task will connect to FTP once in this type of loop?

Fred

sql

Is it a good to replace SQL script files with XML files?

I am thinking about replacing the INSERT data script
files that I have with XML files. This way I can open the XML
file using an XML Editor and see the values in a GRID and
make changes easier.

Do you see any problem with this approach?

I managed to put together some code that is exporting
a SQL table with its data to an XML file and also a code
that reads the XML file's data and inserts it into a table.

Now I am researching on XSD, td:datatype, DTD...
(I am new to XML) in order to figure out how I can
use a single xml file that will hold both the sql server
fields, the datatypes and their values.

If you have links to some sample code that has anything
to do with the datatype export and import I am working
on, can you please share them with me?

Most importantly what do you think about the idea of using
XML files vs sql scripts?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> I am thinking about replacing the INSERT data script
> files that I have with XML files. This way I can open the XML
> file using an XML Editor and see the values in a GRID and
> make changes easier.
> Do you see any problem with this approach?

I know too little XML to say that whether this is good or bad. I didn't
know that there were XML Editors where you could edit grid cells.

I recognize the problem, though, because we have plenty of such files in
our shop. Our solution to the problem is Excel. (Which can be saved as
XML, but we don't do that currently.) Then we have a tool that reads the
Excel book and generates an INSERT-file from it. That file, by the way, does
not include any INSERT statements, but calls to a stored procedure that
will insert or update (or delete), so that the files easily can be rerun.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have only started learning XML 3-4 days ago. I ran into a
newsgroup post by chance where someone was using XML
to transfer data to SQL Server.

http://visualbasic.ittoolbox.com/gr...rver-l&i=780204

So that made me wonder why I wouldn't do that?
I've been working on this since then and slowly learning more
about SELECT * FROM TABLE FOR XML AUTO, XML,
DTD, XSD files, now I need to learn XDR, I think XDR is
similar to XSD but seems to be aimed for SQL Server.
I'll post some questions on microsoft.public.xml and hopefully
I'll get some answers from people who have already done what
I am trying to do.

But one question I have is if you are using Excel, are you using
it only for the INSERT data part? What about using the same
or another Excel file to hold the table's column names and data
types?

At this point in time (with my very little knowledge of XML) I
believe it wouldn't be a good idea to replace the sql files holding
the table structures with XML files holding the equivalent in terms
of the columns and its data types. I think that is more difficult
for someone to make table changes.

Here are three links for free XML Editors.
http://www.xmlcooktop.com/

I like these two as they will show you the data in grids:

http://symbolclick.com/index.htm
http://www.xmlfox.com/download.htm

Thanks

> I know too little XML to say that whether this is good or bad. I didn't
> know that there were XML Editors where you could edit grid cells.
> I recognize the problem, though, because we have plenty of such files in
> our shop. Our solution to the problem is Excel. (Which can be saved as
> XML, but we don't do that currently.) Then we have a tool that reads the
> Excel book and generates an INSERT-file from it. That file, by the way,
> does
> not include any INSERT statements, but calls to a stored procedure that
> will insert or update (or delete), so that the files easily can be rerun.|||serge (sergea@.nospam.ehmail.com) writes:
> But one question I have is if you are using Excel, are you using
> it only for the INSERT data part? What about using the same
> or another Excel file to hold the table's column names and data
> types?

I might be misunderstanding your questions, but for that purpose a
data-modelling tool is much better in my opinion. In our shop we
use PowerDesigner from Sybase.

(Incidently, you can save the data model in XML format. But the main
point with that is if you keep the model under verison control, you
can use a standard diff tool to see the differences between two versions.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>I might be misunderstanding your questions, but for that purpose a
>data-modelling tool is much better in my opinion. In our shop we
>use PowerDesigner from Sybase.

For some reason my Outlook Express is not downloading your last
post.

I checked the demo of PowerDesigner from Sybase. Modeling tool
is something I will have to look into in the next weeks/months.

Thanks

Is FOR XML EXPLICIT still an accepted technique?

I have inherited (someone else built it) an ASP IIS site attached to a SQL
Server 2000 database. It is quite a large web site job and I don't want to
rewrite it in .NET. I don't have the time to do that and I am not familiar
with .NET. Our company still uses VB6 for our products.
The remote site allows the user to select recordsets which currently can be
emailed as HTML or TEXT and also downloaded in an Excel (XLS) file. My job
is to create XML from the recordset, transmit it to the client browser
(which is part of a VB program) and have the client program load it into the
local SQL Server database.
I am new at using XML and have done considerable reading (my head hurts).
Some of the books are a couple years old. The recordsets are composed of
header records from the main table and child records (one to many) from 3
other tables.
I am leaning toward using FOR XML EXPLICIT in conjunction with ADODB stream
sent to the Response object. I have gotten a simple FOR XML AUTO program to
work properly and send the stream back to the browser, but now I need to
shape the more complicated XML properly.
I just want to make sure that the FOR XML EXPLICIT will not become "legacy"
code in the next few years. I have looked at using XML Views briefly, but do
not like the setup required on the SQL server to use them. It will be a
hosted remote server that houses the IIS ASP code and the SQL database.
So before I spend weeks writing and debugging the process, I want to make
sure I haven't missed some spectacular new, reliable and "easy" method of
accomplishing the same thing.
Also I plan on using the Transact/SQL OPENXML function to write the
resulting XML to the database at the client site.
There is one other problem. Using the ADODB stream sent to the Response
object results in the XML remaining "hidden" (such that a blank page appears
in the browser) which is fine...except I don't know how to access it. I
have experience using XML data islands (in HTML pages) to populate SQL
Server and also opening XML files on disk and writting to SQL Server.
Thanks for your help in advance...
For XML Explicit is definitely supported in the next release of SQL Server.
There is also a For XML Path option in the next release that would be easier
for you to use but anything you do in Explicit mode should work for the
foreseeable future. OpenXML is also fully supported in SQL Server 2005.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:e5SLwHbaEHA.3684@.TK2MSFTNGP09.phx.gbl...
>I have inherited (someone else built it) an ASP IIS site attached to a SQL
> Server 2000 database. It is quite a large web site job and I don't want to
> rewrite it in .NET. I don't have the time to do that and I am not familiar
> with .NET. Our company still uses VB6 for our products.
> The remote site allows the user to select recordsets which currently can
> be
> emailed as HTML or TEXT and also downloaded in an Excel (XLS) file. My job
> is to create XML from the recordset, transmit it to the client browser
> (which is part of a VB program) and have the client program load it into
> the
> local SQL Server database.
> I am new at using XML and have done considerable reading (my head hurts).
> Some of the books are a couple years old. The recordsets are composed of
> header records from the main table and child records (one to many) from 3
> other tables.
> I am leaning toward using FOR XML EXPLICIT in conjunction with ADODB
> stream
> sent to the Response object. I have gotten a simple FOR XML AUTO program
> to
> work properly and send the stream back to the browser, but now I need to
> shape the more complicated XML properly.
> I just want to make sure that the FOR XML EXPLICIT will not become
> "legacy"
> code in the next few years. I have looked at using XML Views briefly, but
> do
> not like the setup required on the SQL server to use them. It will be a
> hosted remote server that houses the IIS ASP code and the SQL database.
> So before I spend weeks writing and debugging the process, I want to make
> sure I haven't missed some spectacular new, reliable and "easy" method of
> accomplishing the same thing.
> Also I plan on using the Transact/SQL OPENXML function to write the
> resulting XML to the database at the client site.
> There is one other problem. Using the ADODB stream sent to the Response
> object results in the XML remaining "hidden" (such that a blank page
> appears
> in the browser) which is fine...except I don't know how to access it. I
> have experience using XML data islands (in HTML pages) to populate SQL
> Server and also opening XML files on disk and writting to SQL Server.
> Thanks for your help in advance...
>