小贝子编程

T-SQL 和 Xquery - 更改文本值以使用对 none 的引用不会返回结果



我希望有人能帮助我：

此列根据 xml 中的DTS:ObjectName="vSQL_ASN"属性返回我需要的值：

col.value('(//DTS:Variables/DTS:Variable[@DTS:ObjectName=**''vSQL_ASN''**]/DTS:VariableValue)[1]', 'VARCHAR(MAX)')

但是，当我替换"vSQL_ASN"以使用另一个 xpath 查询的查找时，我知道该查询的评估结果恰好为"v_SQl_ASN"，我返回了 Null。

col.value('(//DTS:Variables/DTS:Variable[@DTS:ObjectName = **substring((./properties/property[@name="SelectStatement"])[1],9,string-length(substring((./properties/property[@name=''SelectStatement''])[1],8,50))-2)]/DTS:VariableValue**)[1]', 'VARCHAR(MAX)')

谁能帮忙为什么会这样？

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:refId="Package"
DTS:CreationDate="2/16/2013 1:09:40 PM"
DTS:CreationName="Microsoft.Package"
DTS:CreatorComputerName=""
DTS:CreatorName=""
DTS:DTSID="{47964379-22CB-4CC3-A5CD-3D01A1B8565C}"
DTS:EnableConfig="True"
DTS:ExecutableType="Microsoft.Package"
DTS:LastModifiedProductVersion="15.0.1100.123"
DTS:LocaleID="2057"
DTS:ObjectName="1118_WMOS_Intk_FV"
DTS:PackageType="5"
DTS:VersionBuild="169"
DTS:VersionGUID="{C43BDFD3-ED85-482F-8065-C07332E04CD1}">
<DTS:Property
DTS:Name="PackageFormatVersion">8</DTS:Property>
<DTS:Variables>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{2BC52CDF-EB31-4362-9D38-96E9F58C47BE}"
DTS:EvaluateAsExpression="True"
DTS:Expression="&quot;This isn't SQL as we're not yet constraining extract SQL using From &amp; To date - We currently SELECT ALL FROM Staging VIEW for Products as opposed to an incremental date related load - this variable here for that purpose&quot;"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vExtractSQL">
<DTS:VariableValue
DTS:DataType="8">This isn't SQL as we're not yet constraining extract SQL using From &amp; To date - We currently SELECT ALL FROM Staging VIEW for Products as opposed to an incremental date related load - this variable here for that purpose</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:Description="This should be set to the PackageID of the package in the ManagmentDB tblPackage table"
DTS:DTSID="{B7B9C488-1B2B-4EC0-A78B-7014DAE3D528}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPackageID">
<DTS:VariableValue
DTS:DataType="3">1118</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:Description="package execution ID returned from usp at start - used for comletion at end"
DTS:DTSID="{8D185A78-654C-468C-9BEA-2BFD8471B9CC}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgExecID">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{8A604FDE-416C-444E-BD73-9CC599728D33}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[System::PackageID]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vPkgGUID">
<DTS:VariableValue
DTS:DataType="8">{47964379-22CB-4CC3-A5CD-3D01A1B8565C}</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{FF4A4DA8-A725-4075-9A72-FA72711E29B8}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgLastRunID">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{FF87FDEF-B219-4CA1-AF26-A22022025D17}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[System::PackageName]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vPkgName">
<DTS:VariableValue
DTS:DataType="8">1118_WMOS_Intk_FV</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{1D72E935-0B7A-4EBF-BA03-0EDC01D6526A}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[User::vPkgRowsBad_ILMAPPOINTMENTS] +  @[User::vPkgRowsBad_ASN_DTL] +  @[User::vPkgRowsBad_ASN]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsBad">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{A38F9327-CEC7-40A1-B14F-94545197FFB5}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsBad_ASN">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{18F09461-E18D-41A9-BB02-3E49C13D196B}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsBad_ASN_DTL">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{09363B5F-A496-49FA-B11C-FBB7C0E7FA74}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsBad_ILMAPPOINTMENTS">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{3CECBAD0-E1DB-44A4-9081-F32831415669}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[User::vPkgRowsExtracted_ILMAPPOINTMENTS] +  @[User::vPkgRowsExtracted_ASN_DTL] +  @[User::vPkgRowsExtracted_ASN]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsExtracted">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{19441A99-2D0D-45AA-86DB-315A7E14830E}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsExtracted_ASN">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{D13BC9F9-E34A-488C-A418-B8EF62AD7C84}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsExtracted_ASN_DTL">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{0137A3E9-065A-4856-919B-B9E88F9CAC5B}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsExtracted_ILMAPPOINTMENTS">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{B23A146C-67D5-494D-9247-2637FE78A143}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[User::vPkgRowsExtracted] -  @[User::vPkgRowsBad]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vPkgRowsInserted">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{E47A8B66-1D05-4CD4-A821-933AA955A97C}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[System::VersionGUID]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vPkgVersionGUID">
<DTS:VariableValue
DTS:DataType="8">{C43BDFD3-ED85-482F-8065-C07332E04CD1}</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{5B7EDEC7-8C77-47EF-AD02-B240407A6CB0}"
DTS:EvaluateAsExpression="True"
DTS:Expression="@[System::StartTime]"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vReportingEndDate">
<DTS:VariableValue
DTS:DataType="7">10/8/2019 1:50:43 PM</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{92B57286-BE9F-484B-A472-2338BF93F46A}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vReportingStartDate">
<DTS:VariableValue
DTS:DataType="7">6/1/2019</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{227CB535-8A39-405E-A3D5-AF96BC7F7E9C}"
DTS:EvaluateAsExpression="True"
DTS:Expression="&quot;SELECT &#xA;  asn.destination_facility_alias_id AS TO_WHSE,&#xA;  asn.tc_asn_id AS SHPMT_NBR,&#xA;  asn.actual_arrival_dttm AS ARRIVAL_DATE_TIME,&#xA;  asn.first_receipt_dttm AS FIRST_RCPT_DATE_TIME,&#xA;  asn.last_receipt_dttm AS LAST_RCPT_DATE_TIME,&#xA;  TO_CHAR(asn.last_updated_dttm, 'DD-MON-YY') AS MOD_DATE_TIME,&#xA;    CASE &#xA;    WHEN asn.asn_status IN (5,10) THEN 0  -- CONVERT IN PLANNING &amp; OPEN TO ORDER PLACED&#xA;    WHEN asn.asn_status = 20 THEN 10      -- CONVERT IN TRANSIT TO SHIPPED&#xA;    WHEN asn.asn_status = 30 THEN 30      -- CONVERT RECEIVING STARTED TO DOM PO IN RECEIPT&#xA;    WHEN asn.asn_status = 40 THEN 90      -- CONVERT RECEIVED VERIFED TO RECEIVED&#xA;    WHEN asn.asn_status IN (50,60,70) THEN 99      -- CONVERT REFUSED, CANCELLED &amp; PENDING CANCELLED TO CANCELLED&#xA;  END AS STAT_CODE,&#xA;  CAST('FV' AS VARCHAR2(2)) AS SOURCE&#xA;FROM wms17seed.asn&#xA;WHERE&#xA;  SUBSTR(asn.tc_asn_id,1,3) NOT IN ('BLD','COL','DAC','FLD','SAM') &#xA;  AND TRUNC(asn.last_updated_dttm) &gt;= TRUNC(TO_DATE('&quot; +  (DT_WSTR,20) @[User::vReportingStartDate] + &quot;','DD/MM/YYYY HH24:MI:SS'))&#xA;  AND TRUNC(asn.last_updated_dttm) &lt;= TRUNC(TO_DATE('&quot; + (DT_WSTR,20)  @[User::vReportingEndDate] + &quot;','DD/MM/YYYY HH24:MI:SS'))&quot;"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vSQL_ASN">
<DTS:VariableValue
DTS:DataType="8">SELECT 
asn.destination_facility_alias_id AS TO_WHSE,
asn.tc_asn_id AS SHPMT_NBR,
asn.actual_arrival_dttm AS ARRIVAL_DATE_TIME,
asn.first_receipt_dttm AS FIRST_RCPT_DATE_TIME,
asn.last_receipt_dttm AS LAST_RCPT_DATE_TIME,
TO_CHAR(asn.last_updated_dttm, 'DD-MON-YY') AS MOD_DATE_TIME,
CASE 
WHEN asn.asn_status IN (5,10) THEN 0  -- CONVERT IN PLANNING &amp; OPEN TO ORDER PLACED
WHEN asn.asn_status = 20 THEN 10      -- CONVERT IN TRANSIT TO SHIPPED
WHEN asn.asn_status = 30 THEN 30      -- CONVERT RECEIVING STARTED TO DOM PO IN RECEIPT
WHEN asn.asn_status = 40 THEN 90      -- CONVERT RECEIVED VERIFED TO RECEIVED
WHEN asn.asn_status IN (50,60,70) THEN 99      -- CONVERT REFUSED, CANCELLED &amp; PENDING CANCELLED TO CANCELLED
END AS STAT_CODE,
CAST('FV' AS VARCHAR2(2)) AS SOURCE
FROM wms17seed.asn
WHERE
SUBSTR(asn.tc_asn_id,1,3) NOT IN ('BLD','COL','DAC','FLD','SAM') 
AND TRUNC(asn.last_updated_dttm) &gt;= TRUNC(TO_DATE('01/06/2019','DD/MM/YYYY HH24:MI:SS'))
AND TRUNC(asn.last_updated_dttm) &lt;= TRUNC(TO_DATE('08/10/2019 13:50:43','DD/MM/YYYY HH24:MI:SS'))</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{858CF7F6-58E8-4A53-A4E0-CDACFBBDC0E0}"
DTS:EvaluateAsExpression="True"
DTS:Expression="&quot;SELECT &#xA; asn.destination_facility_alias_id AS TO_WHSE,&#xA;  asn.tc_asn_id AS SHPMT_NBR,&#xA;  asn_dtl.seq_nbr AS SHPMT_SEQ_NBR,&#xA;  asn_dtl.sku_name AS SKU_BRCD,&#xA;  NVL(asn_dtl.shipped_lpn_count,0) AS CASES_SHPD,&#xA;  NVL(asn_dtl.shipped_qty,0) AS UNITS_SHPD,&#xA;  NVL(asn_dtl.received_lpn_count,0) AS CASES_RCVD,&#xA;  NVL(asn_dtl.received_qty,0) AS UNITS_RCVD,&#xA;  asn_dtl.tc_purchase_orders_id AS PO_NBR,&#xA;  asn_dtl.tc_po_line_id AS PO_LINE_NBR,&#xA;  TO_CHAR(asn_dtl.created_dttm, 'dd-MON-yy') AS CREATE_DATE_TIME,&#xA;  TO_CHAR(asn_dtl.last_updated_dttm, 'dd-MON-yy') AS MOD_DATE_TIME,&#xA;  CAST('FV' AS VARCHAR2(2)) AS SOURCE&#xA;FROM wms17seed.asn_detail asn_dtl&#xA;  INNER JOIN wms17seed.asn ON asn_dtl.asn_id = asn.asn_id &#xA;WHERE&#xA;  SUBSTR(asn.tc_asn_id,1,3) NOT IN ('BLD','COL','DAC','FLD','SAM')&#xA;  &#xA;AND asn_dtl.seq_nbr IS NOT NULL&#xA;&quot;"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vSQL_ASNDetail">
<DTS:VariableValue
DTS:DataType="8">SELECT 
asn.destination_facility_alias_id AS TO_WHSE,
asn.tc_asn_id AS SHPMT_NBR,
asn_dtl.seq_nbr AS SHPMT_SEQ_NBR,
asn_dtl.sku_name AS SKU_BRCD,
NVL(asn_dtl.shipped_lpn_count,0) AS CASES_SHPD,
NVL(asn_dtl.shipped_qty,0) AS UNITS_SHPD,
NVL(asn_dtl.received_lpn_count,0) AS CASES_RCVD,
NVL(asn_dtl.received_qty,0) AS UNITS_RCVD,
asn_dtl.tc_purchase_orders_id AS PO_NBR,
asn_dtl.tc_po_line_id AS PO_LINE_NBR,
TO_CHAR(asn_dtl.created_dttm, 'dd-MON-yy') AS CREATE_DATE_TIME,
TO_CHAR(asn_dtl.last_updated_dttm, 'dd-MON-yy') AS MOD_DATE_TIME,
CAST('FV' AS VARCHAR2(2)) AS SOURCE
FROM wms17seed.asn_detail asn_dtl
INNER JOIN wms17seed.asn ON asn_dtl.asn_id = asn.asn_id 
WHERE
SUBSTR(asn.tc_asn_id,1,3) NOT IN ('BLD','COL','DAC','FLD','SAM')
AND asn_dtl.seq_nbr IS NOT NULL
</DTS:VariableValue>
</DTS:Variable>
</DTS:Variables>
<DTS:Executables>
<DTS:Executable
DTS:refId="PackageSEQ_StagingJob"
DTS:CreationName="STOCK:SEQUENCE"
DTS:Description="The work of the package"
DTS:DTSID="{7EFE1902-6B7A-4D1A-A0C6-37479BCA7A94}"
DTS:ExecutableType="STOCK:SEQUENCE"
DTS:LocaleID="-1"
DTS:ObjectName="SEQ_StagingJob">
<DTS:Variables />
<DTS:Executables>
<DTS:Executable
DTS:refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTbls"
DTS:CreationName="Microsoft.Pipeline"
DTS:Description="Data Flow Task"
DTS:DTSID="{5E1643D0-3ADB-43F3-A179-F5945057D703}"
DTS:ExecutableType="Microsoft.Pipeline"
DTS:LocaleID="-1"
DTS:ObjectName="dft_LdFVPKMSStgTbls"
DTS:TaskContact="Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1">
<DTS:Variables />
<DTS:ObjectData>
<pipeline
version="1">
<components>
<component
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN"
componentClassID="Microsoft.RowCount"
contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status."
name="badrows_ASN">
<properties>
<property
dataType="System.String"
description="Specifies the variable to hold the row count."
name="VariableName">User::vPkgRowsBad_ASN</property>
</properties>
<inputs>
<input
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN.Inputs[Row Count Input 1]"
hasSideEffects="true"
name="Row Count Input 1">
<externalMetadataColumns />
</input>
</inputs>
<outputs>
<output
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN.Outputs[Row Count Output 1]"
name="Row Count Output 1"
synchronousInputId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN.Inputs[Row Count Input 1]">
<externalMetadataColumns />
</output>
</outputs>
</component>
<component
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN_DTL"
componentClassID="Microsoft.RowCount"
contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status."
name="badrows_ASN_DTL">
<properties>
<property
dataType="System.String"
description="Specifies the variable to hold the row count."
name="VariableName">User::vPkgRowsBad_ASN_DTL</property>
</properties>
<inputs>
<input
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN_DTL.Inputs[Row Count Input 1]"
hasSideEffects="true"
name="Row Count Input 1">
<externalMetadataColumns />
</input>
</inputs>
<outputs>
<output
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN_DTL.Outputs[Row Count Output 1]"
name="Row Count Output 1"
synchronousInputId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ASN_DTL.Inputs[Row Count Input 1]">
<externalMetadataColumns />
</output>
</outputs>
</component>
<component
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ILM_APPOINTMENTS"
componentClassID="Microsoft.RowCount"
contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status."
name="badrows_ILM_APPOINTMENTS">
<properties>
<property
dataType="System.String"
description="Specifies the variable to hold the row count."
name="VariableName">User::vPkgRowsBad_ILMAPPOINTMENTS</property>
</properties>
<inputs>
<input
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ILM_APPOINTMENTS.Inputs[Row Count Input 1]"
hasSideEffects="true"
name="Row Count Input 1">
<externalMetadataColumns />
</input>
</inputs>
<outputs>
<output
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ILM_APPOINTMENTS.Outputs[Row Count Output 1]"
name="Row Count Output 1"
synchronousInputId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblsbadrows_ILM_APPOINTMENTS.Inputs[Row Count Input 1]">
<externalMetadataColumns />
</output>
</outputs>
</component>
<component
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN"
componentClassID="Microsoft.RowCount"
contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status."
name="cnt_extrctd_ASN">
<properties>
<property
dataType="System.String"
description="Specifies the variable to hold the row count."
name="VariableName">User::vPkgRowsExtracted_ASN</property>
</properties>
<inputs>
<input
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN.Inputs[Row Count Input 1]"
hasSideEffects="true"
name="Row Count Input 1">
<externalMetadataColumns />
</input>
</inputs>
<outputs>
<output
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN.Outputs[Row Count Output 1]"
name="Row Count Output 1"
synchronousInputId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN.Inputs[Row Count Input 1]">
<externalMetadataColumns />
</output>
</outputs>
</component>
<component
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN_DTL"
componentClassID="Microsoft.RowCount"
contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status."
name="cnt_extrctd_ASN_DTL">
<properties>
<property
dataType="System.String"
description="Specifies the variable to hold the row count."
name="VariableName">User::vPkgRowsExtracted_ASN_DTL</property>
</properties>
<inputs>
<input
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN_DTL.Inputs[Row Count Input 1]"
hasSideEffects="true"
name="Row Count Input 1">
<externalMetadataColumns />
</input>
</inputs>
<outputs>
<output
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN_DTL.Outputs[Row Count Output 1]"
name="Row Count Output 1"
synchronousInputId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ASN_DTL.Inputs[Row Count Input 1]">
<externalMetadataColumns />
</output>
</outputs>
</component>
<component
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ILM_APPOINTMENTS"
componentClassID="Microsoft.RowCount"
contactInfo="Row Count;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
description="Counts rows as they pass through a data flow. For example, use the number of rows processed to determine whether or not to perform cleanup operations, or update text in an e-mail message to communicate processing status."
name="cnt_extrctd_ILM_APPOINTMENTS">
<properties>
<property
dataType="System.String"
description="Specifies the variable to hold the row count."
name="VariableName">User::vPkgRowsExtracted_ILMAPPOINTMENTS</property>
</properties>
<inputs>
<input
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ILM_APPOINTMENTS.Inputs[Row Count Input 1]"
hasSideEffects="true"
name="Row Count Input 1">
<externalMetadataColumns />
</input>
</inputs>
<outputs>
<output
refId="PackageSEQ_StagingJobdft_LdFVPKMSStgTblscnt_extrctd_ILM_APPOINTMENTS.Outputs[Row Count Output 1]"
name="Row Count Output 1"

