Importing XML File via URL using MSSQL 2005

I have an XML file that is hosted on a mainframe web server. I need to read
the file via a URL and import this into my SQL database.
I'm fairly new to SQL 2005 but this seems to be a simple task. I've looked
at using the Import Export wizard but I don't see a simpl way to do this.
I've tried selecting "Flat File" as my data source but this does not seem to
be able to read XML. I've tried the SQLSML data sources but I don't think
this will work if the web server is not IIS.
Does anyone have any ideas?"Bryan" <Bryan@.discussions.microsoft.com> wrote in message
news:37B66D17-FE91-4CFD-B502-DB80BD2B8360@.microsoft.com...
>I have an XML file that is hosted on a mainframe web server. I need to
>read
> the file via a URL and import this into my SQL database.
> I'm fairly new to SQL 2005 but this seems to be a simple task. I've
> looked
> at using the Import Export wizard but I don't see a simpl way to do this.
> I've tried selecting "Flat File" as my data source but this does not seem
> to
> be able to read XML. I've tried the SQLSML data sources but I don't think
> this will work if the web server is not IIS.
> Does anyone have any ideas?
Write a SQLCLR function in C# or VB to retrieve the data from the web
server.

Importing Pervasive SQL into SQL2005 via SSIS

In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.

In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net Providers\Odbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DB\SQL Native Client) to the SQL database. Both databases reside on the same machine.

I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.

Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.

This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie

connect to the data conversion task before inserting into the destination or provide false for metadata in the properties of both the task.|||

The issue is still with the Data Conversion. I've been reading many of the forums and it seems that, like myself, there are a lot of frustrated developers out there trying to make simple sql2000 dts packages work in sql2005 SSIS. Almost all of them point to the data conversion issue. What a real pain in the arse. I surely hope that Microsoft does something about this, SOON...

Something simple that would take less than an hour could turn into weeks and weeks of fruitless effort.

The client has a pervasive sql database that i did many tests on using sql2000 dts. There's approximately 150 tables. My testing went very well. I recommended to the client that they purchase a new server Win2003 R2 along with SQL2005, advising them that my development time in SQL2005, converting the tables with DTS would be a much better process with the upgrade (is it an really an upgraded when you have all this bickering on unicode (data conversion) issues).

I have spent countless hours trying to get this working in sql2005 ssis, with no success.

I wish in retrospect that i'd just had them get Win2000 & SQL2000. I would have been done with the project by now and i would have had a happy client. I feel like a damn fool, recommending a package that i thought would be a significant DEVELPOR improvement, and have yet to show any results. This does not bode well for others like me at all.

|||

I have been testing and trying for days, almost a week trying to create a simple, single table SSIS package that i created in DTS in minutes. I'm very frustrated with how the whole process gets hung up in the data conversion.

I have a DataReader Source (an ODBC connection (System DSN) to a Pervasive SQL Database). It pulls the table in just fine. I have a DataFlow Task, using a Data Conversion. I have a SQL Server Destination (The table already exists in the SQL 2005 database).|||

Please do this, I had did same thing in my package.

double click on data task flow,

then click on source task go to it's properties windows

there is a properties called ValidateExternalMetadata :- set is False

repeat for destination task properties.

|||

Your recommendation seems to work for Numerical Conversions.

BUT...

Not for string transformations. The strings come in as unicode DT-WSTR.

In the Data Conversion Transformation Editor, I have to manually create a transformation for each and every instance, from DT_WSTR to DT_STR. Once i've done this, it passes muster with running in Debug. This would still require lots of tedious manual, repetitive tasks.

Is there a way to force the DT_WSTR to DT_STR for all of these so i don't have to manually create a Transformation for each?

Or...

Do you have another suggestion on the STR issue that i might try?

Thanks too very much for your assistance!!!

Importing Pervasive SQL into SQL2005 via SSIS

In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.

In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net Providers\Odbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DB\SQL Native Client) to the SQL database. Both databases reside on the same machine.

I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.

Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.

This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie

connect to the data conversion task before inserting into the destination or provide false for metadata in the properties of both the task.|||

The issue is still with the Data Conversion. I've been reading many of the forums and it seems that, like myself, there are a lot of frustrated developers out there trying to make simple sql2000 dts packages work in sql2005 SSIS. Almost all of them point to the data conversion issue. What a real pain in the arse. I surely hope that Microsoft does something about this, SOON...

