Hi All,
A simple DTS job I have is giving me fits. It is a straight copy column job from a pipe delimited text file into a table. The input file comes from a mapped drive linked to a shared filesystem on a sun solaris box.
The typical scenario. I run the DTS job to load 8000 rows from the input text file. Job succeeds.
A week later, the text file is updated with 9000 new rows. I run the DTS job with no changes and it loads 8000 rows from last week.
I reboot my Win XP pc and run DTS again. It now loads the 9000 new rows.
I tried mapping to a UNC to no avail.
Is it buffering the old file somewhere? I need help.
current environment:
SQL Server 2000 with all latest SP's and patches
Windows 2000 Server with all latest SP's and patches
Drive 'G' mapped to a shared filesystem on sun solaris via Samba?I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.|||I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.
Thanks Satya,
I'm not sure what you mean by "old settings"? Here's what I do know.
Drive 'G' is mapped to \\sun001\data
The input file name is weekly_updates.txt and does not change.
My DTS connection 1 properties uses
G:\weekly_updates.txt in the File Name box.
It always works correctly after a reboot, and more specifically, from any pc, not just mine. Any idea's?|||Might it be somehow related to Enterprise Manager? I wonder if there is a way to automatically force everything in EM to refresh. I dislike having to submit a job and then keep hitting refresh to see when it completes. I'm wondering if the "old" information stored in EM is related to my DTS issue?
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Thursday, March 29, 2012
Sunday, February 26, 2012
DTS mapping global variable to column
Hi
I have doubt in DTS -i want 2 export text file into Database - which i can able to do it while running DTS it will prompt inputbox to enter the datetime of transferring which i should update in the datetime column of the table,which i will store this user input value in globalvariables- so how should i map to column.
i placed text object (source) and connection object and active x script object
thanks in advance
HrMay search SQL DTS (http://www.sqldts.com) website.
I have doubt in DTS -i want 2 export text file into Database - which i can able to do it while running DTS it will prompt inputbox to enter the datetime of transferring which i should update in the datetime column of the table,which i will store this user input value in globalvariables- so how should i map to column.
i placed text object (source) and connection object and active x script object
thanks in advance
HrMay search SQL DTS (http://www.sqldts.com) website.
Friday, February 17, 2012
DTS import/export Question
When importing a flat-text file, is it possible within the import/export wizard to tie more than one column as a DTSSource?
As an example, in an online store, we have columns from the flat-file like so:
"ID","Name","Description","Rarity","Price","Image","Weight"
And in the destination database, the columns are:
"ProductID","Name","Description","Price","Image","Weight"
I'd like to do the following within the dts wizard:
'********************************************************
Function Main()
DTSDestination("ProductID") = DTSSource("id")
DTSDestination("Name") = DTSSource("name")
DTSDestination("Description") = DTSSource("description") &"<br /><br />"& DTSSource("rarity")
DTSDestination("tImage") = DTSSource("image")
DTSDestination("Price") = DTSSource("Price")
DTSDestination("Weight") = DTSSource("Weight")
Main = DTSTransformStat_OK
End Function
'********************************************************
Obviously the description line errors out. Any suggestion on how to accomplish this for an import?You could import 'rarity' as another column, then combine the two in your SELECT query:
SELECT (description + '<br /><br />' + rarity) AS description ...Or import the rarity as a column and then run SQL to combine the two
UPDATE mytable SET description = (description + '<br /><br />' + rarity)...of course you would have to provide logic to not combine previously combined columns. I realize both of these options contain reduntant data, I guess you could add a temp column, import,combine,delete temp column.
DTS import stores the last {LF} in Table.
Hi i have a csv file where the last column usually happens to be null. So my file is like this:
1,2,3,,{LF}
4,5,6,,{LF}
7,8,9,,{LF}
{CR}
16897,"Optioned",25510,65,0,,,,20060920,25510,FALSE,
16899,"Confirmed",33333,130,0,,,,20060902,33333,FALSE,
16900,"Cancelled",33333,180,0,,,,20060902,33333,FALSE,
1,2,3,,{LF}
4,5,6,,{LF}
7,8,9,,{LF}
{CR}
I run the dts export from csv file to db and it doesnt save the last row (column5) in the table us null. instead it stores the {LF} character. Ascii =10. How can i resove this problem.
the csv comes from a third party application. the DTS needs to un automatically and somehow resolve that last problem on its own. but how? is there a code that can trim the last character in the last line to delete the last {LF} or some query in DTS that can achive this.....
Could you provide an actual sample of data? I wouldn't mind taking a look at this.
-Jamie
|||16896,"Optioned",25510,65,0,,,,20060920,25510,FALSE,16897,"Optioned",25510,65,0,,,,20060920,25510,FALSE,
16899,"Confirmed",33333,130,0,,,,20060902,33333,FALSE,
16900,"Cancelled",33333,180,0,,,,20060902,33333,FALSE,
As you can see, the above has a 12th column that saves a {LF} character in the table.
Thanks for your help.|||
DaPosh wrote:
16896,"Optioned",25510,65,0,,,,20060920,25510,FALSE,
16897,"Optioned",25510,65,0,,,,20060920,25510,FALSE,
16899,"Confirmed",33333,130,0,,,,20060902,33333,FALSE,
16900,"Cancelled",33333,180,0,,,,20060902,33333,FALSE,As you can see, the above has a 12th column that saves a {LF} character in the table.
Thanks for your help.
I managed to get this working OK (I think). I copied the data above into a text file called c:\temp\source2.txt
And below is the package I built that managed to load it.
(If for some reason copying and pasting has stuffed this up a bit then please contact me via my blog (http://blogs.conchango.com/jamiethomson) and I can send you the package.)
cheers
Jamie
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">INT\Jamie.Thomson</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">CGOJTHOMSON</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">9/7/2006 11:31:49 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">1</DTS:Property><DTS:Property DTS:Name="VersionGUID">{AD8AD142-588A-47CA-A828-218DF0262CC9}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">source</DTS:Property><DTS:Property DTS:Name="DTSID">{A99C13CD-D0FA-4A6F-A4B2-545EC4FB13A5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="FileUsageType">0</DTS:Property><DTS:Property DTS:Name="Format">Delimited</DTS:Property><DTS:Property DTS:Name="LocaleID">2057</DTS:Property><DTS:Property DTS:Name="Unicode">0</DTS:Property><DTS:Property DTS:Name="HeaderRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="HeaderRowDelimiter" xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property DTS:Name="ColumnNamesInFirstDataRow">0</DTS:Property><DTS:Property DTS:Name="RowDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="DataRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="TextQualifier"><none></DTS:Property><DTS:Property DTS:Name="CodePage">1252</DTS:Property>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 0</DTS:Property><DTS:Property DTS:Name="DTSID">{4BB37FAB-6EAF-4CC7-BFC5-66892E7BF5F8}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 1</DTS:Property><DTS:Property DTS:Name="DTSID">{0B886B87-A8C1-4C64-A20B-478AECC3D1C3}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 2</DTS:Property><DTS:Property DTS:Name="DTSID">{16B09A7C-F5C6-4C47-8FA7-BCBAEAD6D5AB}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 3</DTS:Property><DTS:Property DTS:Name="DTSID">{CF8AE770-1ECB-4E8E-B40F-3344BE408761}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 4</DTS:Property><DTS:Property DTS:Name="DTSID">{0ACCB9EC-01EB-43C7-9B00-893B0E8D3E76}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 5</DTS:Property><DTS:Property DTS:Name="DTSID">{9F8128F5-DBEE-4B02-AAF9-9636E983F41E}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 6</DTS:Property><DTS:Property DTS:Name="DTSID">{ABC37C70-12C7-414A-9FAA-F7CDF1313905}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 7</DTS:Property><DTS:Property DTS:Name="DTSID">{3BB7C651-EB87-41E8-B982-4B69F460FDFA}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 8</DTS:Property><DTS:Property DTS:Name="DTSID">{60AE15F8-431A-42A1-8CAE-9ED565EDB591}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 9</DTS:Property><DTS:Property DTS:Name="DTSID">{6C970184-9853-408E-9316-60E30818969E}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 10</DTS:Property><DTS:Property DTS:Name="DTSID">{6841922F-28C5-408F-9D17-0924A368081C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>
<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 11</DTS:Property><DTS:Property DTS:Name="DTSID">{5A1327A9-CE77-4F75-BBD0-C8A1F97AD2DD}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn><DTS:Property DTS:Name="ConnectionString">C:\temp\source2.txt</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>1455</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="8" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="18521" y="11562" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Flat File Source" left="9578" top="1455" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{C2728E10-B913-4AF1-97E8-322C490562BE}/components/1" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Merges multiple datasets." left="13494" top="4471" logicalid="5" controlid="5" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{C2728E10-B913-4AF1-97E8-322C490562BE}/components/127" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="10978" top="2220" logicalid="6" controlid="6" masterid="0" hint1="0" hint2="0" width="4715" height="2751" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobj>
<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1">
<adornment percentpos="50" controlid="7" width="423" height="423" side="1" behavior="2" himetric="2884" distfromline="175" startobj="0" x="13589" y="2868" visible="1" allowoverlap="1" usepercent="1" />
</polyline>
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{C2728E10-B913-4AF1-97E8-322C490562BE}/paths/130" vartype="8" />
<property name="Virtual" value="0" vartype="11" />
<property name="VisibleAP" value="0" vartype="3" />
</ddsxmlobj>
</layoutobject>
<connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="5" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="11377" y="2619" />
<point x="11377" y="3466" />
<point x="15293" y="3466" />
<point x="15293" y="4471" />
</connector>
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" left="13589" top="2868" logicalid="7" controlid="7" masterid="6" hint1="0" hint2="0" width="423" height="423" noresize="1" nomove="1" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="0" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="0" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="00080000a7010000a7010000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="VisualizerAdornment" value="-1" vartype="11" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsDataFlowDiagram></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{C2728E10-B913-4AF1-97E8-322C490562BE}</DTS:Property><DTS:Property DTS:Name="DTSID">{492E4A24-30E2-4202-9B2D-7E8B2CC6FB50}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>Union All Input 1</dwd:DestinationName><dwd:SourceName>Flat File Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{C2728E10-B913-4AF1-97E8-322C490562BE}-130</DTS:Property><DTS:Property DTS:Name="DTSID">{C10CBBE0-58D8-4889-B051-35B2EA133B30}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="18521" y="12488" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="0" top="0" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{C2728E10-B913-4AF1-97E8-322C490562BE}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{EA47FAE1-4AA0-4C40-90A1-44D8BA2ADB2B}</DTS:Property><DTS:Property DTS:Name="DTSID">{99C4335C-C05D-4C1D-A920-A2E4444AD068}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">2057</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{C2728E10-B913-4AF1-97E8-322C490562BE}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">
<components>
<component id="1" name="Flat File Source" componentClassID="{90C7770B-DE7C-435E-880E-E718C92C0573}" description="Flat File Source" localeId="2057" usesDispositions="true" validateExternalMetadata="True" version="1" pipelineVersion="0" contactInfo="Flat File Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">
<properties>
<property id="7" name="RetainNulls" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether zero-length columns are treated as null." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</property>
<property id="8" name="FileNameColumnName" dataType="System.String" state="default" isArray="false" description="Specifies the name of an output column containing the file name. If no name is specified, no output column containing the file name will be generated." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>
<connections>
<connection id="6" name="FlatFileConnection" description="" connectionManagerID="{A99C13CD-D0FA-4A6F-A4B2-545EC4FB13A5}"/></connections>
<outputs>
<output id="2" name="Flat File Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="18" name="Column 0" description="" lineageId="18" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="17"><properties>
<property id="19" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="20" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="22" name="Column 1" description="" lineageId="22" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="21"><properties>
<property id="23" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="24" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="26" name="Column 2" description="" lineageId="26" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="25"><properties>
<property id="27" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="28" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="30" name="Column 3" description="" lineageId="30" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="29"><properties>
<property id="31" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="32" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="34" name="Column 4" description="" lineageId="34" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="33"><properties>
<property id="35" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="36" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="38" name="Column 5" description="" lineageId="38" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="37"><properties>
<property id="39" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="40" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="42" name="Column 6" description="" lineageId="42" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="41"><properties>
<property id="43" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="44" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="46" name="Column 7" description="" lineageId="46" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="45"><properties>
<property id="47" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="48" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="50" name="Column 8" description="" lineageId="50" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="49"><properties>
<property id="51" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="52" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="54" name="Column 9" description="" lineageId="54" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="53"><properties>
<property id="55" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="56" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="58" name="Column 10" description="" lineageId="58" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="57"><properties>
<property id="59" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="60" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>
<outputColumn id="62" name="Column 11" description="" lineageId="62" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="61"><properties>
<property id="63" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="64" name="UseBinaryFormat" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the data is in binary format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="17" name="Column 0" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="21" name="Column 1" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="25" name="Column 2" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="29" name="Column 3" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="33" name="Column 4" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="37" name="Column 5" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="41" name="Column 6" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="45" name="Column 7" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="49" name="Column 8" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="53" name="Column 9" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="57" name="Column 10" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>
<externalMetadataColumn id="61" name="Column 11" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/></externalMetadataColumns></output>
<output id="3" name="Flat File Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="69" name="Flat File Source Error Output Column" description="Flat File Source Error Output Column" lineageId="69" precision="0" scale="0" length="0" dataType="text" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="4" name="ErrorCode" description="" lineageId="4" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="5" name="ErrorColumn" description="" lineageId="5" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
<component id="127" name="Union All" componentClassID="{C5E47A29-265C-4F93-82CA-D8462EE23278}" description="Merges multiple datasets." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="1" pipelineVersion="0" contactInfo="Union All;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">
<inputs>
<input id="128" name="Union All Input 1" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><inputColumns>
<inputColumn id="131" name="" description="" lineageId="18" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="132" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">133</property></properties></inputColumn>
<inputColumn id="134" name="" description="" lineageId="22" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="135" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">136</property></properties></inputColumn>
<inputColumn id="137" name="" description="" lineageId="26" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="138" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">139</property></properties></inputColumn>
<inputColumn id="140" name="" description="" lineageId="30" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="141" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">142</property></properties></inputColumn>
<inputColumn id="143" name="" description="" lineageId="34" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="144" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">145</property></properties></inputColumn>
<inputColumn id="146" name="" description="" lineageId="38" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="147" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">148</property></properties></inputColumn>
<inputColumn id="149" name="" description="" lineageId="42" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="150" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">151</property></properties></inputColumn>
<inputColumn id="152" name="" description="" lineageId="46" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="153" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">154</property></properties></inputColumn>
<inputColumn id="155" name="" description="" lineageId="50" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="156" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">157</property></properties></inputColumn>
<inputColumn id="158" name="" description="" lineageId="54" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="159" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">160</property></properties></inputColumn>
<inputColumn id="161" name="" description="" lineageId="58" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="162" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">163</property></properties></inputColumn>
<inputColumn id="164" name="" description="" lineageId="62" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"><properties>
<property id="165" name="OutputColumnLineageID" dataType="System.Int32" state="default" isArray="false" description="Output column lineage ID" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">166</property></properties></inputColumn>
</inputColumns><externalMetadataColumns isUsed="False"/></input>
<input id="167" name="Union All Input 2" description="" hasSideEffects="true" dangling="true" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>
</inputs>
<outputs>
<output id="129" name="Union All Output 1" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="133" name="Column 0" description="" lineageId="133" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="136" name="Column 1" description="" lineageId="136" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="139" name="Column 2" description="" lineageId="139" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="142" name="Column 3" description="" lineageId="142" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="145" name="Column 4" description="" lineageId="145" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="148" name="Column 5" description="" lineageId="148" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="151" name="Column 6" description="" lineageId="151" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="154" name="Column 7" description="" lineageId="154" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="157" name="Column 8" description="" lineageId="157" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="160" name="Column 9" description="" lineageId="160" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="163" name="Column 10" description="" lineageId="163" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="166" name="Column 11" description="" lineageId="166" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
</components>
<paths>
<path id="130" name="Flat File Source Output" description="" startId="2" endId="128"/>
</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package4</DTS:Property><DTS:Property DTS:Name="DTSID">{EA47FAE1-4AA0-4C40-90A1-44D8BA2ADB2B}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
DTS import problem with text datatype from SQL Server to MySQL table
Can anybody tell how to import a table with the text column from SQL Server 2000 to MySQL 4.0.17?
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".Can you not export the data into a text file and import it into mysql ?|||You also can export the data into a file with .csv and import it into mysql.|||Thanks rnealejr and gyuan
Even with export to text file and then import that text file to MySQL gives the similar error -
Error Source: Microsoft OLE DB provider for ODBC drivers
Error Description: The number of failing rows exceeds the maximum specified.Query-based Insertion or updating of BLOB values is not supported.
I have also tried MySQL connection with mysql-oledb-3.0.0 but it seems it has some problems as it swaps the database name and server name parameters each time I open for edit or test connections.|||Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/|||Originally posted by Brett Kaiser
Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/
Thanks
I have checked http://www.dbforums.com/f5 and found a similar posting but no useful replies.
How to import a SQL Server table with text column to a similar table in MySQL database?|||What type of cursor are you using ?|||All DTS defaults. I am using DTS package tasks and connection objects with their defaults.|||Has anybody transfered the data from SQL Server table having text column to MySQL database?|||I understand this could be a ODBC problem of handling the text data but
When I use "MySQL.OLEDB Provider" instead of "MySQL ODBC 3.1 Driver" to make a connection with MySQL in DTS package and setup a Transform Data Task between SQL Server(source connection) and MySQL(destination connection) I see all the MySQL tables appended with "INFORMATION_SCHEMA." in the dropdown list of destination table names in Transform Data Task Properties whereas there is no user or database named INFORMATION_SCHEMA in MySQL database.
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".Can you not export the data into a text file and import it into mysql ?|||You also can export the data into a file with .csv and import it into mysql.|||Thanks rnealejr and gyuan
Even with export to text file and then import that text file to MySQL gives the similar error -
Error Source: Microsoft OLE DB provider for ODBC drivers
Error Description: The number of failing rows exceeds the maximum specified.Query-based Insertion or updating of BLOB values is not supported.
I have also tried MySQL connection with mysql-oledb-3.0.0 but it seems it has some problems as it swaps the database name and server name parameters each time I open for edit or test connections.|||Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/|||Originally posted by Brett Kaiser
Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/
Thanks
I have checked http://www.dbforums.com/f5 and found a similar posting but no useful replies.
How to import a SQL Server table with text column to a similar table in MySQL database?|||What type of cursor are you using ?|||All DTS defaults. I am using DTS package tasks and connection objects with their defaults.|||Has anybody transfered the data from SQL Server table having text column to MySQL database?|||I understand this could be a ODBC problem of handling the text data but
When I use "MySQL.OLEDB Provider" instead of "MySQL ODBC 3.1 Driver" to make a connection with MySQL in DTS package and setup a Transform Data Task between SQL Server(source connection) and MySQL(destination connection) I see all the MySQL tables appended with "INFORMATION_SCHEMA." in the dropdown list of destination table names in Transform Data Task Properties whereas there is no user or database named INFORMATION_SCHEMA in MySQL database.
Tuesday, February 14, 2012
DTS Import data problem (exel into SQL server 2000)
Hi all,
I face a small problem in DTS, hope you can help me to solve this issue.
I designed a DTS package to import excel data ( with 4 column ) into SQL server database,The database design for the database table was
column name type length
ID bigint 8
CountedQty decimal 9
Location nvarchar 8
Other nvarchar 50
The DTS package is work and does not appear any error message. But the data in column 'Location' appeared <Null> after import into the database, this issue happen when the excel data was <numeric> value (string value in this data column can be accepted and appear nicely).
Izzit because nvarchar cannot accept numeric value, how can i solve this problem ?
Thank you very much.
flreStarter
hi,
are you place up a log file for that DTS? Even in the own pump task you can enable a specific log in order to obtain further details.
DTS Import data problem ( excel into database )
Hi all,
I face a small problem in DTS, hope you can help me to solve this issue.
I designed a DTS package to import excel data ( with 4 column ) into SQL server database,The database design for the database table was
column name type length
ID bigint 8
CountedQty decimal 9
Location nvarchar 8
Other nvarchar 50
The DTS package is work and does not appear any error message. But the data in column 'Location' appeared <Null> after import into the database, this issue happen when the excel data was <numeric> value (string value in this data column can be accepted and appear nicely).
Izzit because nvarchar cannot accept numeric value ?
flreStarter
Excel assigns a native data type to each column as it imports. If your first 8 rows are blank or alpha then it will not be considered numeric. Numeric field values, as opposed to numeric characters in a text field, will import as NULL.
Microsoft Article 189897 and Microsoft article 194124 explain the problem. You can set the number of "guess type" rows to a higher number or, if you want all rows checked, change it to 0. See 189897 for a description of how to do this.
Edit: To get this to work from SQL DTS (as I've just discovered by trying it) you have to edit the extended OLE DB connection properties as well as editing the registry entry for TypeGuessRows.
1) Open up your saved DTS package in the designer.
2) Right click on an empty spot in the designer window and choose "Disconnected Edit".
3) Expand the connection for each Excel connection and under OLE DB Properties choose "Extended Properties".
4) Edit the string "value" and add ;IMEX=1 to the connection properties. It'll look something like "Excel 8.0;HDR=YES;IMEX=1"
5) Save your package and rerun it.
There are other ways if you really don't want to change the registry.
If you really just have numeric data, and no alpha characters at all, in the problem column then just put a zero in the first row of data for the column.
If you have mixed data then this works for me:
1) Create two new columns to the left of the problem column
2) In the first column make a formula to concatenate an empty string to the column value. This forces it to be a text value. For example: if your problem column was B and your first data row was 2 then you'd put this in: =concatenate(B2,"")
3) Paste your formula down the entire new column all the way to the last row. The new column should look identical to the first.
4) Copy the first new column and past it into a new empty column with "paste special" and choose values only.
5) Delete the original column and the column with the concatenate formula in it.
|||
Hi Wysiwyg,
Thank you for you reply,
The 1st method that you provide ( editing OLE database connection ) is work, but those imported data will store as SCIENTIFIC format in database.
For example, '240' (numeric in excel file) will represent '2.40E+02' (scientific value) when the data transferred.
I know '2.40E+02' scientific value is because i copy the data from database, then i paste it into the excel file, then right click --> 'format cells', it represent as 'scientific'.
How can we the right data import data (integer taype) rather than scientific format For method two that u provided was not suit for our scenario, because we cannot modify the excel file, therefore we cannot use 'create new column' soluation.
Regrds.
|||I've never had the problem of imported data showing up as scientific values. What is the datatype you are importing into? Did you format the column as numeric with the decimal places specified? When you copy value you'll want the column formatted correctly.
edit: I was able to reproduce it by importing a mixed column into a varchar field; in my case fax numbers some of which had no dashes.
I was able to get around this by copying the columns, as described in my first post, and pasting the values as a text field. If you are importing into a numeric field then you probably have non-numeric values in the column somewhere.
|||There are a number of causes of unexpected numeric formats in Excel. Check out the KB article on the subject:
214233 Text or number converted to unintended number format in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;214233
Mike
|||I am attempting to get to this point and import Excel however I cannot seem to get DTS (Import) function to show up on the context menu... Please help
|||we load operating budget twice a year in an excel format to the database and eveyrtime we spend way too much time trying to resolve issues related to format and nulls
even product code being truncated because they start with zeros. I realized that struggling with cached format and format defect while not have control over was a waste of
time.
The perfect solution was to change the format that the the data is uploaded from. instead of using an excel sheet we used coma separated value file.
This is so great because all the problematic excel format got lost when we saved the file to csv. event the data scope that was to big and causing the null we
got ride of.
basically in dts package used a text file (source) and specified properties:
row delimiter (CR LF) no text delim
DTS Import data problem ( excel into database )
Hi all,
I face a small problem in DTS, hope you can help me to solve this issue.
I designed a DTS package to import excel data ( with 4 column ) into SQL server database,The database design for the database table was
column name type length
ID bigint 8
CountedQty decimal 9
Location nvarchar 8
Other nvarchar 50
The DTS package is work and does not appear any error message. But the data in column 'Location' appeared <Null> after import into the database, this issue happen when the excel data was <numeric> value (string value in this data column can be accepted and appear nicely).
Izzit because nvarchar cannot accept numeric value ?
flreStarter
Excel assigns a native data type to each column as it imports. If your first 8 rows are blank or alpha then it will not be considered numeric. Numeric field values, as opposed to numeric characters in a text field, will import as NULL.
Microsoft Article 189897 and Microsoft article 194124 explain the problem. You can set the number of "guess type" rows to a higher number or, if you want all rows checked, change it to 0. See 189897 for a description of how to do this.
Edit: To get this to work from SQL DTS (as I've just discovered by trying it) you have to edit the extended OLE DB connection properties as well as editing the registry entry for TypeGuessRows.
1) Open up your saved DTS package in the designer.
2) Right click on an empty spot in the designer window and choose "Disconnected Edit".
3) Expand the connection for each Excel connection and under OLE DB Properties choose "Extended Properties".
4) Edit the string "value" and add ;IMEX=1 to the connection properties. It'll look something like "Excel 8.0;HDR=YES;IMEX=1"
5) Save your package and rerun it.
There are other ways if you really don't want to change the registry.
If you really just have numeric data, and no alpha characters at all, in the problem column then just put a zero in the first row of data for the column.
If you have mixed data then this works for me:
1) Create two new columns to the left of the problem column
2) In the first column make a formula to concatenate an empty string to the column value. This forces it to be a text value. For example: if your problem column was B and your first data row was 2 then you'd put this in: =concatenate(B2,"")
3) Paste your formula down the entire new column all the way to the last row. The new column should look identical to the first.
4) Copy the first new column and past it into a new empty column with "paste special" and choose values only.
5) Delete the original column and the column with the concatenate formula in it.
|||
Hi Wysiwyg,
Thank you for you reply,
The 1st method that you provide ( editing OLE database connection ) is work, but those imported data will store as SCIENTIFIC format in database.
For example, '240' (numeric in excel file) will represent '2.40E+02' (scientific value) when the data transferred.
I know '2.40E+02' scientific value is because i copy the data from database, then i paste it into the excel file, then right click --> 'format cells', it represent as 'scientific'.
How can we the right data import data (integer taype) rather than scientific format For method two that u provided was not suit for our scenario, because we cannot modify the excel file, therefore we cannot use 'create new column' soluation.
Regrds.
|||I've never had the problem of imported data showing up as scientific values. What is the datatype you are importing into? Did you format the column as numeric with the decimal places specified? When you copy value you'll want the column formatted correctly.
edit: I was able to reproduce it by importing a mixed column into a varchar field; in my case fax numbers some of which had no dashes.
I was able to get around this by copying the columns, as described in my first post, and pasting the values as a text field. If you are importing into a numeric field then you probably have non-numeric values in the column somewhere.
|||There are a number of causes of unexpected numeric formats in Excel. Check out the KB article on the subject:
214233 Text or number converted to unintended number format in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;214233
Mike
|||I am attempting to get to this point and import Excel however I cannot seem to get DTS (Import) function to show up on the context menu... Please help
|||we load operating budget twice a year in an excel format to the database and eveyrtime we spend way too much time trying to resolve issues related to format and nulls
even product code being truncated because they start with zeros. I realized that struggling with cached format and format defect while not have control over was a waste of
time.
The perfect solution was to change the format that the the data is uploaded from. instead of using an excel sheet we used coma separated value file.
This is so great because all the problematic excel format got lost when we saved the file to csv. event the data scope that was to big and causing the null we
got ride of.
basically in dts package used a text file (source) and specified properties:
row delimiter (CR LF) no text delim
DTS Import data problem ( excel into database )
Hi all,
I face a small problem in DTS, hope you can help me to solve this issue.
I designed a DTS package to import excel data ( with 4 column ) into SQL server database,The database design for the database table was
column name type length
ID bigint 8
CountedQty decimal 9
Location nvarchar 8
Other nvarchar 50
The DTS package is work and does not appear any error message. But the data in column 'Location' appeared <Null> after import into the database, this issue happen when the excel data was <numeric> value (string value in this data column can be accepted and appear nicely).
Izzit because nvarchar cannot accept numeric value ?
flreStarter
Excel assigns a native data type to each column as it imports. If your first 8 rows are blank or alpha then it will not be considered numeric. Numeric field values, as opposed to numeric characters in a text field, will import as NULL.
Microsoft Article 189897 and Microsoft article 194124 explain the problem. You can set the number of "guess type" rows to a higher number or, if you want all rows checked, change it to 0. See 189897 for a description of how to do this.
Edit: To get this to work from SQL DTS (as I've just discovered by trying it) you have to edit the extended OLE DB connection properties as well as editing the registry entry for TypeGuessRows.
1) Open up your saved DTS package in the designer.
2) Right click on an empty spot in the designer window and choose "Disconnected Edit".
3) Expand the connection for each Excel connection and under OLE DB Properties choose "Extended Properties".
4) Edit the string "value" and add ;IMEX=1 to the connection properties. It'll look something like "Excel 8.0;HDR=YES;IMEX=1"
5) Save your package and rerun it.
There are other ways if you really don't want to change the registry.
If you really just have numeric data, and no alpha characters at all, in the problem column then just put a zero in the first row of data for the column.
If you have mixed data then this works for me:
1) Create two new columns to the left of the problem column
2) In the first column make a formula to concatenate an empty string to the column value. This forces it to be a text value. For example: if your problem column was B and your first data row was 2 then you'd put this in: =concatenate(B2,"")
3) Paste your formula down the entire new column all the way to the last row. The new column should look identical to the first.
4) Copy the first new column and past it into a new empty column with "paste special" and choose values only.
5) Delete the original column and the column with the concatenate formula in it.
|||
Hi Wysiwyg,
Thank you for you reply,
The 1st method that you provide ( editing OLE database connection ) is work, but those imported data will store as SCIENTIFIC format in database.
For example, '240' (numeric in excel file) will represent '2.40E+02' (scientific value) when the data transferred.
I know '2.40E+02' scientific value is because i copy the data from database, then i paste it into the excel file, then right click --> 'format cells', it represent as 'scientific'.
How can we the right data import data (integer taype) rather than scientific format For method two that u provided was not suit for our scenario, because we cannot modify the excel file, therefore we cannot use 'create new column' soluation.
Regrds.
|||I've never had the problem of imported data showing up as scientific values. What is the datatype you are importing into? Did you format the column as numeric with the decimal places specified? When you copy value you'll want the column formatted correctly.
edit: I was able to reproduce it by importing a mixed column into a varchar field; in my case fax numbers some of which had no dashes.
I was able to get around this by copying the columns, as described in my first post, and pasting the values as a text field. If you are importing into a numeric field then you probably have non-numeric values in the column somewhere.
|||There are a number of causes of unexpected numeric formats in Excel. Check out the KB article on the subject:
214233 Text or number converted to unintended number format in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;214233
Mike
|||I am attempting to get to this point and import Excel however I cannot seem to get DTS (Import) function to show up on the context menu... Please help|||we load operating budget twice a year in an excel format to the database and eveyrtime we spend way too much time trying to resolve issues related to format and nulls
even product code being truncated because they start with zeros. I realized that struggling with cached format and format defect while not have control over was a waste of
time.
The perfect solution was to change the format that the the data is uploaded from. instead of using an excel sheet we used coma separated value file.
This is so great because all the problematic excel format got lost when we saved the file to csv. event the data scope that was to big and causing the null we
got ride of.
basically in dts package used a text file (source) and specified properties:
row delimiter (CR LF) no text delim
Subscribe to:
Posts (Atom)