Ssis - Export Csv File and Upload to Ftp

Contents

• 1 Introduction
• 2 Method-1 : Upload SQL information to Azure Blob in Two steps
  • 2.1 Step-1: Configure Source Connection in Consign CSV Task
  • 2.2 Step-2: Compress CSV Files in SSIS ( GZIP format – *.gz )
  • 2.3 Step-3: Split CSV files by row count or information size in SSIS
  • 2.4 Footstep-4: Upload CSV files to Azure Blob – Using multi threaded option
• 3 Method-ii : Upload SQL data to Azure Blob without local stage (One step)
• four Method-3 : Using Azure Blob destination – Generate Azure Hulk file from any source
• five Decision
  • 5.1 Share this:
  • 5.2 Like this:
  • v.3 Related

Introduction

In this blog mail you will see how easy it is to load big corporeality of information from SQL Server to Azure Hulk Storage using SSIS. Nosotros will export / compress information to multiple files.

For demo purpose we volition use SQL Server as relational source but y'all can use aforementioned steps for any database engine such as Oracle, MySQL, DB2. In this postal service we volition use Export CSV Task and Azure Hulk Storage Chore  to reach desired integration with Azure Hulk with drag and drop approach. You can as well consign JSON or XML data to Azure Blob using same techniques (Use Export JSON Task  or Export XML Job ).

Our goal is to achieve post-obit things

• Extract large amount of data from SQL Server Table or Query and export to CSV files
• Generate CSV files in compressed format (*.gz) to speedup upload and save data transfer cost to Azure
• Split CSV files by row count
• Upload data to Azure Blob using highly parallel manner for maximum speed

At that place are three unlike means y'all can achieve data consign to Azure Blob using SSIS.

1. Method-one (Fastest): Use two step procedure (First export SQL Server data to local files using Export Task and then upload files to Azure using  Azure Hulk Storage Chore  )
2. Method-two (Slower): Use Consign Task with Azure Blob Connection as Target rather than save to Local files.
3. Method-three (Slower): Utilize Data flow components like Azure Blob Destination for CSV  (for JSON / XML  use Method1 or Method2)

Each method has its own advantage / disadvantage. If you prefer to upload / shrink / split large corporeality of information then we recommend Method#ane (Two steps). If you lot have not very huge dataset then you can utilize Method#two or Method#3. For Terminal method you can only use CSV export option (we don't have JSON/ XML Destination for Azure Blob yet – we may add in hereafter)

Screenshot of SSIS Package

Excerpt SQL Server Information to CSV files in SSIS (Majority export) Split / GZip Compress / upload files to Azure Blob Storage

Method-1 : Upload SQL data to Azure Blob in Ii steps

In this section nosotros will encounter offset method (recommended) to upload SQL data to Azure Blob. This is the fastest approach if yous have lots of information to upload.  In this approach we kickoff create CSV files from SQL Server data on local disk using SSIS Export CSV Task. After that in 2nd stride we upload all files to Azure Blob using SSIS Azure Storage Job.

Pace-i: Configure Source Connection in Consign CSV Chore

To extract data from SQL Server you can use Export CSV Chore. It has many options which makes it possible to split large amount of information into multiple files. You tin specify single tabular array or multiple tables as your data source.

For multiple tabular array use vertical bar. e.k.  dbo.Customers|dbo.Products|dbo.Orders. When you export this it will create iii files ( dbo.Customers.csv , dbo.Products.csv, dbo.Orders.csv )

Steps:

1. Drag ZS Consign CSV Task from Toolbox
2. Double click task to configure
3. From connection drib downward select New connection option (OLEDB or ADO.net)
4. One time connectedness is configured for Source database specify SQL Query to extract information as below

   

   Consign SQL Server Tabular array or Query as CSV file (Bulk export in SSIS)

5. Now go to target tab. Here you lot can specify full path for file. eastward.m. c:\ssis\temp\azure\cust.csv

Stride-two: Compress CSV Files in SSIS ( GZIP format – *.gz )

