• Define 'set up' and 'best practices'.

    If you're saying that your company is going to start using SSIS and would like to know if there's any kind of guidance on how to do ETL then I'm going to guess the answer is 'not really'. Every company has their own processes and even within the same one there are differences depending on the area writing the code.

    Some things that I personally try to jam through when I'm building new processes/packages.

    Naming standards. Whatever works for your company but my preference is 'Grouping_Process' as in Contact_Extract. That way all the packages for a process hang together.

    Parameters. I'm a big fan of parameters. Create a parameter table and have a package that sets them for the incremental load. Then create a stored procedure to overwrite them. I can go into more detail how I handle that one but the result is the ability to gap fill and rerun by setting the parameter and requesting the job be run and no code is touched.

    Logging. Find a way to log what's important to you. If you're using the catalog there will be reports but they're not very open for viewing. I built my own process since we want to catch things that aren't always part of the things SSIS or SQL want to log.

    Embedded code vs stored procedures. Putting your SQL into stored procedures means using SSIS more as a control flow than the tool but it also makes your code accessible and changeable without having to open the package. The downside is that you've got a bunch of stored procedures that need to go with the package along with the rights to execute them.

    If you're interested in more detail about how I built out the current framework and templates for our ETL I'll gladly write a lot more and give examples.