{"id":6632,"date":"2019-03-23T08:54:48","date_gmt":"2019-03-23T08:54:48","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6632"},"modified":"2019-09-11T11:22:41","modified_gmt":"2019-09-11T11:22:41","slug":"read-sftp-ftp-files-ssis-csv-json-xml","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/","title":{"rendered":"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML)"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-3058 size-full alignleft\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png\" alt=\"\" width=\"100\" height=\"100\" \/><\/a>In our previous blog, we saw <a href=\"https:\/\/zappysys.com\/blog\/ssis-sftp-task-examples-upload-download-move-delete-files-folders\/\" target=\"_blank\" rel=\"noopener\">how to perform SFTP \/ FTP File Operations in SSIS<\/a>. Now in this blog, we will see <strong>How to Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML Format files)<\/strong>. To illustrate, we will use\u00a0<a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\" rel=\"noopener\">ZappySys SSIS PowerPack<\/a>, which includes several tasks to import\/export data from multiples sources to multiple destinations like flat files, Azure, AWS, databases, Office files and more. They are\u00a0Coding free, drag and drop high-performance suite of\u00a0<em>Custom SSIS Components<\/em>\u00a0and\u00a0<em>SSIS Tasks.<\/em> If you like to perform file operations on FTP Files (e.g. Download, Upload, Create, Delete) then <a href=\"https:\/\/zappysys.com\/blog\/category\/ssis\/tasks\/ssis-sftp-task\/\" target=\"_blank\" rel=\"noopener\">check these articles<\/a>.<\/p>\n<p>In nutshell,\u00a0this post will focus on how to Read files from secure FTP Storage (CSV, JSON and XML Files) and load into SQL Server Table in few clicks.<\/p>\n<p><strong>Components Mentioned in this article<\/strong><br \/>\n<div class=\"su-table su-table-alternate\">\n<table style=\"width: 407px;height: 187px\">\n<tbody>\n<tr>\n<td style=\"width: 32.3864px\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i1.wp.com\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/secure-ftp-json-source\/ssis-secure-ftp-json-file-source.png?w=720&amp;ssl=1\" alt=\"SSIS Amazon S3 CSV File Source\" width=\"32\" height=\"32\" \/><\/td>\n<td style=\"width: 358.75px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-json-file-source\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Source for JSON File<\/a><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 32.3864px\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/secure-ftp-xml-source\/ssis-secure-ftp-xml-file-source.png?w=720&amp;ssl=1\" alt=\"SSIS Amazon S3 CSV File Destination\" width=\"32\" height=\"32\" \/><\/td>\n<td style=\"width: 358.75px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-xml-file-source\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Source for XML File<\/a><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 32.3864px\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i2.wp.com\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/secure-ftp-csv-source\/ssis-secure-ftp-csv-file-source.png?w=720&amp;ssl=1\" alt=\"SSIS Amazon S3 XML File Source\" width=\"32\" height=\"32\" \/><\/td>\n<td style=\"width: 358.75px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-csv-file-source\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Source for CSV File<\/a><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 32.3864px\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i1.wp.com\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/secure-ftp-csv-destination\/ssis-secure-ftp-csv-file-destination.png?w=720&amp;ssl=1\" alt=\"SSIS Amazon S3 JSON File Source\" width=\"32\" height=\"32\" \/><\/td>\n<td style=\"width: 358.75px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-csv-file-destination\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Destination for CSV File<\/a><br \/>\n<b><\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h2>Prerequisite<\/h2>\n<ol>\n<li>First, you will need to have SSIS installed<\/li>\n<li>Secondly, make sure to have SSDT<\/li>\n<li>You have obtained FTP\/SFTP Credential.<\/li>\n<li>Finally, do not forget to install\u00a0ZappySys\u00a0<a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\" rel=\"noopener\">SSIS PowerPack<\/a><\/li>\n<\/ol>\n<h2><span id=\"Whats_is_FTP_FTPS_SFTP_FTPSSL\">What is FTP, FTPS, SFTP, FTP\/SSL?<\/span><\/h2>\n<p>Many people still have confusion over various protocols around FTP terminology. Most common terms you hear are as below.<\/p>\n<ul>\n<li>FTP<\/li>\n<li>Classic FTP<\/li>\n<li>Secure FTP<\/li>\n<li>SFTP<\/li>\n<li>FTPS<\/li>\n<li>FTP\/SSL<\/li>\n<li>FTP over SSL<\/li>\n<li>FTP Over SSH<\/li>\n<\/ul>\n<p>You must be overwhelmed by now? So are they all same or different? See below for quick clarification in nutshell.<\/p>\n<p>Mainly there are 2 protocols for FTP and yes they are very different but still used to do Files Transfer to\/from the remote server.<\/p>\n<ol>\n<li><strong>Classic FTP<\/strong>\u00a0\u2013 sometimes referred as just\u00a0<strong>FTP<\/strong>\u00a0(Typically server runs on Port 21)<\/li>\n<li><strong>SFTP<\/strong>\u00a0\u2013 Sometimes referred as\u00a0<strong>Secure FTP<\/strong>\u00a0or\u00a0<strong>FTP over SSH<\/strong>\u00a0(Typically server runs on Port 22)<\/li>\n<\/ol>\n<p>So in short SFTP is not the same as the Classic FTP protocol. SFTP is more secure because traffic is always encrypted. On the other hand, Classic FTP is not encrypted by default but you can use\u00a0<strong>FTP over SSL<\/strong>\u00a0to request encrypted traffic in classic FTP.\u00a0<strong>FTP over SSL<\/strong>\u00a0also referred to as\u00a0<strong>FTPS or FTP\/SSL<\/strong>.<\/p>\n<h2>Getting Started<\/h2>\n<p>In order to start, we will show several examples. ZappySys includes an <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/#cat_secure_ftp\" target=\"_blank\" rel=\"noopener\">SSIS Secure FTP Source for CSV\/JSON\/XML File<\/a> that will help you in reading\u00a0CSV, JSON and XML Files from FTP to the Local machine, Upload files(s) to FTP Storage. It will also support Delete, Rename, List, Get Property, Copy, Move, Create, Set Permission \u2026 and many more operations. Here we are showing you is, How to download files from FTP Storage.<\/p>\n<p>You can connect to your FTP by entering your FTP credentials.<\/p>\n<h2>Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML)<\/h2>\n<p>Let\u00b4s start with an example. First of all, In this SSIS FTP Source for CSV\/JSON\/XML File task example, we will read CSV\/JSON\/XML files from FTP to SQL Server database.<\/p>\n<ol>\n<li>First\u00a0of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit\n<div id=\"attachment_7934\" style=\"width: 470px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/09\/ssis-drag-drop-data-flow-task.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-7934\" class=\"wp-image-7934 size-full\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/09\/ssis-drag-drop-data-flow-task.png\" alt=\"Drag and Drop SSIS Data Flow Task from SSIS Toolbox\" width=\"460\" height=\"155\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/09\/ssis-drag-drop-data-flow-task.png 460w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/09\/ssis-drag-drop-data-flow-task-300x101.png 300w\" sizes=\"(max-width: 460px) 100vw, 460px\" \/><\/a><p id=\"caption-attachment-7934\" class=\"wp-caption-text\">Drag and Drop SSIS Data Flow Task from SSIS Toolbox<\/p><\/div><\/li>\n<li>Now drag and drop relevant Secure FTP for CSV\/JSON\/XML File Task from the SSIS Toolbox.\n<div id=\"attachment_6635\" style=\"width: 1118px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/Drag-and-drop-FTP-source.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6635\" class=\"wp-image-6635 size-full\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/Drag-and-drop-FTP-source.png\" alt=\"Drag and Drop FTP Source\" width=\"1108\" height=\"473\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/Drag-and-drop-FTP-source.png 1108w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/Drag-and-drop-FTP-source-300x128.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/Drag-and-drop-FTP-source-768x328.png 768w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/Drag-and-drop-FTP-source-1024x437.png 1024w\" sizes=\"(max-width: 1108px) 100vw, 1108px\" \/><\/a><p id=\"caption-attachment-6635\" class=\"wp-caption-text\">Drag and Drop FTP<\/p><\/div><\/li>\n<li>Create a connection for Secure FTP Account.\n<div id=\"attachment_6636\" style=\"width: 730px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/create-ftp-connection.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6636\" class=\"wp-image-6636 size-medium_large\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/create-ftp-connection-768x514.png\" alt=\"Create FTP Connection\" width=\"720\" height=\"482\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/create-ftp-connection-768x514.png 768w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/create-ftp-connection-300x201.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/create-ftp-connection-272x182.png 272w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/create-ftp-connection.png 1022w\" sizes=\"(max-width: 720px) 100vw, 720px\" \/><\/a><p id=\"caption-attachment-6636\" class=\"wp-caption-text\">Create FTP Connection<\/p><\/div><\/li>\n<li>Likewise, select the relevant single file to read from FTP in their relevant source of CSV\/JSON\/XML File Task.\n<div id=\"attachment_6539\" style=\"width: 944px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-select-File.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6539\" class=\"wp-image-6539 size-full\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-select-File.png\" alt=\"Select File From Azure Blob Storage\" width=\"934\" height=\"582\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-select-File.png 934w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-select-File-300x187.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-select-File-768x479.png 768w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-select-File-436x272.png 436w\" sizes=\"(max-width: 934px) 100vw, 934px\" \/><\/a><p id=\"caption-attachment-6539\" class=\"wp-caption-text\">Select File From FTP Storage<\/p><\/div><\/li>\n<li>Similarly, we can also\u00a0read the multiple files stored in FTP Storage using wildcard pattern supported e.g. dbo.tblNames*.csv \/ dbo.tblNames*.json \/ dbo.tblNames*.xml in relevant source task\n<div id=\"attachment_6540\" style=\"width: 557px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-set-multiple-Filepath.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6540\" class=\"wp-image-6540 size-full\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-set-multiple-Filepath.png\" alt=\"Use wildcard pattern .* to read multiple files data\" width=\"547\" height=\"178\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-set-multiple-Filepath.png 547w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-Azure-Blob-Source-set-multiple-Filepath-300x98.png 300w\" sizes=\"(max-width: 547px) 100vw, 547px\" \/><\/a><p id=\"caption-attachment-6540\" class=\"wp-caption-text\">Use wildcard pattern .* to read multiple files data<\/p><\/div><\/li>\n<li>We can also read the zip and gzip compressed files also without extracting it in the specific FTP Storage for CSV\/JSON\/XML File Task.\n<div id=\"attachment_6541\" style=\"width: 698px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-azure-blob-storage-source-read-zip-gzip-compressed-files.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6541\" class=\"wp-image-6541 size-full\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-azure-blob-storage-source-read-zip-gzip-compressed-files.png\" alt=\"Reading zip and gzip compressed files (stream mode)\" width=\"688\" height=\"273\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-azure-blob-storage-source-read-zip-gzip-compressed-files.png 688w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/03\/ssis-azure-blob-storage-source-read-zip-gzip-compressed-files-300x119.png 300w\" sizes=\"(max-width: 688px) 100vw, 688px\" \/><\/a><p id=\"caption-attachment-6541\" class=\"wp-caption-text\">Reading zip and gzip compressed files (stream mode)<\/p><\/div><\/li>\n<li>Finally, we are ready to load this file(s) data into the SQL Server.<\/li>\n<\/ol>\n<h2>Load Secure FTP files data into SQL Server<\/h2>\n<div class=\"content_block\" id=\"custom_post_widget-5617\"><p>ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The <strong>Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n<h3>Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n<ol>\r\n<li>Drag and drop the <strong>Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n<li>Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n<div class=\"wp-caption aligncenter\">\r\n<a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/ssis-data-flow-drag-drop-upsert-destination.png\">\r\n<img loading=\"lazy\" decoding=\"async\" class=\"size-full\" alt=\"\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/ssis-data-flow-drag-drop-upsert-destination.png\" \/><\/a>\r\n<p class=\"wp-caption-text\">SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n<h3>Step 2: Configure Target Connection<\/h3>\r\n<ol>\r\n<li>Double-click the <strong>Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n<li>Under <strong>Connection<\/strong>, select an existing target connection or click <strong>NEW<\/strong> to create a new connection.\r\n<ul>\r\n<li>Example: SQL Server, or PostgreSQL, or Amazon Redshift.<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ol>\r\n<h3>Step 3: Select or Create Target Table<\/h3>\r\n<ol>\r\n<li>In the <strong>Target Table<\/strong> dropdown, select the table where you want to load data.<\/li>\r\n<li>Optionally, click <strong>NEW<\/strong> to create a new table based on the source columns.<\/li>\r\n<\/ol>\r\n<div class=\"wp-caption aligncenter\">\r\n<a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/09\/upsert-destination-configuration.png\">\r\n<img loading=\"lazy\" decoding=\"async\" class=\"size-full\" alt=\"\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/09\/upsert-destination-configuration.png\" \/><\/a>\r\n<p class=\"wp-caption-text\">Configure SSIS Upsert Destination Connection - Loading data (REST \/ SOAP \/ JSON \/ XML \/CSV) into SQL Server or other target using SSIS<\/p>\r\n<\/div>\r\n<h3>Step 4: Map Columns<\/h3>\r\n<ol>\r\n<li>Go to the <strong>Mappings<\/strong> tab.<\/li>\r\n<li>Click <strong>Auto Map<\/strong> to map source columns to target columns by name.<\/li>\r\n<li>Ensure you <strong>check the Primary key column(s)<\/strong> that will determine whether a record is inserted or updated.<\/li>\r\n<li>You can manually adjust the mappings if necessary.<\/li>\r\n<\/ol>\r\n <div class=\"wp-caption aligncenter\">\r\n<a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/09\/upsert-destination-key.png\">\r\n<img loading=\"lazy\" decoding=\"async\" class=\"size-full\" alt=\"\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/09\/upsert-destination-key.png\" \/><\/a>\r\n<p class=\"wp-caption-text\">SSIS Upsert Destination - Columns Mappings<\/p>\r\n<\/div>\r\n<h3>Step 5: Save Settings<\/h3>\r\n<ul>\r\n<li>Click <strong>OK<\/strong> to save the Upsert Destination configuration.<\/li>\r\n<\/ul>\r\n<h3>Step 6: Optional: Add Logging or Analysis<\/h3>\r\n<ul>\r\n<li>You may add extra destination components to log the number of inserted vs. updated records for monitoring or auditing purposes.<\/li>\r\n<\/ul>\r\n<h3>Step 7: Execute the Package<\/h3>\r\n<ul>\r\n<li>Run your SSIS package and verify that the data is correctly inserted and updated in the target table.<\/li>\r\n<\/ul>\r\n<div class=\"wp-caption aligncenter\">\r\n<a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-upsert-destination-execute.png\">\r\n<img loading=\"lazy\" decoding=\"async\" class=\"size-full\" alt=\"\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-upsert-destination-execute.png\" \/><\/a>\r\n<p class=\"wp-caption-text\">SSIS Upsert Destination Execution<\/p>\r\n<\/div><\/div>\n<h2><span id=\"Conclusion\">Conclusion<\/span><\/h2>\n<p>Above all, in this blog, we learned how to Read Secure FTP Storage Files in SSIS. Even more, we used <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-csv-file-source\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Source for CSV File<\/a>,\u00a0<a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-json-file-source\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Source for JSON File<\/a> and\u00a0<a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-secure-ftp-xml-file-source\/\" target=\"_blank\" rel=\"noopener\">Secure FTP Source for XML File<\/a> to read the file(s) from FTP Storage and load data into SQL server. You can <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\">download SSIS PowerPack here<\/a> to try many other scenarios not discussed in this blog along with 70+ other components.<\/p>\n<h2><span id=\"References\">References<\/span><\/h2>\n<p>Finally, You can use the following links for more information:<\/p>\n<ul>\n<li><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/#cat_secure_ftp\" target=\"_blank\" rel=\"noopener\">SSIS Secure FTP Source for CSV\/JSON\/XML File<\/a><\/li>\n<li><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\">About SSIS PowerPack<\/a><\/li>\n<li><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-sftp-task-ftp-ftps\/\">About Secure FTP Task<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In our previous blog, we saw how to perform SFTP \/ FTP File Operations in SSIS. Now in this blog, we will see How to Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML Format files). To illustrate, we will use\u00a0ZappySys SSIS PowerPack, which includes several tasks to import\/export data from multiples sources [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":3058,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[461,473,474,138,475],"tags":[128,296,295,6,294,48,12,7],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Read Secure FTP Storage Files in SSIS (CSV, JSON, XML). Load data in SQL server using SSIS OLEDB destination. Extract data from single or multiple files.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Read Secure FTP Storage Files in SSIS (CSV, JSON, XML). Load data in SQL server using SSIS OLEDB destination. Extract data from single or multiple files.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2019-03-23T08:54:48+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2019-09-11T11:22:41+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"100\" \/>\r\n\t<meta property=\"og:image:height\" content=\"100\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/\",\"url\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/\",\"name\":\"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png\",\"datePublished\":\"2019-03-23T08:54:48+00:00\",\"dateModified\":\"2019-09-11T11:22:41+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35\"},\"description\":\"Read Secure FTP Storage Files in SSIS (CSV, JSON, XML). Load data in SQL server using SSIS OLEDB destination. Extract data from single or multiple files.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png\",\"width\":100,\"height\":100},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"url\":\"https:\/\/zappysys.com\/blog\/author\/hshah\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML) | ZappySys Blog","description":"Read Secure FTP Storage Files in SSIS (CSV, JSON, XML). Load data in SQL server using SSIS OLEDB destination. Extract data from single or multiple files.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/","og_locale":"en_US","og_type":"article","og_title":"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML) | ZappySys Blog","og_description":"Read Secure FTP Storage Files in SSIS (CSV, JSON, XML). Load data in SQL server using SSIS OLEDB destination. Extract data from single or multiple files.","og_url":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/","og_site_name":"ZappySys Blog","article_published_time":"2019-03-23T08:54:48+00:00","article_modified_time":"2019-09-11T11:22:41+00:00","og_image":[{"width":100,"height":100,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/","url":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/","name":"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png","datePublished":"2019-03-23T08:54:48+00:00","dateModified":"2019-09-11T11:22:41+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35"},"description":"Read Secure FTP Storage Files in SSIS (CSV, JSON, XML). Load data in SQL server using SSIS OLEDB destination. Extract data from single or multiple files.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-sftp-ftp-ftps-task.png","width":100,"height":100},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/read-sftp-ftp-files-ssis-csv-json-xml\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Read SFTP \/ FTP Files in SSIS (CSV, JSON, XML)"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c9dc782c929d0027e2e87e3951ce9d35","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/076a738938c19d459fbfe125c759a0ea?s=96&d=mm&r=g","caption":"ZappySys"},"url":"https:\/\/zappysys.com\/blog\/author\/hshah\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6632"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=6632"}],"version-history":[{"count":8,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6632\/revisions"}],"predecessor-version":[{"id":7951,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6632\/revisions\/7951"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3058"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=6632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=6632"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=6632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}