SQL Server BI Step by step 2 用SSIS简单的导入和导出

 Step by step 2   用SSIS简单的导入和导出

一、      新建一个Integration Service项目

打开vs.net 2005 ,”文件”——“新建”——选择商业智能项目——选中模板中的Integration Service项目——输入项目名称

 

 

 

二、      数据库表导出excel

 创建好后,我们就在默认的Package包中进行设计【详解1】。

 

 

 SSIS 设计器界面

• 新建数据源
  首先向控制流中添加一个控制流组件(Data Flow Task),双击进入数据流.从左边的工具箱中选OLEDB数据源(OLE DB Source),新添加的,标示红色的错误提示。双击设置该组件，配置其连接管理器。下图为新建连接管理器。

 

新建数据源的连接管理器

• 配置OLE DB源
  选定数据库后设置直接访问的Produt表,当然在这里也可以通过sql语句获取数据源,其中可以调用存储过程,另外还可以通过变量设置的方式,可以把表或者视图的名称,或者sql命令直接放在变量中

 

配置好的OLE DB连接器

配置好以后，红色的错误提示已经不存在了。我们再添加一个目标数据源,我们将数据导出成Excel数据格式,所以选择Excel Destination,同样,双击对Excel连接管理器进行配置,配置好文件名称和路径(此处选择在首行显示列名,这样会从第二行开始才开始显示数据).

 

 

拖入Excel Destination组件后

•  配置Excel Destination
  直接双击Excel Destination组件，报错。因为没有把数据源与目标源连接。

 

 

 

 

 

 

• 配置连接器,新建表

 

 

点击从左边切换到映射栏目，对数据流中元数据的列和Excel表中的列一 一映射。因为刚才是自动创建的Excel工作表,所以默认是根据名称对应的.这样我们就完成了对Product产品表的导出。在右边的解决方案中,右击执行包或运行（F5）,可以看到绿色执行成功.

 

 

 

映射后

 

 执行成功 导出数据

 

三、 用SQL语句查询出的数据进行导出
数据源端：把OLE  DB数据源的数据访问模式设置为SQL命令，然后输入查询语句。

 

 

在Excel Destination端：设置对应的表和映射。再次执行时就会发现生成的Excel表中已经只包含了Color=’Black’的数据(注意,如果你刚才所有的数据的Excel文件没有删除,你会发现这次导出的数据是添加到了上次的数据的后面).
 

---

 

 四、 excel导入数据库表
接下来,我们再将刚才导出的产品数据导入Product表中.再添加Excel Source和OLE DB Destination,其实就是做和导出相反的过程.(注意：Product表中存入新导入的数据，要备份下数据库哟).
现在源是：Excel Source。
目标为：OLE DB Destination。设置时注意：数据访问模式为 [表或视图]。

 

确定后发现有红色错误提示,这是因为数据库中Product产品是以ProductID作为主键标识的,所以不能够插入,我们从映射中设置将ProdutID字段删除,同样的,我们需要将rowguid字段忽略,这两个字段都是数据库中自动生成的.  

 

再次确认后会发现已经没有错误,只剩下了黄色的警告,我们现在暂时不理会这个警告.我们把刚才生成的Excel文件删除,重新配置Excel连接管理器生成新的空Excel文件(或者把生成的Excel中的数据删除),然后再次运行包。
你会发现,刚才的数据导出仍然正常,但是数据导入却显示的是没有导入任何数据,这是因为在数据中刚才的数据导出和导入并没有先后,所以他们是同步执行的,执行导入时发现里面的数据为空,所以没有导入成功任何数据.
试验：尝试着把数据导入的操作直接放在Excel Destination后面是失败的,当Excel Destination就是数据流目标,意味着整个流程的结束.(此时Excel Destination中只可定义一个错误输出).
因此，我们再添加一个数据流任务——数据导出流,将第一个数据流任何指向这个(鼠标拖拉绿色箭头):

 

 

导入导出都配置好后，清空Excel数据再次运行包,怎么还是有错误,”这是为什么呢?” ,呵呵,看下面的错误信息:
错误: 0xC0202009，位于 数据导入, OLE DB 目标 [960]: 出现 OLE DB 错误。错误代码: 0x80040E2F。
已获得 OLE DB 记录。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 说明:“语句已终止。”。
已获得 OLE DB 记录。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 说明:“不能在具有唯一索引 'AK_Product_ProductNumber' 的对象 'Production.Product' 中插入重复键的行。”。

因为，在Product表中，Name,ProductNumber,rowguid字段中的数据都具有唯一约束性！不能插入重复的纪录。但是rowguid是数据库自动生成，因此在映射时，忽略即可。（备注：查看唯一索引的目的）。

Product  表

 

解决方法：我们暂时通过添加一次转换,在刚才的数据流源和数据流目标中间再添加一个派生列组件(Derived Column ,Updates column values using expressions).添加一个新列NewProductNumber,在Excel中的产品编号后面加xxh,组成新的产品编号,同样我们派生出一个新的产品名称Name,因为在数据库中同样也有唯一性约束.(注意：添加的数据xxh，必须使组成新纪录要和表中其他纪录有区别！例如添加“1”，也许就会和表中纪录重复，而造成导入的立刻终止。只导入一部分数据).

同时,我们还要修改OLE DB目标中的映射,将目标列的ProductNumber对应的输入列ProductNumber改成刚才派生的NewProductNumber.将目标列的Name对应的输入列Name改成刚才派生的NewName.清空Excel数据,再次运行包,都变成了绿色,执行全部成功

 

清空Excel数据,再次运行包,都变成了绿色,执行全部成功.

 

 

通过对比数据库,确实已经成功的添加进了504行新的数据.
细心的可能会发现,控制流中的数据导出和导入两个组件其实是前后约束条件的,也就是必须数据导出必须成功了才会执行导入(后面会介绍).另外,所谓的数据导出并不会局限于数据库的导入和导出,数据流源和数据流目标都可以是Excel,Flat File(txt,csv),XML,DataReader等连接.也就是说同样可以实现txt导入Excel,或者是XML导入数据库等操作.
好了,今天是SSIS的一个入门,我们利用SSIS实现了数据的导入和导出,把Product表中的数据导出成Excel,然后对产品编号和名称两个字段经过派生的功能进行转换再导入到数据库中,这其中我们认识了控制流和数据流,数据流源和数据流目标,并且还引入了派生列组件来实现我们的导入

问题：excel导入SQL中发现文本截断的错误：

 

excel导数据到表，总是发生错误：数据流中Name长度为255的列中数据插入数据库Name中长度为25的列，数据可能截断。
这说明：是插入了重复值，违反了唯一性。
-----------------------------------------------------------错误报告-------------------------------------------------------------------
错误: 0xC0202009，位于 数据导出, OLE DB 目标 [520]: 出现 OLE DB 错误。错误代码: 0x80040E2F。
已获得 OLE DB 记录。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 说明:“语句已终止。”。
已获得 OLE DB 记录。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 说明:“不能在具有唯一索引 'AK_Product_ProductNumber' 的对象 'Production.Product' 中插入重复键的行。”
--------------------------------------------------------------END-------------------------------------------------------------------------

 

 

项目step1---4源代码文件：版本为SQL 2005，运行代码前还需要安装ExceL应用程序

/Files/cocole/Step1-4Sql05.rar

 

作者：悟空的天空（天马行空）
出处：http://www.cnblogs.com/cocole/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
 

转载于:https://www.cnblogs.com/cocole/archive/2011/05/27/2060120.html