Above steps will export file equally CSV format without splitting or pinch. But to shrink file once exported you can get to Target tab of Export CSV Task and cheque [Shrink file to *.gz format] option.

Shrink exported SQL Server data files to GZip ( *.gz) in SSIS Export CSV Task

Footstep-three: Split CSV files past row count or data size in SSIS

Now lets look at how to split exported CSV files into multiple files and then we tin upload many files in parallel. Goto Separate Options and check [Enable Split up by Size/Rows]

Using SSIS Split Exported CSV files (Separate by row count or size)

Pace-four: Upload CSV files to Azure Hulk – Using multi threaded option

Now final thing is use Azure Storage Task to upload files to Azure.

Steps:

1. Drag ZS Azure Storage Task from SSIS toolbox
2. Double click Azure Storage Task to configure it
3. Specify Action = UploadFilesToAzure
4. Specify Source file path (or pattern) e.thousand. c:\SSIS\temp\azure\*.*
5. Now in the Target connexion dropdown click [New]
6. When Connection UI opens Enter your Business relationship, Hole-and-corner Key (Leave all other parameters default if y'all non sure)
7. Click Test and close connexion UI
8. On the Target path on Azure Storage Chore enter your bucket and folder path where you want to upload local files. For example your container proper noun is bw-east-1 and binder is sqldata then enter as beneath
   bw-east-1/sqldata/
9. Click ok and Run bundle to test total packet

Method-2 : Upload SQL data to Azure Blob without local stage (One stride)

Now allow'southward modify previous approach little scrap to send SQL server data directly to Azure Blob without any Landing area on local deejay.  Export CSV Task , Consign JSON Task  and Consign XML Task all of them supports Azure Blob / Azure Hulk and Secure FTP (SFTP) connection every bit target (Only available in Pro Edition). We will apply this feature in following section.

This arroyo helps to avert any local disk need and it may be useful for security reason for some users. However drawback of this approach is, it wont use parallel threads to upload large corporeality of information like previous method.

Post-obit change will be needed on Consign job to upload SQL data directly to Azure / FTP or Azure storage.

Export SQL data to multiple files to Azure Blob, Amazon S3, Secure FTP (SFTP) in Stream Mode using SSIS. Configure Compress GZip, Overwrite, Split Options

Method-3 : Using Azure Blob destination – Generate Azure Hulk file from whatever source

Now allow'due south wait at third approach to save data from whatsoever SSIS Source to Azure Blob file. Advantage of this arroyo is you are not express to few source options provided by Export CSV Job. If you have complex data transformation needed in Data Period earlier sending data to Azure then use this approach.  Nosotros will utilize Azure Blob Destination for CSV as below

1. Drag SSIS Data flow task from toolbox
2. Create necessary source connection (east.g. OLEDB connection)
3. Create Azure Blob Connection (Right click in Connection Managers console in bottom and click New connection and select ZS-Azure-STORAGE type )
4. Once connection managers are created Go to data flow designer and Drag OLEDB Source
5. Configure OLEDB Source to read desired data from source system (e.g. SQL Server / Oracle)
6. Once source is configured elevate ZS Azure Blob CSV File Destination from SSIS toolbox
7. Double click Azure Destination and configure equally below
   1. On Connection Managers tab select Azure Connexion (Nosotros created in before section).
   2. Properties tab configure like below screenshot
   3. On Input Columns tab select desired column you like to write in the target file. Your name from upstream will be taken equally is for target file. So make sure to name upstream columns correctly.
   4. Click OK to save UI
8. Execute package and cheque your Azure Bucket to see files got created.

Loading SQL Server data into Azure Container Files (Split, Shrink Gzip Options) – SSIS Azure Blob CSV File Destination

Conclusion

In this mail service you lot have seen how easy information technology is to upload / archive your SQL Server data (or any other RDBMS data) to Azure Blob Storage in few clicks. Endeavour SSIS PowerPack for gratuitous and find out yourself how easy information technology is to integrate SQL Server and Azure Blob using SSIS.

millsmanned.blogspot.com

Source: https://zappysys.com/blog/ssis-data-load-sql-server-azure-blob-split-files-gzip/

Share this post