Optimize for Many Tables and Temp files

We are using the Import/Export wizard to create some simple packages to transfer tables. When doing a lrge number of tables, the 'Optimize for many tables' option is automatically selected (as noted in BOL). What we've found is that the package creates a bunch of temp files in the creator's Documents and Settings....Temp folder. Needless to say this package cannot be re-run later, nor scheduled, since the path referenced doesn't necessarily exist on the server. Is there a way to specify where these files should be created so thatthe package is re-usable and still be optimized?

Steve

Yes, we already had people complaining about this.

There are a few options to workaround this, though:

1. Move all those files to the safe location and update the package accordingly.

2. If your number of tables is not too big (less than 100 should work on an average machine), uncheck the "Optimize for many tables" option and see if it runs for you. If it does not run, create a few sequential data flows and split the simple flows that the wizard had placed in the single Data Flow task into several of them. You can use the Copy/Paste for that.

HTH.

OPENXML Parsing Error: Multiple colons are not allow

I'm receiving the above parsing error for this document. I am trying to create an export that can be sent to a sister program and read into their database (same schemas). If I can export the entire entity (I do this using a union with nulls), I should
also be able to import it into another's database and maintain integrity. This is my attempt at the export half of it. I need to use OPENXML to be able to read the document once I receive it. This one is in non-binary format. The one I intend to use i
s binary, but binary is hard to debug so it is in this format. I have no idea what I am doing.
Thanks for any help.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
--Sample XML document
SET @.doc ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="Tag" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID" rs:autoincrement="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="SLSMNID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="CusID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
</s:AttributeType>
<s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c15" rs:name="P!2!active" rs:number="16" rs:nullable="true">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, "/>
<z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True"/>
<z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True" c16="394" c17="2759" c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
</rs:data>
</xml>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( QuoteID int '.',
QuoteID int '../@. QuoteID',
QuoteID int '../../@. QuoteID')
EXEC sp_xml_removedocument @.idoc
Regards,
Jamie
It is not multiple colons that are the problem but that your WITH clause
uses the same name for all the columns.
Try:
SELECT c1 as QuoteID, c2 as QuoteID, c3 as QuoteID
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( c1 int '.',
c2 int '../@.QuoteID',
c3 int '../../@.QuoteID')
Furthermore, you want your @.doc variable to be at least of size 4000. And
you probably want to use path expressions in the OpenXML that actually
extracts information from your XML.
I don't understand your comment about binary format. OpenXML requires XML
and not some binary format.
I hope now have a better idea of what you are doing :-)
HTH
Michael
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:4FFA87D1-077F-40AF-AE84-F2C1AA325BEE@.microsoft.com...
> I'm receiving the above parsing error for this document. I am trying to
> create an export that can be sent to a sister program and read into their
> database (same schemas). If I can export the entire entity (I do this
> using a union with nulls), I should also be able to import it into
> another's database and maintain integrity. This is my attempt at the
> export half of it. I need to use OPENXML to be able to read the document
> once I receive it. This one is in non-binary format. The one I intend to
> use is binary, but binary is hard to debug so it is in this format. I
> have no idea what I am doing.
> Thanks for any help.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> --Sample XML document
> SET @.doc ='
> <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
> <s:Schema id="RowsetSchema">
> <s:ElementType name="row" content="eltOnly" rs:updatable="true">
> <s:AttributeType name="Tag" rs:number="1">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3"
> rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID"
> rs:autoincrement="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
> <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="SLSMNID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="CusID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
> </s:AttributeType>
> <s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
> </s:AttributeType>
> <s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15"
> rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c15" rs:name="P!2!active" rs:number="16"
> rs:nullable="true">
> <s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:extends type="rs:rowbase"/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286"
> c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1
> electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity
> bin, "/>
> <z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True"/>
> <z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True" c16="394" c17="2759"
> c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
> </rs:data>
> </xml>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/xml/QUOTEID')
> WITH ( QuoteID int '.',
> QuoteID int '../@. QuoteID',
> QuoteID int '../../@. QuoteID')
> EXEC sp_xml_removedocument @.idoc
> --
> Regards,
> Jamie