Use Ssis to Extract Data From a Sql Table Into a Csv File and Upload to Sftp Automaticlly

Contents

• 1 Introduction
• two Method-1 : Upload SQL data to Secure FTP in Two steps
  • 2.1 Footstep-i: Configure Source Connection in Consign CSV Task
  • 2.two Step-two: Compress CSV Files in SSIS ( GZIP format – *.gz )
  • 2.three Footstep-3: Dissever CSV files by row count or data size in SSIS
  • 2.4 Step-iv: Upload CSV files to Secure FTP – Using multi threaded option
• 3 Method-2 : Upload SQL data to Secure FTP without local phase (One step)
• four Method-3 : Using Secure FTP CSV File Destination – Generate Secure FTP file from any source
• 5 Conclusion
  • 5.i Share this:
  • 5.two Like this:
  • 5.3 Related

Introduction

In this web log postal service you will see how easy it is to load large amount of data from SQL Server to FTP/SFTP. SSIS Information Load – SQL Server to FTP/SFTP (Dissever Files, GZip).

For demo purpose we will employ SQL Server as relational source merely you can use same steps for whatever database engine such as Oracle, MySQL, DB2. In this postal service we will use Export CSV Job and ZS Secure FTP Task (SFTP, FTP, FTPS) to achieve desired integration with FTP/SFTP/FTPS with drag and drop approach. Y'all can also export data to JSON or XML or Excel file to FTP/SFTP/FTPS using same techniques (Use Export JSON Task  or Export XML Chore or Consign Excel Task ).

Our goal is to achieve following things

• Extract large corporeality of data from SQL Server Table or Query and export to CSV files
• Generate CSV files in compressed format (*.gz) to speedup upload
• Split CSV files past row count
• Upload information to FTP/SFTP/FTPS using highly parallel manner for maximum speed

There are three different ways you can achieve data consign to Secure FTP using SSIS.

1. Method-1 (Fastest): Use ii step process (First export SQL Server data to local files using Export Task so upload files to Secure FTP using  ZS Secure FTP Task (SFTP, FTP, FTPS) )
2. Method-2 (Slower): Utilize Export Job with Secure FTP Connexion equally Target rather than save to Local files.
3. Method-three (Slower): Utilize Data catamenia components similar Secure FTP CSV File Destination  (for JSON / XML / EXCEL   use Method1 or Method2)

Each method has its own advantage / disadvantage. If you prefer to upload / compress / carve up large amount of information so we recommend Method#one (2 steps). If y'all take non very huge dataset then you can apply Method#ii or Method#3. For Last method you lot tin only use CSV export option (nosotros don't have JSON/ XML / EXCEL Destination for Secure FTP yet – we may add in future)

Screenshot of SSIS Bundle

Extract SQL Server Data to CSV files in SSIS (Bulk export) Split / GZip Compress / upload files to FTP/SFTP/FTPS

Method-1 : Upload SQL data to Secure FTP in Two steps

In this department we will run into first method (recommended) to upload SQL information to Secure FTP. This is the fastest arroyo if y'all have lots of data to upload.  In this approach we starting time create CSV files from SQL Server data on local disk using SSIS Export CSV Task. After that in 2d pace nosotros upload all files to Secure FTP Storage using ZS Secure FTP Job (SFTP, FTP, FTPS).

Step-one: Configure Source Connection in Export CSV Chore

To extract data from SQL Server you tin utilise Export CSV Job. Information technology has many options which makes information technology possible to carve up large amount of information into multiple files. Y'all can specify single table or multiple tables as your information source.

For multiple table utilise vertical bar. e.g.  dbo.Customers|dbo.Products|dbo.Orders. When yous export this it will create iii files ( dbo.Customers.csv , dbo.Products.csv, dbo.Orders.csv )

Steps:

1. Elevate ZS Export CSV Chore from Toolbox
2. Double click task to configure
3. From connection drib downward select New connection option (OLEDB or ADO.net)
4. Once connection is configured for Source database specify SQL Query to excerpt information every bit below

   

   Export SQL Server Table or Query as CSV file (Majority export in SSIS)

