为什么我不能将它转换为 DT_STR - 数据源是 WSTR，但这种转换不应该使输出 DT_STR 吗？
(IPUNSPSC) =="**no_UNSPSC_available**" ? (DT_STR,20,1252)NULL(DT_STR,20,1252) : (DT_STR,20,1252)TRIM(IPUNSPSC)
所以我有一个程序：
ALTER PROCEDURE [dbo].[use___MasterLoadAll]
AS
--Must run in order
EXEC [dbo].[use__MasterLoad.Staging];
EXEC [dbo].[use__MasterApplyBRs];
EXEC [dbo].[use__MasterLoad.pdw.*];
EXEC [dbo].[use__MasterLoad.FPDW_Cube];
我的问题是，如果控制器上的业务规则失败，我想停止该过程 - 这是 EXEC [dbo] 中的内容。[use__MasterApplyBRs]
ALTER PROCEDURE [dbo].[use__MasterApplyBRs]
AS
EXEC [dbo].[usp_ApplyDMBusinessRules];
EXEC [dbo].[usp_ApplyIndirectBusinessRules];
然后执行 [dbo].[usp_ApplyDMBusinessRules]
ALTER PROCEDURE [dbo].[usp_ApplyDMBusinessRules]
AS
-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'DMController.dtsx', @execution_id=@execution_id OUTPUT,
@folder_name=N'StagingImport', @project_name=N'BusRulesDirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr
-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync
-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id
IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)
然后执行 [dbo].[usp_ApplyIndirectBusinessRules]
ALTER PROCEDURE [dbo].[usp_ApplyIndirectBusinessRules]
AS
-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'InDirectController.dtsx', @execution_id=@execution_id OUTPUT,
@folder_name=N'StagingImport', @project_name=N'BusRulesIndirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr
-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync
-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id
IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)
所以最后每个控制器都有类似的 TSQL 调用一堆业务规则包 - 但如果这些包中的任何一个失败，控制器将继续并结束报告失败 -
因此，如果控制器在业务规则中报告失败，我需要 MasterLoadAll 停止——如何？
我是新来的...
我在 SSIS 中构建了一个 ETL 过程，它需要 46 个平面文件并将它们加载到 stg.Suppliers 和 triage.Suppliers 中。然后运行应用业务规则的另一个解决方案。然后最终解决方案将好东西从 stg.Suppliers 转移到 pdw.Suppliers。
我的问题是我什么时候运行所有这些（每周一次）；清理 stg.Suppliers 表的缓冲区是否好（最佳实践）？我注意到，当我运行此查询时，它是排名第一的 cached_pages_count。
如果是，如何？