SSIS高级转换任务—导入列

简介: 在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:img_7700682d7f1643a8b8f1d4e58cc01cad.png
    图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。
    img_c3bf7427b43e42e93560710c3b032ec3.png
    图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:
    img_18fa99f06e3b348d8745ac187e80005c.png
    图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
    img_64b9b305b43d50103cc5770f9d606325.png
    图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保证数据正确地映射到数据表中。
    img_8d684744c3f64218a331db865c3d346a.png
    图5
  10. 执行包查看结果文件被保存到数据库中的tblmyImages表中,路径信息保存到stgfilelist表中。最后结果如图6
    img_0d62a0d93ae761af53d8ed2e7ad05b25.png
    图6

作者:Tyler Ning
出处:http://www.cnblogs.com/tylerdonet/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,如有问题,可以通过以下邮箱地址williamningdong@gmail.com  联系我,非常感谢。

目录
相关文章
|
JavaScript
VUE之vxe-table高级表格(表格内增删改、导入、导出、自定义打印、列设置隐藏显示等)用法
VUE之vxe-table高级表格(表格内增删改、导入、导出、自定义打印、列设置隐藏显示等)用法
8031 0
VUE之vxe-table高级表格(表格内增删改、导入、导出、自定义打印、列设置隐藏显示等)用法
|
7月前
|
SQL Java 数据库
Sqoop【付诸实践 02】Sqoop1最新版 全库导入 + 数据过滤 + 字段类型支持 说明及举例代码(query参数及字段类型强制转换)
【2月更文挑战第10天】Sqoop【付诸实践 02】Sqoop1最新版 全库导入 + 数据过滤 + 字段类型支持 说明及举例代码(query参数及字段类型强制转换)
332 0
|
7月前
|
SQL 数据采集 Java
Java【代码分享 02】商品全部分类数据获取(建表语句+Jar包依赖+树结构封装+获取及解析源代码)包含csv和sql格式数据下载可用
Java【代码分享 02】商品全部分类数据获取(建表语句+Jar包依赖+树结构封装+获取及解析源代码)包含csv和sql格式数据下载可用
82 0
|
XML 数据格式
FastReport自动提取表的逻辑
FastReport自动提取表的逻辑
|
算法 前端开发
前端算法-Excel表的列名称
前端算法-Excel表的列名称
|
分布式计算 Java 关系型数据库
导入 Import--增量导入--Lastmodified 模式(附加数据)| 学习笔记
快速学习 导入 Import--增量导入--Lastmodified 模式(附加数据)
125 0
ArcGIS的批量操作值提取至点并批量导出到excel数据操作
ArcGIS的批量操作值提取至点并批量导出到excel数据操作
907 0
ArcGIS的批量操作值提取至点并批量导出到excel数据操作
|
SQL JSON 数据处理
DLA SQL技巧:行、列转换和JSON数据列展开
1. 简介 在数据库SQL处理中,常常有行转列(Pivot)和列转行(Unpivot)的数据处理需求。本文以示例说明在Data Lake Analytics(https://www.aliyun.com/product/datalakeanalytics)中,如何使用SQL的一些技巧,达到行转列(Pivot)和列转行(Unpivot)的目的。
4812 0