5. Now go to target tab. Here you tin can specify full path for file. due east.yard. c:\ssis\temp\cust.csv

Pace-ii: Shrink CSV Files in SSIS ( GZIP format – *.gz )

Above steps will export file as CSV format without splitting or compression. But to compress file in one case exported you can get to Target tab of Consign CSV Task and bank check [Compress file to *.gz format] option.

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

Step-3: Carve up CSV files by row count or data size in SSIS

Now lets await at how to split exported CSV files into multiple files and so nosotros can upload many files in parallel. Goto Divide Options and check [Enable Split by Size/Rows]

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

Step-four: Upload CSV files to Secure FTP – Using multi threaded option

Now final matter is use ZS Secure FTP Task (SFTP, FTP, FTPS) to upload files to Secure FTP.

Steps:

1. Drag ZS Secure FTP Task (SFTP, FTP, FTPS) from SSIS toolbox
2. Double click ZS Secure FTP Task (SFTP, FTP, FTPS) to configure information technology
3. Specify Action = Upload Files To FTP Server
4. Specify Source file path (or pattern) east.thousand. c:\SSIS\temp\*.*
5. Now in the Target connection dropdown click [New]
6. When Connection UI opens Enter your Account, Underground Key (Exit all other parameters default if you non sure)
7. Click Test and shut connectedness UI
8. On the Target path on ZS Secure FTP Job (SFTP, FTP, FTPS) enter your bucket and folder path where you want to upload local files. (For instance:/datafolder/sqldata/)
9. Click ok and Run bundle to test total package

Method-2 : Upload SQL data to Secure FTP without local phase (One step)

Now let's modify previous approach little scrap to send SQL server data directly to FTP/SFTP/FTPS without any Landing area on local deejay.  Export CSV Task , Export JSON Task , Export Excel Chore and Export XML Task all of them supports Azure Blob / Amazon S3 and Secure FTP (SFTP) connection as target (Only available inPro Edition). We will use this characteristic in post-obit section.

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

Following change will be needed on Consign task to upload SQL information directly to Azure / FTP or Amazon storage.

Consign SQL data to multiple files to Amazon S3, Azure Blob, Secure FTP (SFTP) in Stream Way. Compress GZip, Overwrite, Split Options

Method-3 : Using Secure FTP CSV File Destination – Generate Secure FTP file from any source

Now let's wait at third approach to save data from whatever SSIS Source to FTP/SFTP/FTPS file. Advantage of this arroyo is you are not limited to few source options provided by Consign CSV Task. If you have complex data transformation needed in Information Catamenia before sending data to FTP/SFTP/FTPS then use this arroyo.  We will employ Secure FTP CSV File Destination equally beneath

1. Elevate SSIS Data period task from toolbox
2. Create necessary source connexion (east.chiliad. OLEDB connexion)
3. Create Secure FTP Connection (Right click in Connection Managers console in lesser and click New connexion and selectZS-SFTP type )
4. Once connection managers are created Go to data menses designer and Drag OLEDB Source
5. Configure OLEDB Source to read desired data from source system (east.g. SQL Server / Oracle)
6. Once source is configured drag ZS Secure FTP CSV File Destination from SSIS toolbox
7. Double click Secure FTP CSV File Destination and configure equally below
   1. On Connection Managers tab select Secure FTP Connection (Nosotros created in earlier department).
   2. Properties tab configure like below screenshot
   3. On Input Columns tab select desired column yous like to write in the target file. Your proper name from upstream will be taken equally is for target file. So brand sure to proper noun upstream columns correctly.
   4. Click OK to save UI
8. Execute package and check your Secure FTP to run across files got created.

   

   Loading SQL Server data into Secure FTP Files (Carve up, Compress Gzip Options) – SSIS Secure Ftp CSV File Destination

Conclusion

In this post you have seen how easy information technology is to upload / archive your SQL Server data (or any other RDBMS information) to FTP/SFTP/FTPS in few clicks. Try SSIS PowerPack for gratuitous and notice out yourself how easy it is to integrate SQL Server and Secure FTP using SSIS.

kahndired1980.blogspot.com

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