declare @xml xml = N'
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:refId="Package"
DTS:CreationDate="2/16/2013 1:09:40 PM"
DTS:CreationName="Microsoft.Package"
DTS:CreatorComputerName=""
DTS:CreatorName=""
DTS:DTSID="{47964379-22CB-4CC3-A5CD-3D01A1B8565C}"
DTS:EnableConfig="True"
DTS:ExecutableType="Microsoft.Package"
DTS:LastModifiedProductVersion="15.0.1100.123"
DTS:LocaleID="2057"
DTS:ObjectName="1118_WMOS_Intk_FV"
DTS:PackageType="5"
DTS:VersionBuild="169"
DTS:VersionGUID="{C43BDFD3-ED85-482F-8065-C07332E04CD1}">
<DTS:Property
DTS:Name="PackageFormatVersion">8</DTS:Property>
<DTS:Variables>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{2BC52CDF-EB31-4362-9D38-96E9F58C47BE}"
DTS:EvaluateAsExpression="True"
DTS:Expression="&quot;This isn''t SQL as we''re not yet constraining extract SQL using From &amp; To date - We currently SELECT ALL FROM Staging VIEW for Products as opposed to an incremental date related load - this variable here for that purpose&quot;"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vExtractSQL">
<DTS:VariableValue
DTS:DataType="8">This isn''t SQL as we''re not yet constraining extract SQL using From &amp; To date - We currently SELECT ALL FROM Staging VIEW for Products as opposed to an incremental date related load - this variable here for that purpose</DTS:VariableValue>
</DTS:Variable>

