DEV Community: Daniel ZS

SSIS tutorial: How to install SSIS in Visual Studio 2022

Daniel ZS — Fri, 13 Jun 2025 12:36:09 +0000

Introduction

SQL Server Integration Services (SSIS) is a powerful tool for building ETL workflows and automating data pipelines. If you're using Visual Studio 2022, enabling SSIS is simple once you install the right extensions. In this guide, I'll walk you through how to set up SSIS and supercharge it using the ZappySys SSIS PowerPack for modern integrations.

Prerequisites

Make sure you have the following:

Visual Studio 2022 installed
Admin rights to install extensions

Step-by-step: Enable SSIS in Visual Studio 2022

1. Open Visual Studio

Launch Visual Studio 2022
Go to Extensions > Manage Extensions

2. Search for SSIS Extension

In the Manage Extensions window, switch to the Online tab
Search for SQL Server Integration Services Projects Or download it directly: → SSIS Extension on Visual Studio Marketplace

3. Install the Extension

Follow the Visual Studio Installer prompts
After installation, restart Visual Studio

4. Verify Installation

Open Visual Studio and click on Create a new project
Search for Integration Services Project
Select it and click Next
Name your project and click Create

You should now see the SSIS designer with tabs like Control Flow, Data Flow, and Parameters

Troubleshooting Tips

Can't Find SSIS Templates?

Go to Extensions > Manage Extensions to ensure the SSIS extension is installed and enabled
Try Tools > Import and Export Settings > Reset All Settings

Missing Dependencies?

Double-check that SQL Server Data Tools were installed when setting up Visual Studio

Bonus: Add ZappySys SSIS PowerPack for Modern ETL Needs

Want to connect SSIS to REST APIs, JSON, cloud storage, OAuth, and more?
Install the ZappySys SSIS PowerPack — a set of 70+ powerful components that bring SSIS into the modern data stack.

Key Benefits

API Integration

Supports REST, SOAP, JSON, XML
No coding needed
Use components like: REST API Task, JSON Source, XML Source

Cloud Connectivity

Connect to AWS S3, Azure, Google Drive, etc.
Example connectors: Amazon S3 Task, Google Drive, Azure Blob Storage Task

File Integration

Easily work with CSV, Excel, or flat files
Includes: CSV Source, Excel Source

Secure Connectivity

OAuth2, SSL, Token-based auth
See: OAuth Connector, Base64 Auth in HTTP

Save Time

Drag-and-drop components mean faster development and fewer bugs

Install ZappySys SSIS PowerPack

Download the trial or full version
Run the installer as Admin
Restart Visual Studio
You’ll see new components in the SSIS Toolbox (prefixed with ZS)

Need help installing it in VS 2022? Here's a detailed guide:
→ How to Install SSIS PowerPack in Visual Studio 2022

Conclusion

With SSIS properly enabled in Visual Studio 2022 and ZappySys PowerPack installed, you're ready to build powerful, modern ETL solutions that connect with just about any data source.

References

How to locate ZappySys SSIS components in Visual Studio

Daniel ZS — Fri, 13 Jun 2025 12:23:29 +0000

Introduction

After installing the ZappySys SSIS PowerPack, many users expect new components to appear automatically in Visual Studio (SSDT). These include advanced tasks and data flow connectors that help you build better SSIS packages.

However, it’s not always clear where to find them inside the SSIS Toolbox.

How to locate ZappySys components in SSDT

Open Visual Studio (SQL Server Data Tools).
Start a new SSIS project or open an existing one: Go to File > New > Project > Business Intelligence > Integration Services Project.
In the package designer, go to the Control Flow tab. If the SSIS Toolbox isn't visible, right-click anywhere and select SSIS Toolbox.
In the toolbox, look for tasks that start with ZS — these are ZappySys Control Flow Tasks.
Switch to the Data Flow tab. The toolbox will refresh automatically.
In the Data Flow toolbox, find components starting with ZS — these are ZappySys Data Flow Components.

What if you don’t see any ZappySys components?

If you’ve installed the PowerPack but don’t see anything in your toolbox, don’t worry — this is a common issue. It usually means SSDT didn’t refresh the toolbox automatically.

