从压缩文件将数据导入SQL Server

介绍 (Introduction)

I have seen many organizations receive data from various sources and import into SQL Server. You might receive data in various formats and want to import into SQL Server. We can prepare a ETL (Extract-Transform-Load) process to import data into the SQL Server. In doing so, might receive data in a compressed file, which helps to send data over the network using a ZIP file format because it reduces the file size significantly. If we are receiving a ZIP file to import into SQL Server, we need to unzip it and then only we can import data. We might need to create a ZIP file as well from the existing files.

我已经看到许多组织从各种来源接收数据并将其导入SQL Server。 您可能会收到各种格式的数据，并想导入到SQL Server中。 我们可以准备一个ETL（提取，转换，加载）过程，以将数据导入SQL Server。 这样做可能会接收压缩文件中的数据，这有助于使用ZIP文件格式通过网络发送数据，因为它可以大大减小文件大小。 如果我们接收到要导入到SQL Server的ZIP文件，则需要解压缩该文件，然后才可以导入数据。 我们可能还需要从现有文件中创建一个ZIP文件。

总览 (Overview)

In this article, we will first look out the following tasks

在本文中，我们将首先找出以下任务

• Prepare a compressed file using an SSIS package
  使用SSIS包准备压缩文件
• Unzip files using SSIS package
  使用SSIS包解压缩文件
• Import data into SQL Server tables using a ZIP file
  使用ZIP文件将数据导入SQL Server表

一步步 (Step-by-step)

We have the following excel file, and we want to ZIP the file using the SSIS package.

我们有以下excel文件，并且我们想使用SSIS包对文件进行ZIP处理。

We need to do the following steps to ZIP this file:

我们需要执行以下步骤来压缩该文件：

We need to open Visual Studio 2017 with SQL Server Data Tools to prepare the SSIS package. If we do have it installed, you can go to Install SSDT with Visual Studio 2017 and download required setup files. We need to install SQL Server Integration Services feature during the installation.

我们需要使用SQL Server数据工具打开Visual Studio 2017以准备SSIS包。 如果已安装，则可以转到使用Visual Studio 2017安装SSDT并下载所需的安装文件。 在安装过程中，我们需要安装SQL Server Integration Services功能。

In Visual Studio 2017, go to File -> New -> Project.

在Visual Studio 2017中，转到文件->新建->项目。

It opens the new project wizard. In Business Intelligence templates, click on Integration Services and Integration Service Project.

它打开新的项目向导。 在Business Intelligence模板中，单击Integration Services和Integration Service项目。

As shown in the image above, we can provide the SSIS package name and location to create the SSIS package solution. It creates a solution in the directory.

如上图所示，我们可以提供SSIS包名称和位置来创建SSIS包解决方案。 它在目录中创建一个解决方案。

In the Control Flow, drag Execute Process Task in SSIS configuration.

在“控制流”中，拖动SSIS配置中的“ 执行过程任务” 。

Right click on the Execute Process Task and Rename it to File ZIP Task.

右键单击Execute Process Task并将其重命名为File ZIP Task 。

We can see renamed task in the following image.

我们可以在下图中看到重命名的任务。

It opens the Execute Process Task Editor.

它打开执行流程任务编辑器 。