<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{227CB535-8A39-405E-A3D5-AF96BC7F7E9C}"
DTS:EvaluateAsExpression="True"
DTS:Expression="&quot;SELECT &#xA;  asn.destination_facility_alias_id AS TO_WHSE,&#xA;  asn.tc_asn_id AS SHPMT_NBR,&#xA;  asn.actual_arrival_dttm AS ARRIVAL_DATE_TIME,&#xA;  asn.first_receipt_dttm AS FIRST_RCPT_DATE_TIME,&#xA;  asn.last_receipt_dttm AS LAST_RCPT_DATE_TIME,&#xA;  TO_CHAR(asn.last_updated_dttm, ''DD-MON-YY'') AS MOD_DATE_TIME,&#xA;    CASE &#xA;    WHEN asn.asn_status IN (5,10) THEN 0  -- CONVERT IN PLANNING &amp; OPEN TO ORDER PLACED&#xA;    WHEN asn.asn_status = 20 THEN 10      -- CONVERT IN TRANSIT TO SHIPPED&#xA;    WHEN asn.asn_status = 30 THEN 30      -- CONVERT RECEIVING STARTED TO DOM PO IN RECEIPT&#xA;    WHEN asn.asn_status = 40 THEN 90      -- CONVERT RECEIVED VERIFED TO RECEIVED&#xA;    WHEN asn.asn_status IN (50,60,70) THEN 99      -- CONVERT REFUSED, CANCELLED &amp; PENDING CANCELLED TO CANCELLED&#xA;  END AS STAT_CODE,&#xA;  CAST(''FV'' AS VARCHAR2(2)) AS SOURCE&#xA;FROM wms17seed.asn&#xA;WHERE&#xA;  SUBSTR(asn.tc_asn_id,1,3) NOT IN (''BLD'',''COL'',''DAC'',''FLD'',''SAM'') &#xA;  AND TRUNC(asn.last_updated_dttm) &gt;= TRUNC(TO_DATE(''&quot; +  (DT_WSTR,20) @[User::vReportingStartDate] + &quot;'',''DD/MM/YYYY HH24:MI:SS''))&#xA;  AND TRUNC(asn.last_updated_dttm) &lt;= TRUNC(TO_DATE(''&quot; + (DT_WSTR,20)  @[User::vReportingEndDate] + &quot;'',''DD/MM/YYYY HH24:MI:SS''))&quot;"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="vSQL_ASN">
<DTS:VariableValue
DTS:DataType="8">SELECT 
asn.destination_facility_alias_id AS TO_WHSE,
asn.tc_asn_id AS SHPMT_NBR,
asn.actual_arrival_dttm AS ARRIVAL_DATE_TIME,
asn.first_receipt_dttm AS FIRST_RCPT_DATE_TIME,
asn.last_receipt_dttm AS LAST_RCPT_DATE_TIME,
TO_CHAR(asn.last_updated_dttm, ''DD-MON-YY'') AS MOD_DATE_TIME,
CASE 
WHEN asn.asn_status IN (5,10) THEN 0  -- CONVERT IN PLANNING &amp; OPEN TO ORDER PLACED
WHEN asn.asn_status = 20 THEN 10      -- CONVERT IN TRANSIT TO SHIPPED
WHEN asn.asn_status = 30 THEN 30      -- CONVERT RECEIVING STARTED TO DOM PO IN RECEIPT
WHEN asn.asn_status = 40 THEN 90      -- CONVERT RECEIVED VERIFED TO RECEIVED
WHEN asn.asn_status IN (50,60,70) THEN 99      -- CONVERT REFUSED, CANCELLED &amp; PENDING CANCELLED TO CANCELLED
END AS STAT_CODE,
CAST(''FV'' AS VARCHAR2(2)) AS SOURCE
FROM wms17seed.asn
WHERE
SUBSTR(asn.tc_asn_id,1,3) NOT IN (''BLD'',''COL'',''DAC'',''FLD'',''SAM'') 
AND TRUNC(asn.last_updated_dttm) &gt;= TRUNC(TO_DATE(''01/06/2019'',''DD/MM/YYYY HH24:MI:SS''))
AND TRUNC(asn.last_updated_dttm) &lt;= TRUNC(TO_DATE(''08/10/2019 13:50:43'',''DD/MM/YYYY HH24:MI:SS''))</DTS:VariableValue>
</DTS:Variable>    

<DTS:Variable
DTS:CreationName=""
DTS:Description="This should be set to the PackageID of the package in the ManagmentDB tblPackage table"
DTS:DTSID="{B7B9C488-1B2B-4EC0-A78B-7014DAE3D528}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="vPackageID">
<DTS:VariableValue
DTS:DataType="3">1118</DTS:VariableValue>
</DTS:Variable>
</DTS:Variables>
</DTS:Executable>  
';
select @xml.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
(//DTS:Variables/DTS:Variable[@DTS:ObjectName="vSQL_ASN"]/DTS:VariableValue)[1]', 'VARCHAR(MAX)');
select @xml.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
(//DTS:Variables/DTS:Variable[@DTS:ObjectName="vExtractSQL"]/DTS:VariableValue)[1]', 'VARCHAR(MAX)');
select @xml.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
(//DTS:Variables/DTS:Variable[@DTS:ObjectName=sql:column("drv.property")]/DTS:VariableValue)[1]', 'VARCHAR(MAX)')
from
(values('vSQL_ASN'),('vExtractSQL')) as drv(property);

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium