Forum Discussion

EricWhitley's avatar
Qrew Trainee
7 years ago

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

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: 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,

5 Replies

  • 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.
  • 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!
  • This could be really useful - how is progress coming? Did it ever make it to Github?
  • i know this is years later, were you ever able to get this into github ? i'd love to see how you accomplished this, as i'm having a similar issue. 

    Basil Evans