SSIS高级转换任务—导入列

在SSIS高级转换任务—导出列这一篇随笔中主要完成的是把数据库中的文件列导出，这里将讨论如何将文件导入到数据库中，它和导出列任务是一对经常搭配使用的任务。当我们搞清楚它们实现什么功能的时候会发现原来的名字更加贴切。这种转换将系统文件路径中的物理文件转化为数据库中的表数据，反之亦然。理解输入列任务的关键是输入源中至少有一列保存将要导入到数据库中的文件的路径，还需要目标列保存文件和文件路径。这种转换仍然使用高级编辑。

就因为它是高级编辑所以不够直观，也没有类似于向导一样的设置，但是熟悉之后使用它将会很方便。在高级编辑中没有类似将两列合并成一列这样的设置所以如果源数据中文件路径和文件名字是分离的，需要使用Merge转换将他们连接起来。

　　

导入列例子

现在来做一个例子将图像文件导入到AdventureWorks数据库中。新建一个包命名为ImportColumnExample,添加一个Data Flow Task，在Data Flow界面拖放一个Import Column transformation,按照下面步骤设置包：

1. 创建路径C:\Users \Pictures\MyPicture
2. 随便在路径C:\Users\Pictures\MyPicture中粘贴3个比较小的图像文件命名为1.jpg，2.jpg，3.jpg
3. 在路径C:\Users\Pictures\MyPicture内创建一个txt文件命名为filelist.txt，文件内容如下
   

   ImageFilePath
   C:\Users \Pictures\MyPicture\1.jpg
   C:\Users \Pictures\MyPicture\2.jpg
   C:\Users \Pictures\MyPicture\3.jpg

4. 运行下面的sql语句创建表 
   

   use AdventureWorks
   Go
   CREATE TABLE dbo.tblmyImages
   (
       [StoredFilePath] [varchar](50) NOT NULL,
       [Document] image
   )

5. 使用filelist.txt作为要载入到数据库中的文件的流，添加一个Flat File Source，filelist.txt作为数据源。右击Flat File Source，选择Show Advanced Editor，它和直接双击打开的编辑界面有所不同，没有设置向导，直接对属性进行设置。可能觉得一团乱麻不好辨别，但是在最糟糕的情况下你可以直接删除重新设置。高级编辑界面如下图1：
   图1
6. 在这个例子中，Flat File Source的高级编辑界面和Import Column Transormation的高级编辑界面很类似。在Import and Output Properties标签界面有两个文件夹结点External Columns和Output Columns，他们都有一个子结点ImageFilePath，这个Flat File Source要输出的数据列名和数据源中的列名一致都是ImageFilePath我们还会看到属性ID是16，ExternalMetaDataColumnID是15。这表示这个转换任务将源输入和输出连接起来。我们注意到下面有Add Output和Remove Output两个按钮但是在这个任务中不能配置这个选项。这里他的作用是将文件中的字符转换成字符流。Column Mappings标签内显示映射关系如图2，如果将filelist.txt内的列名改为myImageFilePath那么这里也将显示为myImageFilePath。
   
   图2  这里TxtFilePath是我自己后来添加的另外一个文本文件列。
7. 将Flat File Source和Import Column连接起来。打开Import Column任务的高级编辑选项，选择Input Column标签，它的输入流是Flat File Source的输出流，点击选中ImageFilePath，切换到Input and Output Propertitis界面，如图6-6，输入列集合中有一列ImageFilePath，但是在输出列集合中没有。在Flat File Source可以忽略输入，但是在这里所有的输入都需要有对应输出，事实上如果没有输出会有下面的错误提示：
   Validation error. Data Flow Task: Import Column [1]: The "input column "ImageFilePath" (164)" references output column ID 0, and that column is not found
