I have run into a HUGE problem (possibly a BUG)! I have a custom XML Destination script which uses a FILE Conn to send to another path using the original file name. (found in my foreach container (varFileNm)
The problem is that my destination will not use the value found for the varFileNm. I can see the value just fine in the Flat File Receive, but I can't see the value in my custom XML Destination Script using a File Conn (also tried a Flat File Conn for laughs and giggles)
In additon, I have tried several other ways to set the varFileNm to some value (both dynamc & statically) through scripts in both the control flow and the data flow transforms/destinations but I CANNOT GET the value. If I statically type in the value in the variables pane, then the value shows up fine in my destination name.
Is this a bug If so, is there going to be a fix We desperatelly need assistance on this ASAP, for we have to use the original file name in our Destination.
P.S I have tried many techniques and it only appears that this is a problem when using a Script Destination... doesn't allow one to use variables that have been programmtically (other means outside of just labeling in variables pane) set in current container/package. I CAN however access the variables values that were set in a parent container/package!!
Variables Not Seen In Custom DataFlow Destination
Hot Topic
- Consuming Multiple Messages In Parallel from Multiple Windows Services
- Execute SSIS package from asp.Net
- Cube DDL reverse engineer ?
- What should the maintenance plans be on your mirror server?
- Qualifier in URL Access not working
- How to configure distributor thread for being up while replicated database is down
- can't install sample databases plus no documents or tutorials
- Migrating SqlServer2000->2005 and classic ADO: problem with AddNew to recordset, related to adFldUnknownUpdatable attribute?
- Can't Access Cube Templates
- TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host
Variables Not Seen In Custom DataFlow Destination
TheLastVampire
I'm sure this IS a bug. Do you want to verify it or should Jason just raise it on betaplace (which doesn't seem to be getting actioned of late)
-Jamie
Brian Trexler
This is a hard problem to describe. Maybe I will try and illustrate this with a little less detail.
Overall, I have one data-flow that uses a FFSource / FFConnection to receive a delimited file: "D:\\InDir\TestFile.txt".
Then I have a ScriptComponent Destination that will build custom XML format and send through a FileConnection. In my FileConnection (xmldestination), I set the ConnectionString in an expr: "D:\\OutDir\\TestFile.xml"
At this point all works fine!
Now, let's say that my outbound file ("TestFile") is not statically set in my expr, it is a variable I will use (VariableOutFile). So now my ConnectionString looks like "D:\\OutDir\\"+VariableOutFile+".xml"
The problem now comes when I try and set this VariableOutFile value in my package. (tried doing in control-flow script, and tried in data-flow as derived col, destination script, etc..)
The output in my ConnectionString expr is always EMPTY, and looks like "D:\OutDir\.xml" where my file is named ".xml" only. but, when I set the VariableOutFile value to "StaticTestFile" statically in my variables pane, I will now get "D:\OutDir\StaticTestFile.xml". In additon, if I use a variable that has been set outside of my package (set in my parent package) I can see the value.
Just incase there is still confusion, I will post my code. Do you need the entire package
chrisSB
Thanks Doug for replying, I must apologize for not clearly stating the issue...
Yes, I have a Script component configured as a destination and I want to set the ConnectionString expression to a path + varFileNm. (which is the file_name variable defined in my foreach container as index 0) The problem is that the varFileNm value is never displayed in my ConnectionString.
So let's say that I have a foreach container that check for *.txt, then I have a FFSource that listens for varFileNm, (fnds 1 file, “file001.txt” for this example) finally, I use a Script Destination to write out a OutboundXML file to different location (other then FFSource) with the same varFileNm. (file001.txt.xml)
When I use the varFileNm, I cannot get the value in my ConnectionString of my FileConnection, it is just blank. But, what is weird is that I can get varFileNm value fine in my FileConn ConnectionString when I use a Source component or Destination Component other then ScriptBased.
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
As I have said, I have tried several other methods and different variables and none of them show a value in my ConnectionString of my Script Destination Component (FileConnection) I can however msgbox display the variables values just fine in the actual destination component script.
It appears that the ConectionString expr (when used in conjunction with a ScriptDestination component) will not display the values of any variable defined in that specific package unless you statically set the value in the variables pane...
Make sense Ideas
sdelmas
joesun99
1) varFileName contains the value "Pre"
2) varFileName is changed to have the value "Post" in a script task. [I have verified that this step does work by, in a subsequent script task, outputting varFileName="Post" in a msgbox.]
3) FileConnection (the file [note not Flatfile]connection manager) has an expression on the ConnectionString property: "D:\\"+ @[User::varFileName] +".xml"
4) This file connection is used to define the name of the output file used in the data-flow. The data-flow is executed AFTER varFileName is set to "Post"
You would expect the output file to be called "D:\Post.xml" right WRONG. Its called "D:\Pre.xml".
Why is this My best guess is that the property expression on FileConnection is evaluated prior to varFileName being given the new value. Why this should be the case though I do not know - surely the property expression should be evaluated when the connection manager is used
Comments
-Jamie
Greg Banister
-Jamie
VikasVaidya
Yeah, its just an XML document right So you can easily post it up here.
NUCLEAR-WAR
Jason, are you able to put this up on betaplace
-Jamie
TheDOG
The Script component only creates a strongly-typed, named accessor property for those variables that you list in the Script Transformation Editor as ReadWriteVariables and ReadOnlyVariables. These are the variables that you can access as Me.Variables.<variablename>.
However, this is not the only means to access variables. The component's object model also has a VariableDispenser property (Me.VariableDispenser should work) that returns an IDtsVariableDispenser90 that you can use to work more directly with package variables. Note that in this case you need to handle the locking semantics on your own. Please see IDtsVariableDispenser90 in the API reference for more information on its methods and properties.
-Doug
p4r1tyb1t
Here is a simplified version of what I am trying to do.
1. contains one variable varFileName (which is statically set to StaticfileName in var pane)
2. Has one scirpt at beg of control package which sets varfileName="DynamicFileName"
3. One data-flow
3a. Inside DF, have one FFSource which listens for fixed-length file set in expr ConnectionString
3b. Sends to one ScriptComponent Destination which converts to FF-XML and uses a expr to set ConnectionString to "D:\\OutDir\\"+ @[User::varFileName] +".xml"
the variable varFileNm gets set (or trys to anyways) in three different locations.
1. In variable pane as StaticFileName
2. In control flow script as DynamicFileName
3. In data flow script destination as DynamicDFFile
But as you can see it will always display "StaticFileName" or EMPTY if you remvoe the value form vars pane, and NEVER get set anywhere outside of vars pane.
----------------File: IM_Dim_Market 1.dtsx------------------
< 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">VERISYNC\jworkman</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">PROD17</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">8/1/2005 4:18:25 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">283</DTS:Property><DTS:Property DTS:Name="VersionGUID">{DD4A9F5D-70DB-4298-AD82-B7CDE2459542}</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="ObjectName">010FLFF Connection</DTS:Property><DTS:Property DTS:Name="DTSID">{286D36DB-CAC1-4A1B-AED0-573BF2EEC2E2}</DTS:Property><DTS:Property DTS:Name="Description">010 fixed length flat file connection</DTS:Property><DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property><DTS:PropertyExpression DTS:Name="ConnectionString">"D:\\InDir\\010_ACOT_TestCompany_08032004172800441.txt"</DTS:PropertyExpression><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="FileUsageType">0</DTS:Property><DTS:Property DTS:Name="Format">RaggedRight</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</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">_x000D__x000A_</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">FixedWidth</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="ColumnWidth">15</DTS:Property><DTS:Property DTS:Name="MaximumWidth">15</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">CLIENT</DTS:Property><DTS:Property DTS:Name="DTSID">{2C9B50BE-4EF2-4963-8158-C97F7A09C6FE}</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">FixedWidth</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="ColumnWidth">3</DTS:Property><DTS:Property DTS:Name="MaximumWidth">3</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">COUNTRY</DTS:Property><DTS:Property DTS:Name="DTSID">{E6992B77-A85C-4981-80D4-36B2E9FC7A8B}</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">FixedWidth</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="ColumnWidth">15</DTS:Property><DTS:Property DTS:Name="MaximumWidth">15</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">CODE</DTS:Property><DTS:Property DTS:Name="DTSID">{B269ECB8-1CEE-4EBE-88FB-F4BDF81D1D36}</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">FixedWidth</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="ColumnWidth">10</DTS:Property><DTS:Property DTS:Name="MaximumWidth">10</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ABBR</DTS:Property><DTS:Property DTS:Name="DTSID">{10D2B6FE-6682-4631-B7E9-49C4BC704E4D}</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">FixedWidth</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="ColumnWidth">35</DTS:Property><DTS:Property DTS:Name="MaximumWidth">35</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAME</DTS:Property><DTS:Property DTS:Name="DTSID">{4FB40CDB-6AC8-4911-B534-B97D6154FB5C}</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">FixedWidth</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="ColumnWidth">15</DTS:Property><DTS:Property DTS:Name="MaximumWidth">15</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ACCTCODE</DTS:Property><DTS:Property DTS:Name="DTSID">{5E280FAB-BF14-4FC8-862D-410453A239E1}</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">1</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">0</DTS:Property><DTS:Property DTS:Name="ObjectName">DELETED</DTS:Property><DTS:Property DTS:Name="DTSID">{7D559C79-01B5-4AA5-BE42-8E74B1CBD52F}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn><DTS:Property DTS:Name="ConnectionString">D:\InDir\010_ACOT_TestCompany_08032004172800441.txt</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:ConnectionManager><DTS:Property DTS:Name="ObjectName">010XML Connection</DTS:Property><DTS:Property DTS:Name="DTSID">{A2C705AA-0962-4D04-B1A1-1F5CD88583C5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">FILE</DTS:Property><DTS:PropertyExpression DTS:Name="ConnectionString">"D:\\OutDir\\"+ @[User::varFileName] +".xml"</DTS:PropertyExpression><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="FileUsageType">1</DTS:Property><DTS:Property DTS:Name="ConnectionString">D:\OutDir\StaticFileName.xml</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:BoundingLeft>1000</dwd:BoundingLeft><dwd:BoundingTop>1000</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="29" 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="14023" y="13467" 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="01000000900144420100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Flat File Source" left="2223" top="3016" logicalid="11" controlid="1" 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="{3299DEFC-34A8-4474-8A7F-695E3097D527}/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="Hosts user-defined data transformations, source and destination adapters" left="2309" top="10456" logicalid="12" controlid="2" 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="{3299DEFC-34A8-4474-8A7F-695E3097D527}/components/1288" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="3073" top="3781" logicalid="19" controlid="9" masterid="0" hint1="0" hint2="0" width="1435" height="7175" 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="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{3299DEFC-34A8-4474-8A7F-695E3097D527}/paths/1631" 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="1" destid="2" sourceattachpoint="5" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="3472" y="4180" />
<point x="3472" y="8887" />
<point x="4108" y="8887" />
<point x="4108" y="10456" />
</connector>
</ddscontrol>
</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">185 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1433 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">741 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">741 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1433 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{3299DEFC-34A8-4474-8A7F-695E3097D527}</DTS:Property><DTS:Property DTS:Name="DTSID">{3D82B381-B6A3-4312-B803-ED8794B507FA}</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:PathAnnotation>AsNeeded</dwd:PathAnnotation><dwd:DestinationName>ACCOTInput</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">{3299DEFC-34A8-4474-8A7F-695E3097D527}-1631</DTS:Property><DTS:Property DTS:Name="DTSID">{369BA41F-3C41-4133-8AD3-273C7D97C875}</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:BoundingLeft>1000</dwd:BoundingLeft><dwd:BoundingTop>-2211</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="13" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="-3164" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="14023" y="14393" 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="2629" top="418" logicalid="6" controlid="2" 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="{3299DEFC-34A8-4474-8A7F-695E3097D527}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Script Task" left="3228" top="-2211" logicalid="11" controlid="7" 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="{9DE5FEE4-74D0-47BD-8406-D5A9DA26242E}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="4029" top="-1446" logicalid="12" controlid="8" masterid="0" hint1="0" hint2="0" width="1398" height="2364" 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" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{FA91537B-3A25-42F5-8AF3-AA789A6BD8FC}" 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="7" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="5027" y="-1047" />
<point x="5027" y="-314" />
<point x="4428" y="-314" />
<point x="4428" y="418" />
</connector>
</ddscontrol>
</dds></dwd:Layout><dwd:PersistedViewPortTop>-3164</dwd:PersistedViewPortTop></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{BCCF6AF5-31B1-4083-8D8E-2278157B0F26}</DTS:Property><DTS:Property DTS:Name="DTSID">{21E7F42C-9D82-435B-8100-D680266AC02F}</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"><DtsEventHandler 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:DtsEventHandlerDiagram><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="3" 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="19659" y="10848" 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>
</dds></dwd:Layout></dwd:DtsEventHandlerDiagram></DtsEventHandler></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{CCE3D329-2577-42D4-8B49-B5D14E87761E}</DTS:Property><DTS:Property DTS:Name="DTSID">{E81AFE33-29C5-467F-82CB-5445FD45499B}</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">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8">StaticFileName</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">varFileName</DTS:Property><DTS:Property DTS:Name="DTSID">{6E372EDD-DD2C-4DC8-8BA7-6C1345435057}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<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="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><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">Script Task</DTS:Property><DTS:Property DTS:Name="DTSID">{9DE5FEE4-74D0-47BD-8406-D5A9DA26242E}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_cff6c02316354b0684756de1aa7ef481" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables="varFileName"><ProjectItem Name="dts://Scripts/ScriptTask_cff6c02316354b0684756de1aa7ef481/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dts.Variables("varFileName").Value = "DynamicFileName" 'set the var file name in control flow script
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_cff6c02316354b0684756de1aa7ef481/ScriptTask_cff6c02316354b0684756de1aa7ef481.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50215.44"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50215"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_cff6c02316354b0684756de1aa7ef481"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_cff6c02316354b0684756de1aa7ef481"
ReferencePath = "C:\WINNT\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\WINNT\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_cff6c02316354b0684756de1aa7ef481"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</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:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8"></DTS:VariableValue><DTS:Property DTS:Name="ObjectName">sndFileNm</DTS:Property><DTS:Property DTS:Name="DTSID">{DAA8E8CD-B2C2-47E0-92A6-ED8E18318AB8}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<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">DFT 010FLFF</DTS:Property><DTS:Property DTS:Name="DTSID">{3299DEFC-34A8-4474-8A7F-695E3097D527}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</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="DAS 010FLFF" componentClassID="{90C7770B-DE7C-435E-880E-E718C92C0573}" description="Flat File Source" localeId="1033" 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">false</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">recFileNm</property></properties>
<connections>
<connection id="6" name="FlatFileConnection" description="" connectionManagerID="{286D36DB-CAC1-4A1B-AED0-573BF2EEC2E2}"/></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="26" name="CLIENT" description="" lineageId="26" precision="0" scale="0" length="15" 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></properties></outputColumn>
<outputColumn id="29" name="COUNTRY" description="" lineageId="29" precision="0" scale="0" length="3" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="28"><properties>
<property id="30" 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></properties></outputColumn>
<outputColumn id="32" name="CODE" description="" lineageId="32" precision="0" scale="0" length="15" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="31"><properties>
<property id="33" 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></properties></outputColumn>
<outputColumn id="35" name="ABBR" description="" lineageId="35" precision="0" scale="0" length="10" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="34"><properties>
<property id="36" 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></properties></outputColumn>
<outputColumn id="38" name="NAME" description="" lineageId="38" precision="0" scale="0" length="35" 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></properties></outputColumn>
<outputColumn id="41" name="ACCTCODE" description="" lineageId="41" precision="0" scale="0" length="15" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="40"><properties>
<property id="42" 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></properties></outputColumn>
<outputColumn id="44" name="DELETED" description="" lineageId="44" precision="0" scale="0" length="1" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="43"><properties>
<property id="45" 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></properties></outputColumn>
<outputColumn id="183" name="recFileNm" description="" lineageId="183" precision="0" scale="0" length="260" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="182"><properties>
<property id="184" 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></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="25" name="CLIENT" description="" precision="0" scale="0" length="15" dataType="str" codePage="1252"/>
<externalMetadataColumn id="28" name="COUNTRY" description="" precision="0" scale="0" length="3" dataType="str" codePage="1252"/>
<externalMetadataColumn id="31" name="CODE" description="" precision="0" scale="0" length="15" dataType="str" codePage="1252"/>
<externalMetadataColumn id="34" name="ABBR" description="" precision="0" scale="0" length="10" dataType="str" codePage="1252"/>
<externalMetadataColumn id="37" name="NAME" description="" precision="0" scale="0" length="35" dataType="str" codePage="1252"/>
<externalMetadataColumn id="40" name="ACCTCODE" description="" precision="0" scale="0" length="15" dataType="str" codePage="1252"/>
<externalMetadataColumn id="43" name="DELETED" description="" precision="0" scale="0" length="1" dataType="str" codePage="1252"/>
<externalMetadataColumn id="182" name="recFileNm" description="" precision="0" scale="0" length="260" dataType="wstr" codePage="0"/></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="46" name="Flat File Source Error Output Column" description="Flat File Source Error Output Column" lineageId="46" 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"/>
<outputColumn id="181" name="recFileNm" description="" lineageId="181" precision="0" scale="0" length="260" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
<component id="1288" name="DAD 010XML" componentClassID="{BF01D463-7089-41EE-8F05-0A6DC17CE633}" description="Hosts user-defined data transformations, source and destination adapters" localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Hosts user-defined data transformations, source and destination adapters;Microsoft Corporation;Microsoft SqlServer v9; Ac 2004 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">
<properties>
<property id="1292" name="SourceCode" dataType="System.String" state="cdata" isArray="true" description="Stores the source code of the component" typeConverter="" UITypeEditor="" containsID="false" expressionType="None"><arrayElements arrayElementCount="4"><arrayElement dataType="System.String"><![CDATA[dts://Scripts/ScriptComponent_13227a814b144ea8b275ae05d8812615/ScriptComponent_13227a814b144ea8b275ae05d8812615.vsaproj]]></arrayElement><arrayElement dataType="System.String"><![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50215.44"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50215"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptComponent_13227a814b144ea8b275ae05d8812615"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptComponent_13227a814b144ea8b275ae05d8812615"
ReferencePath = "C:\WINNT\assembly\GAC_MSIL\Microsoft.SqlServer.TxScript\9.0.242.0__89845dcd8080cc91\;C:\WINNT\assembly\GAC_MSIL\Microsoft.SqlServer.PipelineHost\9.0.242.0__89845dcd8080cc91\;C:\WINNT\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap\9.0.242.0__89845dcd8080cc91\;C:\WINNT\assembly\GAC_MSIL\Microsoft.SqlServer.DTSRuntimeWrap\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptComponent_13227a814b144ea8b275ae05d8812615"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.TxScript"
AssemblyName = "Microsoft.SqlServer.TxScript"
/>
<Reference
Name = "Microsoft.SqlServer.PipelineHost"
AssemblyName = "Microsoft.SqlServer.PipelineHost"
/>
<Reference
Name = "Microsoft.SqlServer.DTSPipelineWrap"
AssemblyName = "Microsoft.SqlServer.DTSPipelineWrap"
/>
<Reference
Name = "Microsoft.SqlServer.DTSRuntimeWrap"
AssemblyName = "Microsoft.SqlServer.DTSRuntimeWrap"
/>
<Reference
Name = "System.XML"
AssemblyName = "System.Xml"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<File
RelPath = "BufferWrapper"
BuildAction = "Compile"
ItemType = "2"
/>
<File
RelPath = "ComponentWrapper"
BuildAction = "Compile"
ItemType = "2"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>
]]></arrayElement><arrayElement dataType="System.String"><![CDATA[dts://Scripts/ScriptComponent_13227a814b144ea8b275ae05d8812615/ScriptMain.vsaitem]]></arrayElement><arrayElement dataType="System.String"><![CDATA[' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Dim xWriter As XmlTextWriter
Public Overrides Sub PreExecute()
xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteStartElement("document")
xWriter.WriteAttributeString("name", "syxAC_BO")
xWriter.WriteStartElement("tables")
xWriter.WriteStartElement("ACCOT_INPUT")
xWriter.WriteStartElement("rows")
End Sub
Public Overrides Sub ACCOTInput_ProcessInputRow(ByVal Row As ACCOTInputBuffer)
xWriter.WriteStartElement("row")
xWriter.WriteStartElement("CODE")
xWriter.WriteString(Row.CODE.Trim)
xWriter.WriteEndElement()
xWriter.WriteStartElement("ABBR")
xWriter.WriteString(Row.ABBR.Trim)
xWriter.WriteEndElement()
xWriter.WriteStartElement("NAME")
xWriter.WriteString(Row.NAME.Trim)
xWriter.WriteEndElement()
xWriter.WriteStartElement("ACCTCODE")
xWriter.WriteString(Row.ACCTCODE.Trim)
xWriter.WriteEndElement()
xWriter.WriteStartElement("SAPDELETED")
xWriter.WriteString(Row.DELETED.Trim)
xWriter.WriteEndElement()
xWriter.WriteEndElement()
End Sub
Public Overrides Sub PostExecute()
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
Me.Variables.varFileName = "DynamicDFFile" 'set the var file name in data flow script
End Sub
End Class
]]></arrayElement></arrayElements></property>
<property id="1293" name="BinaryCode" dataType="System.String" state="escaped" isArray="true" description="Stores the binary representation of the component" typeConverter="" UITypeEditor="" containsID="false" expressionType="None"><arrayElements arrayElementCount="0"/></property>
<property id="1294" name="VsaProjectName" dataType="System.String" state="default" isArray="false" description="Specifies the name of the Microsoft Visual Studio for Applications project. Project names must be unique within a package." typeConverter="NOTBROWSABLE" UITypeEditor="" containsID="false" expressionType="None">ScriptComponent_13227a814b144ea8b275ae05d8812615</property>
<property id="1295" name="PreCompile" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether to store pre-compiled binary representation of the script. This makes the package larger, but offers faster start-up time. Required to execute in native 64-bit mode." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="1296" name="ReadOnlyVariables" dataType="System.String" state="default" isArray="false" description="Comma separated list of variables to be made available for read access" typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="1297" name="ReadWriteVariables" dataType="System.String" state="default" isArray="false" description="Comma separated list of variables to be made available for read and write access" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">varFileName</property>
<property id="1298" name="UserComponentTypeName" dataType="System.String" state="default" isArray="false" description="" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</property></properties>
<connections>
<connection id="1316" name="XMLConnection" description="" connectionManagerID="{A2C705AA-0962-4D04-B1A1-1F5CD88583C5}"/></connections>
<inputs>
<input id="1290" name="ACCOTInput" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><inputColumns>
<inputColumn id="1311" name="" description="" lineageId="32" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<inputColumn id="1312" name="" description="" lineageId="35" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<inputColumn id="1313" name="" description="" lineageId="38" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<inputColumn id="1314" name="" description="" lineageId="41" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<inputColumn id="1315" name="" description="" lineageId="44" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<inputColumn id="1760" name="" description="" lineageId="183" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
</inputColumns><externalMetadataColumns isUsed="False"/></input>
</inputs>
</component>
</components>
<paths>
<path id="1631" name="Flat File Source Output" description="" startId="2" endId="1290"/>
</paths></pipeline></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{9DE5FEE4-74D0-47BD-8406-D5A9DA26242E}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{3299DEFC-34A8-4474-8A7F-695E3097D527}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property><DTS:Property DTS:Name="DTSID">{FA91537B-3A25-42F5-8AF3-AA789A6BD8FC}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">IM_Dim_Market</DTS:Property><DTS:Property DTS:Name="DTSID">{BCCF6AF5-31B1-4083-8D8E-2278157B0F26}</DTS:Property><DTS:Property DTS:Name="Description">Import package that handles transformation for the Market dimension</DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property>
<DTS:EventHandler><DTS:Property DTS:Name="EventID">2097253</DTS:Property><DTS:Property DTS:Name="EventName">OnError</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:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">System</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="11">-1</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">Propagate</DTS:Property><DTS:Property DTS:Name="DTSID">{E4158456-5E3B-4822-9BAC-DA61D93F20F5}</DTS:Property><DTS:Property DTS:Name="Description">The propagate property of the event</DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<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="DTSID">{CCE3D329-2577-42D4-8B49-B5D14E87761E}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OnError</DTS:Property></DTS:EventHandler></DTS:Executable>
----------------------- End File-------------------------------------
tfrench
I don't quite follow what's going on here but this sounds like a problem in the file connection. Could you share your package and source files
I won't have chance to repro this until later but until then...have you tried evaluating the variable as an expression That will get round your problem of it being static!
-Jamie
KP99
Well, after many painstaking hours of debugging and beating my head against the wall, I finally found a work around to this bug. (I think it is safe to call it a BUG now!)< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
In this case all we needed to do is set the varFileName (found in the foreach container) to an outbound file name in the connection string. (IE: “D:\\”+varFileName+”.xml” ) The problem here is that the value was not being displayed when setting in a FileConn ConnectionString expr (tied in with a script component destination), so we set the ConnectionString of the destination in script and walla, it worked!!
This makes sense (roughly) since we can msgbox the varFileName value in script and see the value okay, just not in expr.
I still do hope that this issue gets resolved in the near future so we don’t end up with a ton of scripted connection strings, but for now we have a work around to what would have potentially scrubbed SSIS.
Incase others would like to know how to do this in code:
Me.Connections.XMLConnection.ConnectionString = vars("rsServerPath").Value.ToString + "\Program Files\" + vars("rsDSN").Value.ToString + "\xxx\yyy\" + vars("varFileName").Value.ToString.Replace("txt", "xml")
Where
XMLConnection = name of connection created
Vars = is the variables we are setting (in our case we lock them in code)
Note: we are also configuring this connection directly in our script component destination inside the data-flow, and haven’t tried this in the control-flow, but would suspect it would work roughly same way, right
Sincere Thanks to Jamie Thomson for looking into this situation, and helping conclude it to be a problem/bug…
adsolo
Wreckage8
As far as I can tell, Betaplace remains the venue for posting bugs.
We (MS employees) don't have a perfectly systematic or automated system or policy for monitoring newsgroups, and (a point I also tried to make in my recent posting on BOL issues) the right person might never notice a particular posting. So reporting bugs or doc issues in the newsgroups is hit-and-miss.
Best regards,
-Doug