SSIS Convert

Not Code, , , ,

I have been working on a project to bring in a large amount of data into The Raiser’s Edge. Everyone else on the project has been using SSIS to do this and previously I would probably have manipulated the data in Excel, Access or for the most complicated data written a small application to convert the data into the format that I wanted before importing it into RE. Having used SSIS for a day now I am say that I am converted!

SSIS stands for SQL Server Integration Services. Wikipedia describes it:

Integration Services provides a platform to build data integration and workflow applications. The primary use for SSIS is data warehousing, as the product features a fast and flexible tool for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases, update multidimensional cube data, and perform other functions.

Put simply it is a really good tool for manipulating incoming data and spitting it out in another format. Clearly you can use Access to perform similar tasks but what I like about SSIS is the feeling of data flowing from one end of the chain to the other. You can clearly see what steps you have taken to manipulate the data so that it is easy to repeat those steps. With Access or Excel it is so easy to lose a step. Writing a program can be overkill but SSIS seem just right for many tasks.

I would be interested to hear how you have used SSIS to bring data into or out of RE.

No related posts.

5 thoughts on “SSIS Convert”

  1. Hi David,

    I’m interested in this post as we are looking to do an integration with RE. We would prefer not to use the API. Have you done any integration using SSIS to create/update constituents? Are there any issues with writing direct to the SQL tables? The table structure seems quite ‘interesting’. I would be very grateful for your thoughts.

    Thanks,
    John Davies
    Chief Information Officer
    Cancer Council NSW, Sydney, Australia

  2. Hi John,

    Writing back directly to the Raiser’s Edge tables is a serious taboo. For one it invalidates your maintenance agreement with Blackbaud. The Raiser’s Edge front end, along with the API perform a lot of validation checks on the data that just doesn’t happen when write directly back to the tables and that is why it would be so very easy to corrupt the database if you try to do it that way.

    If you do not want to use the API then one solution is to use SSIS to create import files in exactly the correct format with all the linked import ids required to update data. These files can then be imported into RE in a simple step or two. If you have written you SSIS well so that it performs a lot of checks and looks up existing data then the import should not produce any exceptions making the process relatively painless.

    David

  3. Hi David, thanks for your reply. Your advice re: RE tables is noted! With the import file option, is there any way of automating this?
    John

  4. Hi John,

    The way you would do this is to schedule your SSIS package so that it runs every so often then using RE:Queue (assuming that you have that module and that you can get it to work – it can be temperamental) you can schedule the imports to work at certain times so that in theory the process is seamless.

    David

Comments are closed.