Something simple that would take less than an hour could turn into weeks and weeks of fruitless effort.

The client has a pervasive sql database that i did many tests on using sql2000 dts. There's approximately 150 tables. My testing went very well. I recommended to the client that they purchase a new server Win2003 R2 along with SQL2005, advising them that my development time in SQL2005, converting the tables with DTS would be a much better process with the upgrade (is it an really an upgraded when you have all this bickering on unicode (data conversion) issues).

I have spent countless hours trying to get this working in sql2005 ssis, with no success.

I wish in retrospect that i'd just had them get Win2000 & SQL2000. I would have been done with the project by now and i would have had a happy client. I feel like a damn fool, recommending a package that i thought would be a significant DEVELPOR improvement, and have yet to show any results. This does not bode well for others like me at all.

|||

I have been testing and trying for days, almost a week trying to create a simple, single table SSIS package that i created in DTS in minutes. I'm very frustrated with how the whole process gets hung up in the data conversion.

I have a DataReader Source (an ODBC connection (System DSN) to a Pervasive SQL Database). It pulls the table in just fine.

I have a DataFlow Task, using a Data Conversion.

I have a SQL Server Destination (The table already exists in the SQL 2005 database).|||

Please do this, I had did same thing in my package.

double click on data task flow,

then click on source task go to it's properties windows

there is a properties called ValidateExternalMetadata :- set is False

repeat for destination task properties.

|||

Your recommendation seems to work for Numerical Conversions.

BUT...

Not for string transformations. The strings come in as unicode DT-WSTR.

In the Data Conversion Transformation Editor, I have to manually create a transformation for each and every instance, from DT_WSTR to DT_STR. Once i've done this, it passes muster with running in Debug. This would still require lots of tedious manual, repetitive tasks.

Is there a way to force the DT_WSTR to DT_STR for all of these so i don't have to manually create a Transformation for each?

Or...

Do you have another suggestion on the STR issue that i might try?

Thanks too very much for your assistance!!!

importing MDF database

As a newbie, I'm having problems importing a copy of my SQLServer2k development database from my laptop (via a CD), to my production SQL Server2k database.

I've tried the DTS Import/Export wizard & also the Copy wizard, but can't seem to see the dialog that prompts & identifies the source path & filename.

I've tried setting up an ODBC DSN for the database, but get the same problem.

Coming from an Access background, I've been used to the copying of *.mdb files, and half expected that level of simplicity with SQLS2K - but clearly, I'm wrong and am missing something, which the on-line help isn't able to clarify for me...

Can anyone please give me any pointers for me to get a copy of my development database along with its stored procedures, from my laptop (not networked), to my desktop server. Both use SQLS2k.

Thanks in advance, AlexSo let me get this strait. You want to restore a copy of your database thats are on a CD?|||The easiest for you would be to either copy the MDF file along with LDF file onto a local drive of the server from the CD and use Enterprise Manager (EM) to Attach Database, or create a backup on your laptop and then restore from that backup file.|||Originally posted by rdjabarov
The easiest for you would be to either copy the MDF file along with LDF file onto a local drive of the server from the CD and use Enterprise Manager (EM) to Attach Database, or create a backup on your laptop and then restore from that backup file. ...and remember to mark the files Read/Write again! I've never been bitten by that, but I read about this guy that was once!

-PatP|||This link http://vyaskn.tripod.com/moving_sql_server.htm refers moving database from one system to another, just follow instead moving consider copying the files.

Also refer to the corresponding KBAs which refers to resolve any login issue arises.|||Thanks all very much for your prompt replies. Just as I was applying the suggested "Attach" method, the motherboard in my Dell server went kaput! So I won't be able to try it until the weekend - when I hope to report back success.

Interestingly the link provided in one of the responses, was very useful and included code to automate the attachment within a stored procedure (if necessary). I aim to try the menu-dropdown "manual" attachment first, & see how I get on with that.

All the best - & thanks again
Alex|||Thanks to all who replied & helped (especially Pat, Rdjabarov, & Satya).

I've just had the server's power supply replaced & tried your suggestions - the attach worked fine (from the Enterprise>Actions>AllTasks>Attach)

