Our goal is to achieve following things<\/p>\n
- \n
- Extract large amount of data from SQL Server Table or Query and export to CSV files<\/li>\n
- Generate CSV files in compressed format (*.gz) to speedup upload and save data transfer cost to Azure<\/li>\n
- Split CSV files by row count<\/li>\n
- Upload data to Azure Blob using highly parallel manner for maximum speed<\/li>\n<\/ul>\n
There are three different ways you can achieve data export to Azure Blob using SSIS.<\/p>\n
- \n
- Method-1 (Fastest)<\/strong>: Use two step process (First export SQL Server data to local files using Export Task<\/a> and then upload files to Azure using\u00a0 Azure Blob Storage Task\u00a0<\/a>\u00a0)<\/li>\n
- Method-2 (Slower)<\/strong>:\u00a0Use\u00a0Export Task<\/a>\u00a0with Azure Blob Connection as Target rather than save to Local files.<\/li>\n
- Method-3 (Slower)<\/strong>: Use Data flow components like Azure Blob Destination for CSV<\/a>\u00a0 (for JSON \/ XML\u00a0 use Method1 or Method2)<\/li>\n<\/ol>\n
Each method has its own advantage \/ disadvantage. If you prefer to upload \/ compress \/ split large amount of data then we recommend Method#1 (Two steps). If you have not very huge dataset then you can use Method#2 or Method#3. For Last method you can only use CSV export option (we don’t have JSON\/ XML Destination for Azure Blob yet – we may add in future)<\/p>\n
Screenshot of SSIS Package<\/strong><\/p>\n
![\"Extract](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/07\/ssis-export-sql-server-data-upload-to-amazon-s3.png\")
<\/a>Extract SQL Server Data to CSV files in SSIS (Bulk export) Split \/ GZip Compress \/ upload files to Azure Blob Storage<\/p><\/div>\n
Method-1 : Upload SQL data to Azure Blob in Two steps<\/h2>\n
In this section we will see first method (recommended) to upload SQL data to Azure Blob. This is the fastest approach if you have lots of data to upload.\u00a0 In this approach we first create CSV files from SQL Server data on local disk using SSIS Export CSV Task<\/a>. After that in second step we upload all files to Azure Blob using SSIS Azure Storage Task<\/a>.<\/p>\n
Step-1: Configure Source Connection in Export CSV Task<\/h3>\n
To extract data from SQL Server you can use Export CSV Task. It has many options which makes it possible to split large amount of data into multiple files. You can specify single table or multiple tables as your data source.<\/p>\n
For multiple table use vertical bar. e.g.\u00a0 dbo.Customers|dbo.Products|dbo.Orders. When you export this it will create 3 files ( dbo.Customers.csv , dbo.Products.csv, dbo.Orders.csv )<\/p>\n
Steps:<\/strong><\/p>\n
- \n
- Drag ZS Export CSV Task from Toolbox<\/li>\n
- Double click task to configure<\/li>\n
- From connection drop down select New\u00a0connection option (OLEDB or ADO.net)<\/li>\n
- Once connection is configured for Source database specify SQL Query to extract data as below\n
![\"Export](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/07\/export-sql-server-table-query-data-to-csv-fast.png\")
<\/a>Export SQL Server Table or Query as CSV file (Bulk export in SSIS)<\/p><\/div><\/li>\n
- Now go to target tab. Here you can specify full path for file. e.g. c:\\ssis\\temp\\azure\\cust.csv<\/li>\n<\/ol>\n
Step-2: Compress CSV Files in SSIS ( GZIP format – *.gz )<\/h3>\n
Above steps will export file as CSV format without splitting or compression. But to compress file once exported you can go to Target tab of Export CSV Task and check [Compress file to *.gz format<\/strong>] option.<\/p>\n
![\"Compress](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/07\/compress-csv-files-in-ssis.png\")
<\/a>Compress exported SQL Server data files to GZip ( *.gz) in SSIS Export CSV Task<\/p><\/div>\n
Step-3: Split CSV files by row count or data size in SSIS<\/h3>\n
Now lets look at how to split exported CSV files into multiple files so we can upload many files in parallel. Goto Split Options and check [Enable Split by Size\/Rows<\/strong>]\n
![\"Using](\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/07\/ssis-export-split-csv-files-sql-data.png\")
<\/a>Using SSIS Split Exported CSV files (Split by row count or size)<\/p><\/div>\n
Step-4: Upload CSV files to Azure Blob – Using multi threaded option<\/h3>\n
Now final thing is use Azure Storage Task<\/a> to upload files to Azure.<\/p>\n
Steps:<\/strong><\/p>\n
- \n
- Drag ZS Azure Storage Task from SSIS toolbox<\/li>\n
- Double click Azure Storage Task\u00a0to configure it<\/li>\n
- Specify Action = UploadFilesToAzure<\/li>\n
- Specify Source file path (or pattern) e.g. c:\\SSIS\\temp\\azure\\*.*<\/li>\n
- Now in the Target connection dropdown click [New]<\/li>\n
- When Connection UI opens Enter your Account, Secret Key (Leave all other parameters default if you not sure)<\/li>\n
- Click Test and close connection UI<\/li>\n
- On the Target path on Azure Storage Task enter your bucket and folder path where you want to upload local files. For example your container name is bw-east-1 and folder is sqldata then enter as below
\nbw-east-1\/sqldata\/<\/strong><\/li>\n- Click ok and Run package to test full package<\/li>\n<\/ol>\n
Method-2 : Upload SQL data to Azure Blob without local stage (One step)<\/h2>\n
Now let’s change previous approach little bit to send SQL server data directly to Azure Blob without any Landing area on local disk.\u00a0\u00a0Export CSV Task<\/a>\u00a0,\u00a0Export JSON Task<\/a>\u00a0 and\u00a0Export XML Task<\/a>\u00a0all of them supports Azure Blob \/ Azure Blob and Secure FTP (SFTP) connection as target (Only available in Pro Edition<\/strong>). We will use this feature in following section.<\/p>\n
This approach helps to avoid 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 amount of data like previous method.<\/p>\n
Following change will be needed on Export task to upload SQL data directly to Azure \/ FTP or Azure storage.<\/p>\n
![\"Export](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/07\/ssis-export-sql-data-to-s3-csv-compress-gzip.png\")
<\/a>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<\/p><\/div>\n
Method-3 : Using Azure Blob destination – Generate Azure Blob file from any source<\/h2>\n
Now let’s look at third approach to save data from any SSIS Source to Azure Blob file. Advantage of this approach is you are not limited to few source options provided by Export CSV Task. If you have complex data transformation needed in Data Flow before sending data to Azure then use this approach.\u00a0 We will use\u00a0Azure Blob Destination for CSV<\/a>\u00a0as below<\/p>\n
- \n
- Drag SSIS Data flow task from toolbox<\/li>\n
- Create necessary source connection (e.g. OLEDB connection)<\/li>\n
- Create Azure Blob Connection (Right click in Connection Managers panel in bottom and click New connection and select ZS-Azure-STORAGE<\/strong> type )<\/li>\n
- Once connection managers are created Go to data flow designer and Drag OLEDB Source<\/li>\n
- Configure OLEDB Source to read desired data from source system (e.g. SQL Server \/ Oracle)<\/li>\n
- Once source is configured drag ZS Azure Blob CSV File Destination<\/a>\u00a0from SSIS toolbox<\/li>\n
- Double click Azure Destination and configure as below\n
- \n
- On Connection Managers tab select Azure Connection (We created in earlier section).<\/li>\n
- Properties tab configure like below screenshot<\/li>\n
- On Input Columns tab select desired column you like to write in the target file. Your name from upstream will be taken as is for target file. So make sure to name upstream columns correctly.<\/li>\n
- Click OK to save UI<\/li>\n<\/ol>\n<\/li>\n
- Execute package and check your Azure Bucket to see files got created.<\/li>\n<\/ol>\n
- Click ok and Run package to test full package<\/li>\n<\/ol>\n
- Now go to target tab. Here you can specify full path for file. e.g. c:\\ssis\\temp\\azure\\cust.csv<\/li>\n<\/ol>\n
- Method-2 (Slower)<\/strong>:\u00a0Use\u00a0Export Task<\/a>\u00a0with Azure Blob Connection as Target rather than save to Local files.<\/li>\n
- Method-1 (Fastest)<\/strong>: Use two step process (First export SQL Server data to local files using Export Task<\/a> and then upload files to Azure using\u00a0 Azure Blob Storage Task\u00a0<\/a>\u00a0)<\/li>\n
![\"Loading](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/07\/ssis-amazon-s3-csv-destination-split-compress-gzip-options.png\")
<\/a>