The problem

ZappySys components like the ZS REST API Task are missing from both Control Flow and Data Flow. They all start with ZS, so if you can’t find them, something’s off.

The fix: manually refresh the toolbox

Open your SSIS project in Visual Studio.
Open any .dtsx package file.
Right-click inside the SSIS Toolbox.
Select Refresh Toolbox.

This should reload all components, including ZappySys ones.

Still not seeing them?

Here are a few things to check:

Did you install the right bit version (32-bit or 64-bit) that matches your SSDT?
Try restarting Visual Studio after installing PowerPack.
Make sure you’re using SSDT for Visual Studio 2015 or later, depending on your SQL Server version.

Need more help? Check out this support article:
👉 I don’t see PowerPack components in SSIS Toolbox

Conclusion

Once installed, ZappySys SSIS components should appear in both Control Flow and Data Flow toolboxes. If not, refreshing the toolbox or checking SSDT compatibility usually solves it.

After everything loads correctly, you’re ready to build powerful ETL workflows using ZappySys tasks and connectors.

Need help?

Still stuck or have questions?
Reach out to our support team via live chat or by emailing support@zappysys.com.

💡 SSIS Tip: Export Google Drive Data to MongoDB (No Code Required)

Daniel ZS — Fri, 25 Apr 2025 22:41:25 +0000

If you're working with SSIS and need to move data from Google Drive into MongoDB, you're in luck. With the ZappySys SSIS PowerPack, you can build this integration without writing a single line of code.

This solution is ideal for automating backups, analytics pipelines, or syncing documents with other systems.

🚀 What’s Inside the Full Tutorial

The original guide walks you through how to:

✅ Set up a Google API Project and enable the right scopes (Google Drive + Sheets)
🔐 Configure OAuth 2.0 access to Google Drive in SSIS
🔄 Use ZappySys API Source to fetch files and metadata from your Drive
🧩 Connect to MongoDB (local or cloud-based) using the MongoDB Destination
🧱 Optionally use the JSON Generator for structured updates in MongoDB

All within a visual SSIS interface—no scripting required.

🧰 What You’ll Need

Make sure you have the following before you begin:

ZappySys SSIS PowerPack
MongoDB database (on-prem or Atlas)
A Google account with file access

🔗 Read the Full Step-by-Step Guide

📝 The complete tutorial with screenshots and config instructions is available here:

👉 SSIS Tip: How to Export Google Drive Data into MongoDB

It’s beginner-friendly but powerful enough for advanced ETL developers.

📚 Additional Resources

💬 Got Questions?

ZappySys offers live chat and ticket support if you need help setting up your pipeline or debugging authentication issues.

Follow for more SSIS ZappySys integration tips and ETL automation tricks! 🔁

SSIS expressions: introduction and examples

Daniel ZS — Fri, 11 Apr 2025 01:13:44 +0000

To drive traffic to your full article on the ZappySys site while still offering value on Medium or Dev.to, you can include strategic cut-off points where the reader is encouraged to continue reading by visiting the full tutorial. Below is your modified article with those changes included:

Introduction to SSIS Expressions (Quick Overview)

Expressions play a crucial role in SSIS (SQL Server Integration Services) by providing a dynamic and flexible way to configure and control various aspects of your data integration workflows.

Below is a brief overview highlighting the core benefits and use cases of SSIS expressions. To explore a complete guide with screenshots, expression examples, and advanced use cases, you can view the full article on our official ZappySys blog.

👉 Continue reading on our site:

🔗 Full Guide to SSIS Expressions with Examples (ZappySys)

Key Use Cases and Benefits

Dynamically configure properties of SSIS tasks, connections, variables, and components
Adapt SSIS packages to different runtime scenarios
Support conditional logic for building flexible workflows
Enable dynamic error handling based on task outcomes
Make packages more reusable and environment-agnostic via parameterized expressions
Frequently used for dynamic connection strings and file paths

🟡 We go deeper into each of these use cases, including real-world tips, in the full tutorial → Read more

Common Expression Syntax Overview

SSIS Expressions support a variety of operators, functions, and constants:

🔢 Operators:

Arithmetic: +, -, *, /, %
Comparison: ==, !=, <, >, <=, >=
Logical: &&, ||, !
String Concatenation: +

🔧 Functions:

String: LEN(), SUBSTRING(), LEFT(), RIGHT(), LTRIM(), RTRIM()
Math: ABS(), ROUND(), CEILING(), FLOOR()
Date/Time: GETDATE(), DATEPART(), DATEDIFF()
Conversion: CONVERT()
Null Handling: ISNULL()

💡 Want the full breakdown with usage samples and screenshots?

👉 Visit the full guide here: SSIS Expression Syntax in Action

Handling Type Conversion and Compatibility

Expressions often require compatible data types. SSIS may auto-convert types, but using explicit conversions like (DT_STR) or (DT_DATE) is best practice.

⚠️ Mixing types (e.g., strings with numbers) without proper conversion can cause runtime errors.

📌 More on this in our detailed section: Type Compatibility in SSIS Expressions

Using the Expression Builder in SSIS

Want to see how to open the Expression Builder in real SSIS tasks like REST API Task or JSON Source?

✨ We walk you through it step-by-step with annotated screenshots.

➡️ Click here to follow the hands-on tutorial

Bonus: Format a Date as "dd-mm-yyyy" in a Variable

Here’s a quick expression you can use to format the current date:

RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "-" + 
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" +  
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())

📸 Want to see how to apply this using the UI?

See full illustrated steps here →

🔗 ZappySys SSIS Expression Builder Tutorial

✅ Ready to Level Up Your SSIS Expressions?

The full article includes:

Interactive screenshots
Use of variables in control flow
Advanced tricks like dynamic REST URLs
Common pitfalls and how to fix them

🚀 View Full Article on ZappySys Blog

Connect Excel to Google BigQuery — The Easy No-Code Way with ZappySys ODBC Driver

Daniel ZS — Fri, 11 Apr 2025 00:09:48 +0000

If you're looking to pull data from Google BigQuery directly into Microsoft Excel, the process can be a bit intimidating — especially if you're not a developer.

But don't worry — using the ZappySys ODBC API Driver, you can connect Excel to your Google Query API with zero coding. In this post, we'll walk you through the basic setup — and provide a link to the full detailed tutorial with screenshots and video.

🚀 Try it live: Connect Google Query to Excel using ZappySys ODBC (full tutorial)

✅ What You’ll Need

Before getting started, make sure you have:

ZappySys ODBC PowerPack installed — Download it here
A Google account with access to BigQuery
A Google Cloud Project with APIs enabled

That’s it! Let’s get into it.

⚙️ Quick Overview of the Setup Process

Here’s a high-level look at what you’ll do:

🔹 Step 1: Set Up Google API Access

You’ll go into the Google Cloud Console, enable the necessary APIs (like BigQuery API), configure OAuth credentials, and get your Client ID and Secret.

🧠 Need help with that? View full instructions here

🔹 Step 2: Create a ZappySys ODBC DSN

Using the ZappySys API Driver, you’ll create a new DSN and select the Google BigQuery Connector from the list. From there, you’ll enter your credentials and test the connection.

🔹 Step 3: Load Data into Excel

In Excel, go to Data > Get Data > From Other Sources > From ODBC, select your new DSN, and log in using Windows Authentication. Then select the BigQuery table you want to query.

🎥 Want a visual walkthrough?

Watch this short video:

📎 Bonus Tips

✅ Save your OAuth credentials so you don't have to re-authenticate every time.
✅ Use preview mode in the ZappySys driver to explore datasets before pulling them into Excel.

🔗 Get Full Details with Step-by-Step Screenshots

To see the complete tutorial with all screenshots, field names, and advanced configuration tips, check out the full article on our website:

👉 📘 Full Step-by-Step Guide: Connect Google BigQuery to Excel via ODBC

🙋 Need Help?

If you run into issues or have questions, our friendly support team is available via chat on zappysys.com.

Reading AWS REST API in ODBC Apps (Power BI, Excel, Informatica, MS Access)

Daniel ZS — Thu, 10 Apr 2025 23:53:06 +0000

