Any interest in an open source set of SSIS components for MSSQL?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Doesn't Need an Answer
Hello - 

Wondering if there are other users of Microsoft SQL Server who might be interested in a simple set of SSIS components (connection manager, data source, and data destination)?

I realize there's a commercial ODBC driver out there (which looks great), but I found myself in an odd situation where, for specific reasons, buying software is off the table, but investing time is OK.

I've already got some basic stuff working and my intent is to polish it up enough to be useful without needing to know the API:
  1. Connection manager for attaching
  2. Data source for querying
  3. Data destination for writing back
(No generalized ODBC like integration where you can do mass deletes)

For the data source I'm also going to add something I've found useful on other projects, which is emitting "maybe kinda close enough DDL" for generating your target tables (it just abuses SMO). I've already got this working and it applies some basic rules (sanitizing column names, prefacing the individual address fields with the parent name prefix, etc.) as well as does some very rudimentary guesses on data types. It's mean to be an assist, not an operation you completely trust.

I already wrote a generalized "auto sync" process that says "get everything from a database" (simple C# app - creates and runs DDL, merges data over, etc.), but I'm in the process of taking subsets of that and putting things into simple SSIS components. Basic SSIS process - set up the connection manager with your API key and select the database, then in the data source, choose your table. That's it. Done. It's meant to be easy.

The only "API-ish" bit I'm exposing (mainly because I'm lazy) is the query. For right now I'm planning on that just being a string you enter. (EX: https://help.quickbase.com/api-guide/index.html#samplequeries.html) I don't want to have to get too far into the weeds right now in building a custom UI for that.

I'm currently testing on SQL 2016.

If anyone else is interested in testing later, please let me know. I'm happy to ship out a sample DLL. Right now I've got the data source implemented as I try to figure out how MS wants the destination written.

Code will eventually get to GitHub once I finish wrapping some basic UI elements around things.

Thank you,
Eric
Photo of Eric Whitley

Eric Whitley

  • 190 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of ARYEH ZELCZER

ARYEH ZELCZER

  • 332 Points 250 badge 2x thumb
How does this differ from QuNect?
Photo of Eric Whitley

Eric Whitley

  • 190 Points 100 badge 2x thumb
QuNect is definitely to be more fully featured because they appear to be creating a whole ODBC interface to the QuickBase API.

For me, I need a simple way to let SSIS query data out and pump data back in. I've not used QuNect, but if it's doing ODBC-compatible operations, it should be fine doing that and more. Honestly, the $2k they're charging is extremely reasonable.

That said - I can't (for funding reasons), spend $ on software, so I'm quickly writing a simple way to do some very simple interactions with QB. Nothing nearly as fancy or slick as QuNect, but it fits my basic needs and I'm happy to share it in case it might be useful to others.
Photo of Matthew Thiede

Matthew Thiede

  • 80 Points 75 badge 2x thumb
I'm looking for a way to write data back to QB.  I figured out how to pull the data down using SSIS.  I'm using VS2017.  I would be interested in your code or an overview on how to accomplish this task.  I can create the file for upload into QB.  The file is XML or I can make it anything such as Excel, CSV, etc.  I just don't know how to push that file up to QB.  I have tried QuNect but it is slow and I don't want to spend the money because it is only useful for QB.  I'm going to use this process for other applications. Any help is greatly appreciated. Thanks!