Best regards, & thanks again
Alexsql

Importing Infopath Data

Hi
I'm relatively new to importing xml files into SQL. I have some infopath
files and I've tried the normal xml importing via sp_xml_preparedocument and
the other procs used for xml.
The xml can't be parsed becuase of an "invalid character".
I was wondering if my approach is correct ?
Thanks in advance.try this link:
http://support.microsoft.com/defaul...Q23883
3
regards,
Sarav...
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:487FE266-C661-48B0-8B83-089AB4D96290@.microsoft.com...
> Hi
> I'm relatively new to importing xml files into SQL. I have some infopath
> files and I've tried the normal xml importing via sp_xml_preparedocument
> and
> the other procs used for xml.
> The xml can't be parsed becuase of an "invalid character".
> I was wondering if my approach is correct ?
> Thanks in advance.

Importing from Excel to SQL Server 2005 Express

I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.

Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.

Anyone with more clues than me?

thanks

Just setting up the linked server doesn't do anything for the data - you have to issue a query against it. Linked servers are useful if you want to continue working with the data in Excel, but also see it in SQL Server.

If you're just looking to import the data from Excel into SQL Server, you can use Integration Services to do that much quicker, or you can save the Excel file as a comma-separated file (CSV) and use the command line tool called bcp to copy it in. More on both here:

http://support.microsoft.com/kb/321686

Buck Woody

Importing from Excel to SQL Server 2005 Express

I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.

Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.

Anyone with more clues than me?

thanks

Just setting up the linked server doesn't do anything for the data - you have to issue a query against it. Linked servers are useful if you want to continue working with the data in Excel, but also see it in SQL Server.

If you're just looking to import the data from Excel into SQL Server, you can use Integration Services to do that much quicker, or you can save the Excel file as a comma-separated file (CSV) and use the command line tool called bcp to copy it in. More on both here:

http://support.microsoft.com/kb/321686

Buck Woody

sql

Importing DB from Another Install

I have a MS SQL database generated from another user and
sent to me via email. I am trying to import it into my
install of SQL Server 2000. The two approaches I have
tried have not worked, but maybe I am missing a trick.
1) Set up tables with the query analyzer and use the
import wizard under All Tasks >> Import. Even after
registering the datasource for import in ODBC Datasources
control panel, the db for import does not appear in the
list of datasources for import with this wizard.
2) Create a new blank database and copy the datafile for
import to the directory indicated as the location of the
blank database datafile. When I do this the database is
greyed out in Enterprise Manager and marked 'suspect'. No
tables are viewable under the database after copying.
I've also tried this approach after setting up the table
structure with the Query Analyzer.Jennifer,
Short answer: search the SQL Server 2000 Books Online for the section
"Attaching and Detaching a Database"
Longer answer ... and assuming that you have Enterprise Manager and that
your user has sent you **both** the data (.mdf) and log (.ldf) files:
In EM: right-click the "Databases" folder: All Tasks | Attach Database
Chief Tenaya
"Jennifer Sanders" <anonymous@.discussions.microsoft.com> wrote in message
news:e7d601c41022$fe75ec10$a601280a@.phx.gbl...
> I have a MS SQL database generated from another user and
> sent to me via email. I am trying to import it into my
> install of SQL Server 2000. The two approaches I have
> tried have not worked, but maybe I am missing a trick.
> 1) Set up tables with the query analyzer and use the
> import wizard under All Tasks >> Import. Even after
> registering the datasource for import in ODBC Datasources
> control panel, the db for import does not appear in the
> list of datasources for import with this wizard.
> 2) Create a new blank database and copy the datafile for
> import to the directory indicated as the location of the
> blank database datafile. When I do this the database is
> greyed out in Enterprise Manager and marked 'suspect'. No
> tables are viewable under the database after copying.
> I've also tried this approach after setting up the table
> structure with the Query Analyzer.|||I found out it's a backup file.
>--Original Message--
>Jennifer,
>Short answer: search the SQL Server 2000 Books Online for
the section
>"Attaching and Detaching a Database"
>Longer answer ... and assuming that you have Enterprise
Manager and that
>your user has sent you **both** the data (.mdf) and log
(.ldf) files:
>In EM: right-click the "Databases" folder: All Tasks |
Attach Database
>Chief Tenaya
>
>"Jennifer Sanders" <anonymous@.discussions.microsoft.com>
wrote in message
>news:e7d601c41022$fe75ec10$a601280a@.phx.gbl...
Datasources
No
>
>.
>

