| Author |
XML download and import scenario
|
|
| Bill Nguyen 2006-01-17, 1:23 pm |
| Our environment:
Windows server 2003
SQLserver 2000 SP3
VS.NET 2003
FTPserver on IIS 6.0
The attached XML will be uploaded to our FTPserver every hour.
We need to have a routine to pickup the file and insert new data into
corresponding SQLserver tables.
Please give me some idea how to accomplish.
Thanks a million
Bill
--------------------
<?xml version="1.0" encoding="utf-8" ?>
- <IDSData AccountNumber="100228" SiteNumber="12740525">
- <Inventorys>
- <Inventory Type="Physical">
- <!--
ATGID = 15006841487-1, Product = LSD -->
<TankNumber>2</TankNumber>
<DateTime>2006-01-12T17:00:50.000</DateTime>
<Volume>956.78</Volume>
<Ullage>46.2232</Ullage>
<Height>43.6</Height>
<WaterHeight>10.4164</WaterHeight>
</Inventory>
- <Inventory Type="Physical">
- <!--
..... similar data deleted....
--> <TankNumber>1</TankNumber>
<DateTime>2006-01-13T05:00:49.000</DateTime>
<Volume>942.35</Volume>
<Ullage>60.65</Ullage>
<Height>42.7</Height>
<WaterHeight>9.29032</WaterHeight>
</Inventory>
</Inventorys>
</IDSData>
| |
| Kent Tegels 2006-01-17, 8:24 pm |
| Hello Bill,
This would seem to be an ideal task for SQL Server 2005 Integration Services
feature as it has FTP support and tasks for processing XML. I've copied this
to the Integration newsgroup for follow-up by their experts.
Thanks!
Kent
> Our environment:
> Windows server 2003
> SQLserver 2000 SP3
> VS.NET 2003
> FTPserver on IIS 6.0
> The attached XML will be uploaded to our FTPserver every hour.
> We need to have a routine to pickup the file and insert new data into
> corresponding SQLserver tables.
> Please give me some idea how to accomplish.
> Thanks a million
> Bill
>
> --------------------
>
> <?xml version="1.0" encoding="utf-8" ?>
> - <IDSData AccountNumber="100228" SiteNumber="12740525">
> - <Inventorys>
> - <Inventory Type="Physical">
> - <!--
> ATGID = 15006841487-1, Product = LSD -->
> <TankNumber>2</TankNumber>
> <DateTime>2006-01-12T17:00:50.000</DateTime>
> <Volume>956.78</Volume>
> <Ullage>46.2232</Ullage>
> <Height>43.6</Height>
> <WaterHeight>10.4164</WaterHeight>
> </Inventory>
> - <Inventory Type="Physical">
> - <!--
> .... similar data deleted....
> --> <TankNumber>1</TankNumber>
> <DateTime>2006-01-13T05:00:49.000</DateTime>
> <Volume>942.35</Volume>
> <Ullage>60.65</Ullage>
> <Height>42.7</Height>
> <WaterHeight>9.29032</WaterHeight>
> </Inventory>
> </Inventorys>
> </IDSData>
| |
| Bill Nguyen 2006-01-17, 8:24 pm |
| Kent;
I do have intention to upgrade to SQLserver 05. However, it will be several
months before I can migrate our existing databases to it (due to lack of
resources).
What can I do in the meantime to use the data?
Thanks
Bill
"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad742cbe28c7
e98e540129d0@news.microsoft.com...
> Hello Bill,
>
> This would seem to be an ideal task for SQL Server 2005 Integration
> Services feature as it has FTP support and tasks for processing XML. I've
> copied this to the Integration newsgroup for follow-up by their experts.
>
> Thanks!
> Kent
>
>
>
| |
|
| Bill -
Keep in mind that you don't have to migrate your databases to SQL Server 05
to use the SSIS. You will have to install an instance of SQL Server 05
installed somewhere. But SSIS will use pre-2005 databases. If had SSIS
installed it should be pretty straightforward to accomplish the data flow
you desire.
Outside of SSIS, I would said you could write as simple service application
using ADO.NET to acomplish your goal.
Hope this help!
Bruce Thomas
"Bill Nguyen" < billn_nospam_please@
jaco.com> wrote in message
news:uwcYr67GGHA.2940@tk2msftngp13.phx.gbl...
> Kent;
>
> I do have intention to upgrade to SQLserver 05. However, it will be
> several months before I can migrate our existing databases to it (due to
> lack of resources).
> What can I do in the meantime to use the data?
>
> Thanks
> Bill
>
> "Kent Tegels" <ktegels@develop.com> wrote in message
> news:b87ad742cbe28c7
e98e540129d0@news.microsoft.com...
>
>
| |
| Michael Rys [MSFT] 2006-01-18, 1:23 pm |
| I would also encourage the use of SSIS. Otherwise write an ADO.Net or native
code service that takes the file and uses either the SQLXML XML Bulkload
object or OpenXML inside a stored proc to load the data into relations...
Best regards
Michael
"BET" <development.net@antispam.gmail.com> wrote in message
news:%23f4X7KEHGHA.516@TK2MSFTNGP15.phx.gbl...
> Bill -
>
> Keep in mind that you don't have to migrate your databases to SQL Server
> 05 to use the SSIS. You will have to install an instance of SQL Server 05
> installed somewhere. But SSIS will use pre-2005 databases. If had SSIS
> installed it should be pretty straightforward to accomplish the data flow
> you desire.
>
> Outside of SSIS, I would said you could write as simple service
> application using ADO.NET to acomplish your goal.
>
> Hope this help!
> Bruce Thomas
>
>
>
> "Bill Nguyen" < billn_nospam_please@
jaco.com> wrote in message
> news:uwcYr67GGHA.2940@tk2msftngp13.phx.gbl...
>
>
| |
| Bill Nguyen 2006-01-18, 8:24 pm |
| Thanks all of you for giving me tips on this subject.
I'll be attending VS live in SF next month and will learn more about
SQLserver05.
No doubt that I will be using SQLserver05 soon.
Bill
"Michael Rys [MSFT]" <mrys@online.microsoft.com> wrote in message
news:Ok$eOOGHGHA.2012@TK2MSFTNGP14.phx.gbl...
>I would also encourage the use of SSIS. Otherwise write an ADO.Net or
>native code service that takes the file and uses either the SQLXML XML
>Bulkload object or OpenXML inside a stored proc to load the data into
>relations...
>
> Best regards
> Michael
>
> "BET" <development.net@antispam.gmail.com> wrote in message
> news:%23f4X7KEHGHA.516@TK2MSFTNGP15.phx.gbl...
>
>
| |
| Bill nguyen 2006-01-23, 9:23 am |
| Please elaborate on this a little bit more:
1. If I install SQLserver 05 on a different server, can I use SSIS to access
a database residing on another physical server?
For example, server A has SQL2000 with the database I would like to use with
SSIS. Server B has SQLserver 05 installed.
2. Is it ok to install the eval copy of SQLserver 05 (in technet CD) and
turn it into permanent later on or I have to obtain a production version of
SQL05?
Thanks a million
Bill
"BET" <development.net@antispam.gmail.com> wrote in message
news:%23f4X7KEHGHA.516@TK2MSFTNGP15.phx.gbl...
> Bill -
>
> Keep in mind that you don't have to migrate your databases to SQL Server
> 05 to use the SSIS. You will have to install an instance of SQL Server 05
> installed somewhere. But SSIS will use pre-2005 databases. If had SSIS
> installed it should be pretty straightforward to accomplish the data flow
> you desire.
>
> Outside of SSIS, I would said you could write as simple service
> application using ADO.NET to acomplish your goal.
>
> Hope this help!
> Bruce Thomas
>
>
>
> "Bill Nguyen" < billn_nospam_please@
jaco.com> wrote in message
> news:uwcYr67GGHA.2940@tk2msftngp13.phx.gbl...
>
>
|
|
|
|