SQL server 如何在function语句中输出自定义日志log或者print sql输出提示信息

开篇介绍

在 SSIS 中并没有直接提供从数据源到 XML 的转换输出，Destination 的输出对象有 Excel File, Flat File, Database 等，但是并没有直接提供 XML 文件输出的配置。

但是我们仍然可以通过下面这些方法来实现：

方法一：在数据流中使用平面文件对字符串 XML 转换输出

方法二：在控制流中使用 Script Task 输出 XML 文件

需求描述

要将下面的这种查询结果转换成 XML -

需要输出成 XML 文件的格式 -

那么首先在SQL 语句中就需要将格式转换一下，可以将将查询结果包装成相应的 XML 格式 -

SELECT *
FROM T009_SALES_ORDER_DETAIL  
FOR XML RAW('SalesOrderDetail'),ROOT('SalesOrder'),ELEMENTS

关于 SQL XML 查询的内容，不是文本的重点，大家可以参考博客园中其他博友的博客：

1. SQL FOR XML
2. 灵活运用 SQL SERVER FOR XML PATH

在数据流中使用平面文件对字符串 XML 转换输出

在 Data Flow 中新建一个 Source 并且使用上面的 SQL 语句，这个 SQL 语句查询的结果是一个 XML 格式的大字符串。

它的 Columns 输出的就是包含了整个 XML 结果的字符串。

为了兼容后面文件输出时的字符类型转换问题，添加一个 Data Convertion 将这个输出改变一下数据类型，这里选择的是 Unicode text stream [DT_NTEXT]。

新建一个 Flat File Connection Manager，文件后缀名称是 .xml 结尾。如果测试环境是中文，或者输出的字符串中有双字节字符类型，那么就应该在 Locale 选择适当的环境语言，并且应该勾选上 Unicode。

并且在 Advanced 中新增加一个 Column，并且一定要注意在类型中应该选择的应该是 Unicode text stream。并且这也就是要用 Flat File Connection 而不使用 Raw File 的原因，因为 Raw File 中不支持 Text 文本数据类型。

新建一个 Flat File Destination 组件，并且使用上面编辑好了的 Flat File Connection Manager。

使用刚才在 Data Conversion 中编辑过的新 Column 与输出 Column 匹配。

保存并执行 SSIS Package。

你认为一定成功了，对吧！打开一看，傻眼了吧！

原因是什么呢？我们可以回到 OLE_SRC_OrderDetail 中 Preview 一下，看到了吗？在这里这个输出是Byte字节数组。

需要将上面的 SQL 再次包装成 XML 一下，将这个查询结果单独包装成一个字符串格式。

SELECT
(
  SELECT TOP 10
     [SalesOrderID]
    ,[SalesOrderDetailID]
    ,[CarrierTrackingNumber]
    ,[OrderQty]
    ,[ProductID]
    ,[UnitPrice]
    ,[UnitPriceDiscount]
    ,[ModifiedDate]
   FROM [Sales].[SalesOrderDetail]
FOR XML RAW('SalesOrderDetail'),ROOT('SalesOrder'),ELEMENTS
)AS XML_Order

不包装时在 SQL Server 中的查询结果可以理解它是一个正儿八经的 XML 文件，点击打开就是一整个 XML 格式的文本 -

包装之后在 SQL Server 中的查询结果 - 这才是真正的文本数据。

再次 Preview 一下，看到 XML 格式的字符串。

修改后面的 Mapping 关系，并保存执行输出，在浏览器中查看 XML 文件结果。

在控制流中使用 Script Task 输出 XML 文件

新建两个变量，一个保存文件路径，另一个保存 XML 字符串。

注意这时使用的是 Execute SQL Task 是一个控制流控件， SQL Statement 中使用上面那个包装过的 XML 查询。

Result Set 中将输出的 XML 结果用上面新建的变量来保存。

新建一个 Script Task 传入两个变量。

Script 中引用 System.IO 命名空间，然后就是下面这几句代码。

public void Main()
{
            string content = Dts.Variables["User::XmlString"].Value.ToString();
            string filePath = Dts.Variables["User::XmlFilePath"].Value.ToString();
            StreamWriter writer = new StreamWriter(filePath);
            writer.WriteLine(content);
            writer.Close();
           
            Dts.TaskResult = (int)ScriptResults.Success;
}

保存执行并查看输出的 XML 文件。

输出的结果有一个 ROOT ，这可以在代码中非常容易的处理掉。

string content = Dts.Variables["User::XML_STRING"].Value.ToString().Replace("<ROOT>", "").Replace("</ROOT>", "");

PS : 更新补充一下不使用 SSIS 工具直接通过 SQL 输出到文件的方式 - XP_CMDSHELL

EXEC sp_configure 'show advanced options', 1
GO 
RECONFIGURE
GO 
EXEC sp_configure 'xp_cmdshell', 1
GO 
RECONFIGURE
GO
 
EXEC XP_CMDSHELL 'BCP "SELECT(SELECT TOP 2 * FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]  FOR XML RAW(''SalesOrderDetail''),ROOT(''SalesOrder''),ELEMENTS )AS XML_Order " QUERYOUT "D:\text.xml" -c -T'
 
EXEC sp_configure 'show advanced options', 1
GO 
RECONFIGURE
GO 
EXEC sp_configure 'xp_cmdshell', 0
GO 
RECONFIGURE
GO

这样也是可以的，但是一般情况下至少我很少选择这种方式，原因主要有几个：

1. XP_CMDSHELL 默认被禁用的，要使用 sp_configure 来控制，因此需要级别比较高的权限，但是在实际操作中客户在很多时候不会给那么多的权限。

2. 很重要的一点就是在 BI 当中，我们所有的文件输出一定有日志的记录，包括输出路径，起始时间，文件大小等等，使用 SSIS 可以更好的跟 Process Log 结合起来使用。

3. 如果是一些比较复杂的 SQL 查询输出，在 SQL 中拼接字符串也是一件非常痛苦的事情。