Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Difficulty on inserting data into several tables from XML file using Bulk Insert

Hi, I am a newbie for SQL's XML section and need some help from you guys. I just go through some simple articles with simple sample record. However, I found confusing when I want to inserting data into several tables from a prefer XML file.

My scenario is like that :

Tables :

1) trade ( id int)

2) trans ( id int , startdate datetime , enddate datetime , status bit ,comment varchar(50), tranCode int)

* tranCode = id from trade table

3) transInfo ( id int , userlogin int , amount money , transID int ) * transID = id from trans table

4) trandetails ( id int, transID int ) * transID = id from trans table

Preferred XML :

<?xml version='1.0' encoding='utf-8'?>

<ROOT>

<trade code="1">

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Alex" amount="1000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Shipping</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>

</trans>

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Ken" amount="20000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Transportation</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>
</trans>

</trade>

</ROOT>

I am confusing on how to arrange up the elements based on the tables which were created. Or have to re-fix my table ? Any suggestion based on my scenario. Hope able to get any ideas/opinions from here asap. Thanks alot.

Best Regards,

Hans

Problem solved although kinda confusing at the early stage when doing the XML schema.

Different XML elements on the same level?

I would like to generate an XML document using SQL Server 2000's explicit option.

I want something like this for my output:

<root>
<someNode1>
<someNode2 />
</someNode1>
<someNode3>
<someNode4 />
</someNode3>
</root>

The problem is that I end up getting an "originally declared" node being "redeclared" as something else error. In this case, someNode1 is "redeclared" as someNode3. It looks as though it's not possible to create two different elements on the same level. I've not found any samples in documentation that show such a scenario. Anybody have a solution or hack? It seems like this kind of output would be common in the business world.

TIA,

Ian

Hi there,

I'm no expert on this but came across your post whilst being up against a similar problem.

This URL helped me out: http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000535.htm

This is a working example derived from something I am working on myself:

SELECT
1 AS Tag,
NULL AS Parent,
1 AS [Item!1!Id],
'Item' AS [Item!1!Name],
1 AS [Item!1!ParentId],
NULL AS [NutritionalBreakdown!2!Calories!element],
NULL AS [NutritionalBreakdown!2!Protein!element]

UNION ALL

SELECT
2,
1,
NULL,
NULL,
NULL,
'test calorie data',
'test protein data'

ORDER BY Tag
FOR XML EXPLICIT

Produces:

<Item Id="1" Name="Item" ParentId="1">

<NutritionalBreakdown>

<Calories>test calorie data</Calories>

<Protein>test protein data</Protein>

</NutritionalBreakdown>

</Item>

Hope that helps!

Different XML elements on the same level?

I would like to generate an XML document using SQL Server 2000's explicit option.

I want something like this for my output:

<root>
<someNode1>
<someNode2 />
</someNode1>
<someNode3>
<someNode4 />
</someNode3>
</root>

The problem is that I end up getting an "originally declared" node being "redeclared" as something else error. In this case, someNode1 is "redeclared" as someNode3. It looks as though it's not possible to create two different elements on the same level. I've not found any samples in documentation that show such a scenario. Anybody have a solution or hack? It seems like this kind of output would be common in the business world.

TIA,

Ian

Hi there,

I'm no expert on this but came across your post whilst being up against a similar problem.

This URL helped me out: http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000535.htm

This is a working example derived from something I am working on myself:

SELECT
1 AS Tag,
NULL AS Parent,
1 AS [Item!1!Id],
'Item' AS [Item!1!Name],
1 AS [Item!1!ParentId],
NULL AS [NutritionalBreakdown!2!Calories!element],
NULL AS [NutritionalBreakdown!2!Protein!element]

UNION ALL

SELECT
2,
1,
NULL,
NULL,
NULL,
'test calorie data',
'test protein data'

ORDER BY Tag
FOR XML EXPLICIT

Produces:

<Item Id="1" Name="Item" ParentId="1">

<NutritionalBreakdown>

<Calories>test calorie data</Calories>

<Protein>test protein data</Protein>

</NutritionalBreakdown>