Importing data into SQL Server 2005 via ODBC

Hi

I've got an Ingres database of some 200 tables which I need to import
every night into SQL Server 2005 for use by Reporting Services. Most
of the tables will come across unchanged (a few need massaging to
handle time intervals correctly), but the Import Wizard only seems to
want to import one table (or more accurately query) at a time. I seem
to remember the old 2000 Import Wizard handled multiple tables - is
there any way of processing multiple tables in 2005, or must I resign
myself to writing 200 import packages in SSIS.

Chloe Crowder
The British LibraryChloe C (chloe@.mcrowdd.plus.com) writes:

Quote:

Originally Posted by

I've got an Ingres database of some 200 tables which I need to import
every night into SQL Server 2005 for use by Reporting Services. Most
of the tables will come across unchanged (a few need massaging to
handle time intervals correctly), but the Import Wizard only seems to
want to import one table (or more accurately query) at a time. I seem
to remember the old 2000 Import Wizard handled multiple tables - is
there any way of processing multiple tables in 2005, or must I resign
myself to writing 200 import packages in SSIS.

Are you looking at the Import Wizard in Mgmt Studio? I think you should
look into SQL Server Integration Services, for which you can create
packages in Business Intelligence Development Studio. (Although that is
as much I know about SSIS.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Chloe,

Just had to do this today and it seems to work fine (although importing
multiple Excel sheets to multiple tables). After you select the Source and
Destination, there is a screen that asks to "Copy data from one or more
tables/views" or "Query" (not sure about the exact wording of the options
but something close). Then you can do mapping to destination tables/columns.
The last step is to execute or save as SSIS package.

If you do not see that option, check if you have the latest service pack. It
will be worth testing with SQL Server as Source, just to see if it has to do
with the driver for the source (I am confident you can import multiple
tables when the source is SQL Server).

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Tue, 27 Nov 2007 19:13:10 -0500, "Plamen Ratchev"
<Plamen@.SQLStudio.comwrote:

Quote:

Originally Posted by

>Hi Chloe,
>
>Just had to do this today and it seems to work fine (although importing
>multiple Excel sheets to multiple tables). After you select the Source and
>Destination, there is a screen that asks to "Copy data from one or more
>tables/views" or "Query" (not sure about the exact wording of the options
>but something close). Then you can do mapping to destination tables/columns.
>The last step is to execute or save as SSIS package.
>
>If you do not see that option, check if you have the latest service pack. It
>will be worth testing with SQL Server as Source, just to see if it has to do
>with the driver for the source (I am confident you can import multiple
>tables when the source is SQL Server).
>
>HTH,
>
>Plamen Ratchev
>http://www.SQLStudio.com
>

Plamen

thanks for this. Importing multiple tables works well from SQL Server,
but with the Ingres server as the source the Copy data from one or
more tables/view option is greyed out, leaving only the option of a
single select statement in a query.

Ah well, I'll do it the hard way.

Chloe|||On Tue, 27 Nov 2007 22:59:18 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Chloe C (chloe@.mcrowdd.plus.com) writes:

Quote:

Originally Posted by

>I've got an Ingres database of some 200 tables which I need to import
>every night into SQL Server 2005 for use by Reporting Services. Most
>of the tables will come across unchanged (a few need massaging to
>handle time intervals correctly), but the Import Wizard only seems to
>want to import one table (or more accurately query) at a time. I seem
>to remember the old 2000 Import Wizard handled multiple tables - is
>there any way of processing multiple tables in 2005, or must I resign
>myself to writing 200 import packages in SSIS.

>
>Are you looking at the Import Wizard in Mgmt Studio? I think you should
>look into SQL Server Integration Services, for which you can create
>packages in Business Intelligence Development Studio. (Although that is
>as much I know about SSIS.)

Erland

I was rather hoping to, in effect, automate the creation of my SSIS
package with the Import Wizard, otherwise I need to do each import as
a separate task in SSIS.

Ah well, do it the hard way...

Chloe