SSIS OPENROWSET查询平面文件

Question

我目前有一个名为InvoiceFileName的变量名，它通过foreach循环创建.csv文件。然后将.csv的列表输出到文件夹中。

然后，我需要查询每个.csv文件，以选择每个.csv的头和第一行数据。我相信我需要使用OPENROWSET来查询.csv。我有两个问题。

1. 查询变量名InvoiceFileName的语法是什么。
2. 是否可以在不插入表的情况下选择标头字段和第一行数据OPENROWSET。

下面是一个简单的OPENROWSET，它只提供文件的头。

SELECT 
top 1 *
FROM OPENROWSET(BULK N'\\myservername\f$\reports\Invoices\CokeFiles\54ASBSd.csv', SINGLE_CLOB) AS Report 

Answer 1

数据库里有什么样的枢密院？如果您已经或可以稍微提高privs，您可以使用BULK INSERT和xp_cmdShell来完成这一任务，但是就像@scsimon所说的那样，您必须使用动态sql。下面是一个简单的例子：

-----------------------------------------------------------------------------------------------------
-- Set up your variables
-----------------------------------------------------------------------------------------------------
DECLARE 
    @folderPath AS VARCHAR(100) = '\\some\folder\path\here\',
    @cmd AS VARCHAR(150), -- Will populate this with a command to get a list of files in a directory
    @InvoiceFileName AS VARCHAR(100), -- Will be used in cursor loop
    @targetTable AS VARCHAR(50) = 'SomeTable',
    @fieldTerminator AS CHAR(1) = ',',
    @rowTerminator AS CHAR(2) = '\n'
-----------------------------------------------------------------------------------------------------
-- Create a temp table to store the file names
-----------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#FILE_LIST') IS NOT NULL
    DROP TABLE #FILE_LIST
--
CREATE TABLE #FILE_LIST(FILE_NAME VARCHAR(255))

-----------------------------------------------------------------------------------------------------
-- Get a list of the files and store them in the temp table:
-- NOTE: this DOES require elevated permissions
-----------------------------------------------------------------------------------------------------
SET @cmd = 'dir "' + @folderPath + '" /b'
--
INSERT INTO #FILE_LIST(FILE_NAME)
EXEC Master..xp_cmdShell @cmd

--------------------------------------------------------------------------------
-- Here we remove any null values
--------------------------------------------------------------------------------
DELETE #FILE_LIST WHERE FILE_NAME IS NULL

-----------------------------------------------------------------------------------------------------
-- Set up our cursor and loop through the files 
-----------------------------------------------------------------------------------------------------
DECLARE c1 CURSOR FOR SELECT FILE_NAME FROM #FILE_LIST
OPEN c1
FETCH NEXT FROM c1 INTO @InvoiceFileName
WHILE @@FETCH_STATUS <> -1
    BEGIN -- Begin WHILE loop
        BEGIN TRY
            -- Bulk insert won't take a variable name, so dynamically generate the 
            --  SQL statement and execute it instead:
            SET @sql = 'BULK INSERT ' + @targetTable + ' FROM ''' + @InvoiceFileName + ''' '
                + '     WITH ( 
                        FIELDTERMINATOR = ''' + @fieldTerminator + ''', 
                        ROWTERMINATOR = ''' + @rowTerminator + ''', 
                        FIRSTROW = 1,
                        LASTROW = 2
                    ) '
            EXEC (@sql)
        END TRY
        BEGIN CATCH
            -- Handle errors here
        END CATCH
        -- Continue your loop
        FETCH NEXT FROM c1 INTO @path,@filename
    END -- End WHILE loop

-- Do what you need to do here with the data in your target table

一些免责声明：

1. 我还没有测试过这段代码。只是从我在过去使用过的一个稍微复杂一些的proc中复制出来的，它正是适用于这种场景的。
2. 对于BULK INSERT和xp_cmdShell，您将需要更高的特权。
3. 我知道人们不喜欢使用xp_cmdShell (这是有充分理由的)，但是这是一个快速而肮脏的解决方案，对您的环境做了很多假设。
4. 这是假设您在获取变量中的每个文件时没有抓取数据。如果是，可以跳过这段代码的第一部分。
5. 这段代码还假设您正在尝试/捕捉块之外的其他地方执行自己的错误处理。为了简单起见，我省略了很多。

要通过SSIS完成此操作，理想情况下您可能需要对大容量操作使用格式文件，但是您必须有一致的格式化文件并删除SINGLE_CLOB选项。要做到这一点，一种非常讨厌和不理想的方法是这样做：

假设您的文件包含以下数据：

Col1,Col2,Col3,Col4
Here's,The,First,Line
Here's,The,Second,Line
Here's,The,Third,Line
Here's,The,Fourth,Line

然后，您可以简单地解析数据，这样做如下：

SELECT SUBSTRING(OnlyColumn, 0, CHARINDEX(CHAR(10), OnlyColumn, CHARINDEX(CHAR(10), OnlyColumn, 0)+1) )
FROM OPENROWSET(BULK '\\location\of\myFile.csv', SINGLE_CLOB) AS Report (OnlyColumn)

你的结果是：

Col1,Col2,Col3,Col4  Here's,The,First,Line 

这显然取决于您的行尾是否一致，但是如果您希望在单个列和单行中获得结果(就像使用SINGLE_CLOB选项的大容量操作的行为一样)，那么您应该可以得到所需的结果。

您可以查看这是如此的帖子上的解决方案，了解如何将SSIS变量值作为参数传递给查询。

Answer 2

使用Foreach循环容器查询文件夹中的所有文件。您可以使用通配符作为文件名，或者使用DTS中的变量来设置组件的属性。

在循环容器中，使用源文件连接、转换和目的地放置数据流任务。

通过将这些对象的属性设置为DTS中的变量，可以修改所有这些对象的文件名和路径。

使用循环中的Expresion，您可以更改CSV文件连接的路径。

​

​