</Item>

Hope that helps!

different schema formats? (SQLXMLBulkLoad and VS2k3)

Howdy,
What is different between the schema requirements for SQLXMLBulkLoad
and the schema generated from XML with Visual Studio?
Specifically I'm trying to bulk load XML output from the gotdotnet
SharePoint Reports utility
(http://www.gotdotnet.com/workspaces...r />
cb29970eb)
with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
Visual Studio 2003.
I've got the BOL examples working, including using SchemaGen to create
the required tables. However running the bulk load with my target XML
doesn't seem to do anything--no tables created, no data loaded, no
error at the command prompt, and no error log generated.
Any tips?
TIA,
Sean G.Hi,
If you send me the schema and the xml file I will take a look of your proble
m.
--
Thanks,
Monica Frintu
"SeanGerman@.gmail.com" wrote:

> Howdy,
> What is different between the schema requirements for SQLXMLBulkLoad
> and the schema generated from XML with Visual Studio?
> Specifically I'm trying to bulk load XML output from the gotdotnet
> SharePoint Reports utility
> (http://www.gotdotnet.com/workspaces.../>
accb29970eb)
> with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
> Visual Studio 2003.
> I've got the BOL examples working, including using SchemaGen to create
> the required tables. However running the bulk load with my target XML
> doesn't seem to do anything--no tables created, no data loaded, no
> error at the command prompt, and no error log generated.
> Any tips?
>
> TIA,
>
> Sean G.
>|||Thanks for the offer Monica. But I've decided to build a schema by
hand rather than fix the one created by Visual Studio.
I don't know enough about XML and XSD to describe all the relevant
differences between the automatically created schema that doesn't work
and the manually created one that is working, but let me know if you're
interested in a comparison between what VS creates and what BulkLoad
wants.
Thanks again,
Sean G.|||Hi Sean,
Yes, it may be useful for us to look at the differences...
Thanks
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136478947.978472.29760@.g47g2000cwa.googlegroups.com...
> Thanks for the offer Monica. But I've decided to build a schema by
> hand rather than fix the one created by Visual Studio.
> I don't know enough about XML and XSD to describe all the relevant
> differences between the automatically created schema that doesn't work
> and the manually created one that is working, but let me know if you're
> interested in a comparison between what VS creates and what BulkLoad
> wants.
> Thanks again,
>
> Sean G.
>|||Michael,
I'll be able to post the schema files this evening, but I can summarize
the differences. To reiterate, the xml is output from the gotdotnet
SharePoint Reports utility.
For the working file I used xsd:schema while the one generated by
Visual Studio 2k3 is xs:schema.
For the working file, elements have the minimum attributes--name,
type(, sql:relation, sql:relationship as needed).
The generated file has minOccurs, maxOccurs.
The working file has the xsd:annotation element and sql:relation and
sql:relationship attributes for SchemaGen=True.
The generated schema does not have this information, but still doesn't
work even after I add it.
There are some elements in the xml I have commented out in the
schema--primarily for dashes in element names--but those kick up an
"invalid value for 'column'" error during BulkLoad.
The thing that puzzles me is BulkLoad with the VS-generated schema
produces no (visible) output--no tables created, no data loaded, and no
error messages.
Other times I've seen this behavior from BulkLoad indicated a logical
error in the schema data. E.g. a well-formatted schema where the
element names don't match the names in the xml; or something labeled an
element in the schema is an attribute in the xml. But I can't find any
such issue in this schema. *shrug*
But like I said, I was able to create a (mostly, except for invalid
column names) working schema, though I wouldn't mind pinning down this
issue for the sake of edukashun. ;)
Sean G.

different schema formats? (SQLXMLBulkLoad and VS2k3)