8. 选中Output Columns文件夹，点击Add Column按钮添加新列命名为myimage。注意到DataType属性是image [DT_IMAGE]，这是因为输入流中是图像文件，这里夜课仪选择DT_TEXT，DT_NTEXT或者DT_IMAGE。最后需要将输入和输出连接起来，这里注意到myImage列的ID属性是77，这里需要将Input Column集合中ImageFilePath的FileDataColumnID属性设置为77，否则可能如下的错误提示：
   Validation error. Data Flow Task: Import Column [1]: The "output column "myImage" (207)" is not referenced by any input column. Each output column must be referenced by exactly one input column.意思是myImage没有被input column引用到，每一个输出必须有一个对应输入。这里注意如果有多个列，还应设置Input Columns中对应的MappedColumnID属性。
9. 最后添加OLE DB Desination，将Import Column和Ole DB Destination连接起来，编辑Ole DB Destination设置为如上创建的表，点击Mappings setting设置列映射最后运行包，界面如下图3：
   
   图3

最后，数据库中目标表的数据如下：

FullFileName               Document

----------------------     -----------------------------------

C:\import\images\1.JPG     0xFFD8FFE120EE45786966000049492A00...

C:\import\images\2.JPG     0xFFD8FFE125FE45786966000049492A00...

C:\import\images\3.JPG     0xFFD8FFE1269B45786966000049492A00...

(3 row(s) affected)

　　

　　

使用循环导入列

 

在现实环境中可能很少遇到上面的情况，可能要将一系列文件导入到数据库中，这些文件的路径整齐到排放在一个文本文件中。实际情况是从一个FTP文件夹，剪切板中的文件夹中获得文件，将文件载入到数据库中。这里需要自己构件文件路径。可以使用Foreach Loop Container来检索文件，关键的地方是找到可以传递文件路径的字符流。Foreach Loop Container可以得到一系列整齐的文件，但是不能转化成数据源，可以使用sql语句来产生整齐的文件路径，但是这里有更好的方法，读取文件夹内的文件将文件路径保存在数据库中。然后使用Ole DB Source来访问数据并将文件保存在数据库中。

1. 使用下面的语句在数据库中创建一个表USE AdventureWorks
   GO
   CREATE TABLE stgfilelist
   (
      [FullFileName] [varchar](50) NOT NULL,
   )
2. 在Control Flow区域中拖放一个Foreach Loop Container，Foreach container enumerator属性设置为Foreach File Enumerator，Folder属性设置为C:\Users \Pictures\MyPicture，Files属性设置为.jpg，设置Retrieve File Name属性为Fully qualified
3. 点击Variable Mappings，创建一个string类型的package范围内的变量，修改名字为myFilePath，保持Index为默认值0，这将路径内所有文件的文件名保存到数据库中，如图4
   
   图4
4. 在Foreach Loop container中拖放一个Execute SQL，设置Connection属性为数据库Adventure
5. 点击Expression选项，找到SQLStatementSource属性，编写一个sql语句将当前变量myFilePath检索的文件的路径写入到数据库中。Expression表达式如下，使用双引号包括起来："INSERT INTO stgFileList SELECT '"+ @[User::myFilePath] + "'"
6. 将Foreach Loop Container和上次练习中的Data Flow task连接起来，这里直接使用，不再赘述了。
7. 再在整个任务上面添加一个Execute Sql task，删除历史记录，语句如下
   

   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stgfilelist]') AND type in (N'U'))
   BEGIN
   TRUNCATE TABLE dbo.stgfilelist
   END
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblmyMp3]') AND type in (N'U'))
   BEGIN
   TRUNCATE TABLE dbo.tblmyMp3
   END

8. 这里注意到Import Column有一个错误，这是因为修改了数据源错误提示如下：Validation error. Data Flow Task: DTS.Pipeline: input column "FullFileName" (336)has lineage ID 319 that was not previously used in the Data Flow task
9. 打开Import Column的高级编辑界面重新编辑，将会看到无效的列参考信息如图5，这里可以选择无效映射并删除，重新选择OLE DB Source作为源。每一列有单独的ID。这里列名类似在Columns中的下拉列表框中设置新的列，最后保存，打开OLE DB Destination保证数据正确地映射到数据表中。
   
   图5
10. 执行包查看结果文件被保存到数据库中的tblmyImages表中，路径信息保存到stgfilelist表中。最后结果如图6
    
    图6