Excel批量导入SSIS并将包发布到SQL Server

### Excel批量导入SSIS并将包发布到SQL Server #### 一、SSIS批量导入Excel文件的实现方法 本文档详细介绍了如何使用SQL Server Integration Services (SSIS)来批量导入Excel文件，并将这些SSIS包发布到SQL Server的过程。这对于处理大量的Excel数据文件尤其有用。 ### 二、准备工作 在开始之前，确保已经安装了以下软件： - **Microsoft SQL Server 2005** 或更高版本 - **Microsoft Visual Studio 2005** 或更高版本 - **SQL Server Business Intelligence Development Studio**（如果使用的是SQL Server 2005） ### 三、创建测试Excel文件 1. **创建测试Excel文件**：在硬盘上创建一个目录，例如`F:\Excel`，并在该目录下创建多个具有相同结构的Excel文件。假设每个文件都有`A`, `B`, `C`, `D`四个字段。 2. **复制文件**：为了测试批量导入功能，可以复制这些文件多次，确保有足够的样本文件用于批量处理。 ### 四、创建SSIS包 接下来，我们需要创建一个新的SSIS包，以便实现批量导入功能。 1. **新建商业智能项目**：启动Microsoft Visual Studio 2005或SQL Server Business Intelligence Development Studio，新建一个商业智能项目。 2. **添加Foreach循环容器**：从工具箱中拖拽一个Foreach Loop Container到设计面板。 3. **配置容器**： - 编辑容器，设置遍历目录参数。例如，设置为`F:\Excel`，以便遍历该目录及其子目录中的所有Excel文件。 - 新建一个映射变量，用于存储遍历过程中的文件路径。 - 在编辑器中指定变量名称，如`xlspath`。 4. **添加数据流任务**：向容器中添加一个数据流任务。 5. **配置数据流**： - 添加Excel Source组件，并选择其中一个Excel文件作为示例。 - 选择需要导入的Sheet。 - 添加OLE DB Destination组件，并将其与Excel Source组件连接起来。 - 配置OLE DB Destination组件，选择一个已存在的SQL Server表作为目标。 - 如果还没有创建表，则需要先在SQL Server中创建一个与Excel结构相同的表。例如，可以创建如下所示的表： ```sql CREATE TABLE tt ( A VARCHAR(100), B VARCHAR(100), C VARCHAR(100), D VARCHAR(100) ); ``` - 映射Excel字段与表中的列。 6. **使用变量**：编辑Excel连接管理器的属性，将变量`xlspath`映射到Excel文件路径。 ### 五、配置变量 1. **编辑Expressions属性**：在Expressions属性编辑列表中，选择ExcelFilePath，然后设置其值为用户变量`::xlspath`。 2. **解决错误**：可能会遇到SSIS的警告错误。此时需要在容器的属性窗口中设置`DelayValidation`为`True`，以延迟验证直到运行时才进行。 ### 六、运行测试 完成上述配置后，可以按F5键运行测试。这将触发SSIS包，遍历指定目录下的所有Excel文件，并将数据批量导入到SQL Server中。 ### 七、发布SSIS包到SQL Server 1. **打包与发布**：将创建好的SSIS包打包，并发布到SQL Server的MSDB数据库或其他指定位置。 2. **使用SQL Server作业**：可以利用SQL Server Agent中的作业功能，定期自动执行SSIS包，实现自动化导入。 ### 八、解决发布错误 在发布SSIS包到SQL Server时，可能会遇到以下错误： - **代码**: 0xC0016016 - **源**: 说明: 无法解密受保护的XML节点“DTS:Password”，错误为0x8009000B“该项不适于在指定状态下使用。”。可能您无权访问此信息。当发生加密错误时会出现此错误。请确保提供正确的密钥。 **解决方法**： 1. **修改作业属性**：右键点击作业 -> 属性 -> 步骤 -> 选择步骤并点击编辑。 2. **选择执行选项**：在常规选项卡中，选择“执行选项” -> 选中“使用32位运行时”。 3. **原因**：64位环境下不支持Excel连接管理器，因此需要设置为32位运行模式。 通过以上步骤，不仅可以成功实现Excel文件的批量导入，还可以避免发布过程中可能遇到的问题。这种方法非常适合需要频繁处理大量Excel数据的企业环境。