Howdy,
What is different between the schema requirements for SQLXMLBulkLoad
and the schema generated from XML with Visual Studio?
Specifically I'm trying to bulk load XML output from the gotdotnet
SharePoint Reports utility
(http://www.gotdotnet.com/workspaces/...2-5accb29970eb)
with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
Visual Studio 2003.
I've got the BOL examples working, including using SchemaGen to create
the required tables. However running the bulk load with my target XML
doesn't seem to do anything--no tables created, no data loaded, no
error at the command prompt, and no error log generated.
Any tips?
TIA,
Sean G.
Hi,
If you send me the schema and the xml file I will take a look of your problem.
Thanks,
Monica Frintu
"SeanGerman@.gmail.com" wrote:

> Howdy,
> What is different between the schema requirements for SQLXMLBulkLoad
> and the schema generated from XML with Visual Studio?
> Specifically I'm trying to bulk load XML output from the gotdotnet
> SharePoint Reports utility
> (http://www.gotdotnet.com/workspaces/...2-5accb29970eb)
> with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
> Visual Studio 2003.
> I've got the BOL examples working, including using SchemaGen to create
> the required tables. However running the bulk load with my target XML
> doesn't seem to do anything--no tables created, no data loaded, no
> error at the command prompt, and no error log generated.
> Any tips?
>
> TIA,
>
> Sean G.
>
|||Thanks for the offer Monica. But I've decided to build a schema by
hand rather than fix the one created by Visual Studio.
I don't know enough about XML and XSD to describe all the relevant
differences between the automatically created schema that doesn't work
and the manually created one that is working, but let me know if you're
interested in a comparison between what VS creates and what BulkLoad
wants.
Thanks again,
Sean G.
|||Hi Sean,
Yes, it may be useful for us to look at the differences...
Thanks
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136478947.978472.29760@.g47g2000cwa.googlegro ups.com...
> Thanks for the offer Monica. But I've decided to build a schema by
> hand rather than fix the one created by Visual Studio.
> I don't know enough about XML and XSD to describe all the relevant
> differences between the automatically created schema that doesn't work
> and the manually created one that is working, but let me know if you're
> interested in a comparison between what VS creates and what BulkLoad
> wants.
> Thanks again,
>
> Sean G.
>
|||Michael,
I'll be able to post the schema files this evening, but I can summarize
the differences. To reiterate, the xml is output from the gotdotnet
SharePoint Reports utility.
For the working file I used xsd:schema while the one generated by
Visual Studio 2k3 is xs:schema.
For the working file, elements have the minimum attributes--name,
type(, sql:relation, sql:relationship as needed).
The generated file has minOccurs, maxOccurs.
The working file has the xsd:annotation element and sql:relation and
sql:relationship attributes for SchemaGen=True.
The generated schema does not have this information, but still doesn't
work even after I add it.
There are some elements in the xml I have commented out in the
schema--primarily for dashes in element names--but those kick up an
"invalid value for 'column'" error during BulkLoad.
The thing that puzzles me is BulkLoad with the VS-generated schema
produces no (visible) output--no tables created, no data loaded, and no
error messages.
Other times I've seen this behavior from BulkLoad indicated a logical
error in the schema data. E.g. a well-formatted schema where the
element names don't match the names in the xml; or something labeled an
element in the schema is an attribute in the xml. But I can't find any
such issue in this schema. *shrug*
But like I said, I was able to create a (mostly, except for invalid
column names) working schema, though I wouldn't mind pinning down this
issue for the sake of edukashun. ;)
Sean G.

different results using ISO-8859-1 vs utf-8 with sp_xml_preparedDocument

We have a stored procedure which is passed an xml string (text datatype) and that string is passed on to sp_xml_prepared_Document (btw its a Sql 2000 server). We've had no problems until yesterday when a really long xml string (>18,000) was passed in and all of sudden the proc stopped working with an error message of:

"Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 9

XML parsing error: An invalid character was found in text content."

So after doing a lot of googling i found a post stating the following:

"I found that I have to place an xml declaration of

<?xml version="1.0" encoding="ISO-8859-1"?>in my XML string so the sp_xml_prepared_Document stored procedure will treatthe data as UTF-8 and not the Database's code page."

The encoding value I had always used was utf-8. Sure enough when I changed the encoding value to ISO-8859-1, the proc worked as expected. So I would like a better explanation as to what is going on here.

Why do shorter strings work fine when the encoding value is "utf-8" and longer ones only work when ISO-8859-1 is used?

Should I always use ISO-8859-1 as my encoding value?

Thanks for your help!

Thanks to Michael Rys and his blog for helping explain this. The article below was lifted from his site and gives a good explanation of whats happening. I think we will try changing the datatype to NText and see if that alleviates any future headaches.

Recently, I received several customer reports, that sp_xml_preparedocument started rasing the following errors after upgrading their XML-based application from SQL Server 2000 SP3a (or earlier) to either SP4 or SQL Server 2005:

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is `An invalid character was found in text content.`.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

This error is now raised because of a stricter error-discovery during parsing. When we moved from MSXML 2.6 to MSXMLSQL for SQL Server 2000 SP4 and SQL Server 2005, we fixed a couple of bugs in the parser that could lead to data corruption (invalid data being parsed). As a consequence, you are now receiving this error code instead of having invalid characters accepted.

The consequence is that one has to be more explicit with setting the encoding. This will also help mitigate against involuntary data corruption (see below for an example). There are two ways to fix an application (besides moving to an XML datatype and the nodes() method):

1. Make sure that the XML document when passed in a TEXT or (VAR)CHAR argument is compatible with the default code page of the database (or the string type) by either

1. setting the encoding property in the XML declaration (e.g., when the code page is ISO-Latin1), or

2. by making sure that the code page of the database (or string type) can preserve all UTF-8 code points that will ever be passed in through the string.

2. Change the type of the argument to NTEXT (or N(VAR)CHAR) and pass in the XML in UTF-16 encoding (an XML declaration is optional).

Here are the technical details:

sp_xml_preparedocument takes either a single-byte character string (TEXT, (VAR)CHAR) or a two-byte character string (NTEXT, N(VAR)CHAR). In the first case, the string is associated with a code page (normally the databases default code page). In the second case the string is assumed to be either UCS-2 or UTF-16 encoded. Sp_xml_preparedocument (unlike the newer XML datatype parser) will only pick up the strings code page in the second case to detect the encoding of the XML document, but not in the first case. Instead it will look at the string and follow the XML 1.0 spec detection rules. That means that for a single-byte character string unless there is an XML declaration saying otherwise, the data will be parsed as UTF-8. This works fine as long as your instance documents happen to only use characters that share the same code points on the UTF-8 and the database code page (e.g., the ASCII range in ISO-Latin1 and UTF-8), but it will lead to problems, if you use characters that are mapped to different code pages.

For example, the character (Unicode U+00AE, represented as 0xAE00 in SQL Server) will be represented in an ISO-LATIN1 code page (SQL_Latin1_General_CP1_CS_AS) as 0xAE. However if you do not specify an encoding on the XML document that contains the character and pass it as a single-byte character string, the XML parser will interpret the code point 0xAE not as the character but as an invalid starting character of a multi-byte UTF-8 encoding: UTF-8 characters bit sequences start either with 0 (1-byte encoded characters), 110 (two byte encoded characters), 1110 (three byte encoded characters), or 11110 (4-byte encoded surrogate pairs), while 0xAE is 10101110.

In MSXML 2.6 (and thus SQL Server 2000 SP3 and earlier), the XML parser would preserve such invalid characters and thus corrupt the data, while in MSXML 3.0/MSXMLSQL (and thus SQL Server 2000 SP4 and SQL Server 2005), the parser will reject them.

Note that you will still have to watch out for data corruption if the ISO-Latin characters make up a valid UTF-8 encoded character (solutions are as outlined above). For example,

declare @.h int
exec sp_xml_preparedocument @.h output, ``
select * from openxml(@.h, `/root`) with (attr nvarchar(200) `@.attr`)
exec sp_xml_removedocument @.h

will parse in SQL Server 2005 but return the character ? (U+05D1) since the two input characters (0xD791 in an ISO-Latin1 encoding) form the bit stream: 11010111 10010001 which is translated into 00000101 11010001 according to the UTF-8 encoding rules which is a valid UTF-8 2-byte encoded character representing U+05D1.

I hope you agree, that detecting such issues is better, even if we break "backwards-bug-compatibility".