If you need to access AWS REST API data in tools like Power BI, Excel, Informatica, or MS Access but don't want to dive into coding, this guide is for you.
We'll show you how to use the ZappySys ODBC JSON Driver to capture AWS CLI output and feed it into your ODBC-enabled apps - quickly and easily.
✅ Bonus: If you prefer a low-level method without AWS CLI, we've covered that too in the full version of this guide.

Requirements
Before you begin, make sure you have:
✔️ Installed the AWS CLI
✔️ Downloaded ZappySys ODBC PowerPack
[✔️ Set up a new ODBC DSN with ZappySys JSON Driver]

Step-by-Step Setup

Open the ODBC Data Source Admin Open the Start menu, search for "ODBC", and launch ODBC Data Source (64-bit).
Add a New DSN In the System DSN tab: Click Add Select ZappySys JSON Driver Click Finish

🎯 Need screenshots and DSN examples? View the full guide here

🔍 Query AWS API Data Without Writing Code
Here's a sample query to preview patch data from AWS SSM:
SELECT * FROM "$"
WITH(
Src='cmd:>aws ssm describe-available-patches --no-verify-ssl',
"Filter"='$.Patches[*]'
)
You can apply similar logic to retrieve EC2 instance data, CloudWatch metrics, or anything else supported by AWS CLI.
Want to load this data into Power BI or Excel? We've got the exact steps for that too.
👉 Continue to the full tutorial with screenshots and query examples

🎥 Bonus: Video Tutorial
Prefer watching over reading?
🎬 Check out our video: How to read and write AWS REST API data using the ZappySys ODBC Driver.
👉 Watch the video and get started

✅ Conclusion
The ZappySys JSON Driver makes it incredibly easy to query AWS REST APIs from any app that supports ODBC. From Power BI dashboards to Excel spreadsheets - your data is just one query away.
💡 For more examples, advanced filter logic, and update support -
👉 Read the complete article here
Need help? Our support team is always here for you via chat on our website.

SSIS tips: How to import JSON files to MongoDB

Daniel ZS — Thu, 10 Apr 2025 21:38:54 +0000

Introduction

As businesses adopt NoSQL databases like MongoDB for flexibility and scalability, integrating JSON data into these platforms becomes increasingly important. SQL Server Integration Services (SSIS), paired with the ZappySys SSIS PowerPack, offers a streamlined way to import JSON files directly into MongoDB.

👉 To see the complete step-by-step tutorial with screenshots, visit the full article on our site:

How to Import JSON Files into MongoDB using SSIS (Full Guide)

Quick Steps Preview

Add a Data Flow Task to your SSIS package.
Add a JSON Source inside the data flow.
Select your JSON file or use wildcards to include multiple files. Example: D:\Zappysys\Test\JSONs\sub\sub2\newJSON*.json
Use filters to extract specific arrays or objects. Refer to this guide for help.
✅ Preview the data to ensure everything is correct.

🛠️ The full guide continues with:

MongoDB connection setup

Configuring insert/update operations

Using JSON Generator for updates

Complete screenshots and explanations

🔗 Read the full tutorial here:

Import JSON into MongoDB with SSIS (Full Guide + Images)

Conclusion

SSIS and ZappySys make it incredibly easy to handle MongoDB integrations. Whether you're importing new documents or updating existing ones using JSON files, our tools simplify complex ETL tasks.

🚀 Want to see the full setup and screenshots?

👉 Read the complete article on our official ZappySys blog

References

Contact us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.

How to connect BigQuery with Excel using ODBC

Daniel ZS — Fri, 28 Mar 2025 22:51:15 +0000

Google BigQuery is a powerful platform for data analysis, but accessing its data from tools like Microsoft Excel can be challenging. Fortunately, with the ZappySys ODBC driver, we can easily and efficiently connect Excel to BigQuery. In this article, we will guide you through the connection process step by step.

Prerequisites

A Google Cloud account with access to BigQuery.
A BigQuery project with the data you want to query.
Microsoft Excel installed on your system.
ZappySys ODBC PowerPack installed on your computer.

Steps

You can see the steps in this article