How to Upload a Zip File to My Server

Introduction

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

Overview

In this article, nosotros will showtime look out the following tasks

• Ready a compressed file using an SSIS package
• Unzip files using SSIS bundle
• Import data into SQL Server tables using a ZIP file

Step-by-step

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

Nosotros need to exercise the following steps to Nil this file:

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

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

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

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

In the Control Menstruum, drag Execute Process Job in SSIS configuration.

Right click on the Execute Procedure Task and Rename information technology to File Zilch Job.

We can run across renamed task in the following epitome.

It opens the Execute Process Task Editor.

In this article, we are going to use the open up source tool with compression to prepare the compressed files into the .7z extension. It is available with the Windows past default. Yous can find this utility in C:\Program Files\7-Zippo directory.

In the Execute Procedure Task Editor, click on Procedure.

In the Executable, we demand to specify the path for the 7z.exe. You can browse to the directory or paste the consummate path here as shown in the following prototype.

In the Arguments, nosotros need to blazon post-obit control.

a -t7z "C:\sqlshack\Typhoon articles\Data\Inventory.zip" "C:\sqlshack\Draft articles\Data\Inventory.xlsx"

Let me explain these arguments.

• A – This argument specifies a file to add to the archive
• t7z – It specifies the archive type. In this article, we want to create a t7z annal file
• Destination zip filename and directory: In the next statement, specify the Zip file proper noun along with the absolute path that we desire to create
• Source file path and file name: In the last argument, we need to specify the absolute file path for the file we want to ZIP

In the Working directory, we can specify the directory for 7z executable file.

Click OK, and Nosotros tin see that File ZIP Task is ready to execute.

Press F5 or click on the Commencement to run this File ZIP Task. Information technology opens a command prompt with the 7z.exe pinch utility. Yous can encounter the Nada file progress status as well in this command prompt. You lot might non notice this command prompt if file size is small.

We tin see a green tick icon on File Naught Task one time it is successfully executed.

Click on Progress, and you can see progress message of the File ZIP task.

Now permit us go to the destination path that we specified in argument. In the following prototype, you lot can see Inventory.nil file for the excel file. You lot tin notice the size difference besides.

• Microsoft Excel file size: five,192 KB (Without compression)
• Compressed files size: 582 KB

Now let u.s.a. place two files in the same folder.

Nosotros want to create a Zilch file containing both the files in information technology. We do not need to create two Nada task in the SSIS packet. Open the File Nada Task in the SSIS package nosotros created earlier. We can specify the below statement. In this argument, we specified the source file paths 'C:\sqlshack\Draft articles\Data\*.*"' . It takes all the files in the source path and zips them into a single file.

a -t7z "C:\sqlshack\Draft manufactures\Information\CompressDemo.nix" "C:\sqlshack\Draft manufactures\Data\*.*"

Now execute the package over again. In the command prompt window, we tin can see information technology is adding 2 files into an archive.

Let the states go to the destination path, and you tin see a unmarried compressed file.

For the verification purpose, right click on the compressed ZIP file and extract the file using the option Extract to 'CompressDemo\'

Nosotros can see both the files in the extracted file as shown below.

Extract files using the SSIS Package

We take explored the process to create a compressed file using a SSIS package. Suppose we received a Zilch file to import into SQL Server tabular array. This ZIP file contains an Excel file. We cannot import data from a Aught file directly into SQL Server. We demand to extract the file first and then import the excel file into SQL Server and extract the files into a different folder using the SSIS package only.

We have the post-obit Goose egg file into our source path.

Now let us create the SSIS package into ii steps.

1. Unzip the file
2. Import the file into the SQL Server.

We tin can unzip a file using the execute process task. Rename the execute procedure task equally File Unzip Task.

Double click on File Unzip Task and in the argument, we need to specify the argument for extract the ZIP file.

e "C:\sqlshack\Draft articles\Data\Inventory.zip"

In this argument, e instructs to extract the files.

Information technology extracts files in the working directory folder. Let us execute this task to see if Unzip is working fine or not. Information technology unzips the files very quickly. Yous might non notice the extract process since it completes in a fraction of second. It might take longer if the compressed file size is enormous.

In the Visual Studio, nosotros tin see that File Unzip task is also completed successfully.

Let get to the working directory binder, and as per the following screenshot, we can see the extracted file is present here.

Import compressed file data into SQL Server

Nosotros desire to load data from Zippo file into SQL Server tabular array, therefore, add a Data Flow Task and connect it with the File Unzip Task.

Rename Information Flow Task to Import Excel into SQL Server.

Double click on the Import Excel into SQL Server. It opens data flow page in which nosotros demand to ascertain the source and destination connections.

In the Excel Source, specify the excel file path. This path should be the working directory in Unzip File Task.

Click Ok and specify the sheet name.

We tin can view these columns from the right side bill of fare option Columns.

Click Ok, and we tin can see Excel source in the post-obit prototype.

Now add an OLE DB destination and connect information technology to the Excel Source.

We need to configure the OLE DB Destination now. Double click on this destination and we demand to specify the OLE DB connection manager. If we do not accept the existing connection, click on New and specify the destination connexion manager.

If we have a tabular array already present, we can select tabular array from the driblet-down list. If destination table does not be, click on New in front of Name of the table or the view. It automatically shows script to create the destination table with valid information types.

Specify table name as per the requirement.

Click OK, and we can run across that tabular array as highlighted in the post-obit screenshot.

Now click on Mappings to practise the mapping betwixt source and destination. Information technology automatically creates mapping; however, we tin change mapping if required.

Click OK, and we tin run into the configured data menstruation job.

Salvage the package and verify that the source binder contains the Nix file.

We can see the below configured to Control Flow task.

Allow u.s. execute this packet at present. We can track each task status in Visual Studio.

We tin can run across that FileUnZip Chore is completed successfully. We tin can verify this from the working directory. Nosotros take the unzipped file every bit shown in the following image.

We can see that the parcel is executed successfully.

Let us go to the SQL Server table in which we imported information from a compressed excel file.

SELECT count ( * ) as NumberofRecords FROM [ FileStreamDemoDB_test ] . [ dbo ] . [ DemoDatafromZip ]

In the following screenshot, we can see that the number of records is 1046801. It shows that nosotros take successfully imported information using the compressed excel file into the SQL Server tabular array.

Conclusion

In this article, we explored the style to import data from a compressed file into SQL Server tables. It provides u.s.a. with flexibility, avoids manual task for unzipping the file, and then imports it. In the adjacent article, nosotros volition explore the apply of Python for importing Zero file data into SQL Server.

Table of contents

• Writer
• Contempo Posts

Howdy! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, ready related problems, and Operation Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest costless online collections of articles on a single topic, with his l-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions accolade in 2020.

Personal Blog: https://www.dbblogger.com
I am ever interested in new challenges so if you demand consulting help, attain me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

jonesangroys.blogspot.com

Source: https://www.sqlshack.com/importing-data-into-sql-server-from-compressed-files/

Share this post