FTP / SFTP ODBC Driver (for XML Files)FTP / SFTP ODBC Driver for XML files can be used to read XML Files stored on FTP Site (Classic FTP, SFTP, FTPS) . Using this driver you can easily integrate FTP Site data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip). This driver is using same high performance data processing engine which was originally developed for JSON Connector in SSIS PowerPack. ODBC PowerPack and SSIS PowerPack, both products share many UI elements and concepts. We wrote many articles to explain various features in one product but concepts are mostly same in both products so hope you can reuse steps explained in different articles even though screenshots /steps may be slightly different. Feature Summary
|
||||
|
View All Drivers | |||
Featured Articles
|
FTP / SFTP XML ODBC Driver UI
Configure FTP / SFTP Connection – FTP / SFTP ODBC Driver for XML Files
Browse File(s) – FTP / SFTP File Selection
Integration Scenarios (Reporting / ETL / BI / Programming)
ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn’t appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.
ZappySys ODBC Drivers – Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell
BI / Reporting Tools
IntegrationETL Tools
Integration
Programming Languages
Integration
ODBC Integration Screenshots in various tools
SQL Query Examples – FTP / SFTP ODBC Driver for XML Files
ZappySys introduced most innovative way to transform your Cloud Storage files (e.g. CSV / JSON / XML) into Flat table using familiar SQL Query Language. Here are some examples of SQL Queries to read data from Amazon S3, Azure Blob or Secure FTP Storage (SFTP / FTP /FTPS) drivers.
/*--------- Query CSV / JSON / XML File(s) ---------*/ SELECT * FROM $ WITH (SRC='Northwind-Bucket/Customer*.???')
/*--------- Advanced SQL Support with GROUP BY / ORDER BY ---------*/ SELECT Country as Invoice_Country, SUM(UnitPrice * Quantity) Total FROM value WHERE Discount > 0 GROUP BY Country HAVING SUM(UnitPrice * Quantity)>1000 ORDER BY Total DESC WITH (SRC='Northwind-Bucket/Invoices*.*')
/*--------- Query GZip / Zip Compressed Files ----------*/ SELECT * FROM value WITH ( SRC='Northwind-Bucket/Orders*.gz' ,FileCompressionType='GZip' -- None, Zip or GZip )
/*--------- Amazon S3 Driver - Read CSV File Example - Override UI credentials in WITH clause ----------*/ SELECT * FROM value WITH ( Src='Northwind-Bucket/Orders*.csv', AccessKey='AKIAxxxxxxxxxxxxx', SecretKey='a1wxxxxxxxxxxxxxxxxxxxxxxxxxxxx', RegionName='us-west-1', ColumnDelimiter='|', HasColumnHeaderRow='False' )
/*--------- Azure Blob Driver - Read JSON File Example - Override UI credentials in WITH clause ----------*/ SELECT * FROM value WITH ( Src='Northwind-Container/Orders*.json', AccountName='My-Azure-Blob-Account', AccountKey='mpmlcxxxxxxxxxxxxxxxxxxxxxxxxxx', Filter='$.orders[*].items[*]' )
/*--------- FTP / SFTP Driver - Read XML File Example - Override UI credentials in WITH clause ----------*/
SELECT * FROM value
WITH (
Src='Northwind-Container/Orders.xml',
Protocol='Sftp',
ServerName='192.168.12.124',
UserName='MyFtpUser1',
Password='MyP@@$$word1234',
Filter='$.document.orders[*]'
)
ZappySys Data Gateway (ODBC Bridge for SQL Server / JAVA / Linux / Mac)
ZappySys has developed a unique bridge called ZappySys Data Gateway Service (ZSDG) which can help to access our Drivers in SQL Server or JAVA based Apps or Non-Windows OS (e.g. Mac, Linux). ZappySys Data Gateway service can run in the cloud (VM Exposed to internet) or you can install locally on-premises.
Client application can connect to Data Gateway Service using any Microsoft SQL Server compatible driver (i.e. SQL Server ODBC, OLEDB, ADO.net or JDBC Driver or Linked Server in SQL Server). Data Gateway can be installed on the central server where you can have many users who can connect to Data Gateway to use ZappySys Drivers without installing anything on their machine. Data Gateway Service understands TDS Protocol and Client App can be running on any machine or operating system (MacOS, Linux, Windows).
ZappySys Data Gateway – Connect to JSON, XML, OData, REST API, SOAP data sources using TDS protocol compatible drivers (or any SQL Server ODBC, JDBC, OLEDB, ADO.net driver )
Microsoft SQL Server Integration using Data Gateway Service (T-SQL)
Here is one possible use case of using Data Gateway Service. Any DBA or Non-Programmer can start writing T-SQL queries to use ZappySys Drivers (e.g. REST API, JSON, XML, CSV data source) right inside your usual T-SQL code (You can access data from Salesforce, REST API, JSON, XML, CSV inside Views, Functions or SQL Stored Procedures).
This approach can eliminate any possible ETL work needed to extract data outside of SQL Server, you can start using your existing SQL Skill to achieve previously hard to achieve scenarios without coding.
For many other possible use case of Data Gateway click here.
SQL Server Integration Example – Query REST API / JSON Files / XML Files inside SQL Server using ZappySys Data Gateway Service (Use of Linked Server / OPENQUERY Feature in T-SQL Code / SSMS)
Video Tutorial – Calling ZappySys Drivers inside SQL Server (JSON / REST Driver use case)
Here is a short video to demonstrate a use case of Data Gateway. With this approach you can import data from REST API or any other data source for which ZappySys offers Drivers (e.g. Amazon S3, Azure, SFTP, Salesforce, XML , CSV)
Programming Language Examples
Most programming languages come with out of the box support for ODBC. Which means you can use ZappySys ODBC drivers inside your favorite language. Here are few languages which already support ODBC. We have used JSON Driver / SQL query as an example but concept is same for other drivers too. Refer to help file to learn more about Driver specific Connection String and SQL Query.
using (OdbcConnection conn = new OdbcConnection("DRIVER ={ZappySys SFTP XML Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json'")) { conn.Open(); cmd = new OdbcCommand( @"SELECT Country as Invoice_Country, SUM(UnitPrice * Quantity) Total FROM value GROUP BY Country ORDERBY Total DESC", conn); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("---- Fetching Row -------"); for (i = 0; i < rdr.FieldCount; i++) { Console.Write("Field {0}={1} ", i, rdr[i]); } Console.WriteLine(""); } }
//Assuming the Microsoft SQL Server JDBC Driver is in below folder //C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64 private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //The JDBC connection URL to connect to ZappySys Data Gateway Service using SQL Server driver private static final String jdbcURL = "jdbc:sqlserver://localhost:5000;databasename=master;user=tdsuser;password=tds123;"; //Connect to the database Connection databaseConnection = DriverManager.getConnection(jdbcURL); System.out.println("Connected to ZappySys Data Gateway Service using Microsoft SQL Server JDBC driver"); //declare the statement object Statement sqlStatement = databaseConnection.createStatement(); ResultSet rs = sqlStatement.executeQuery("SELECT Country , SUM(UnitPrice * Quantity) Total " + "FROM value " + "GROUP BY Country " + "WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')"); while (rs.next()) { System.out.println("-----Fetching new row----\n"); System.out.println(rs.getString("Country") + "\n"); //System.out.println(rs.getString("Total") + "\n"); }
#Example of using ODBC driver inside Python using pyodbc library (Read more info about pyodbc from below) #https://github.com/mkleehammer/pyodbc/wiki import pyodbc #connect to api service using ZappySys ODBC driver for JSON #Use DSN #conn = pyodbc.connect(r'DSN=MyZappyDsnName;') # OR Use direct connection string conn = pyodbc.connect( r'DRIVER={ZappySys SFTP XML Driver};' ) cursor = cnxn.cursor() #execute query to fetch data from API service cursor.execute("SELECT * FROM value ORDER BY Country WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')") row = cursor.fetchone() while row: print row[0] row = cursor.fetchone()
echo "Example of using ZappySys ODBC Driver in PHP\n"; $conn = odbc_connect("DRIVER={ZappySys SFTP XML Driver};", "", ""); $sql = "SELECT * FROM value ORDER BY Country WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')"; $rs = odbc_exec($conn,$sql); echo "Fetching first row....\n"; odbc_fetch_row($rs); echo "Country=" . odbc_result($rs,"Country") . "\n"; echo "Closing connection ....\n"; odbc_close($conn);
$conn = New-Object System.Data.Odbc.OdbcConnection $conn.ConnectionString = "DRIVER={ZappySys SFTP XML Driver}" #--OR-- Use DSN name #$conn.connectionstring = "DSN=MyDSNName" $conn.Open() # ------------------------------------------------------------------------------- # In powershell $ is special char so we used `$ in below string to escape it. # Also We used multi string start with "@<new line> and ends with <new line>"@ # ------------------------------------------------------------------------------- $sql = @" SELECT * FROM value WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?`$format=json') "@ $cmd = $conn.CreateCommand() $cmd.CommandText = $sql $dataset = New-Object System.Data.DataSet #Load data in DataSet (New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet) #Export datatable to file in CSV format $dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter "`t" | Out-File "c:\temp\dump.csv" -fo Write-Host "Total rows $($dataSet.Tables[0].Rows.Count)" $conn.Close()