Voice of the DBA

Tag: SSIS

  • An SSIS Upgrade

    I came across a post recently on the Microsoft Fabric blog about the evolution of SSIS 2025..I hadn’t heard much about SSIS in SQL Server 2025, so I thought this might provide some info on the investments that Microsoft is still making in Integration Services. I’ve run into a few people in the past year who are still heavily invested in SSIS and run packages daily. SSIS seems to be a technology that isn’t even close to dying for many organizations.

    The blog starts well, delving into the security investments with the change to the SqlClient and TLS 1.3, as well as supporting Strict Encryption. I don’t know many people using this level of security, but it’s good to have SSIS support stronger security. There is also an upgrade for SSIS packages targeting Fabric Data Warehouses if they modify their approach.

    There is also a mention of lift and shift into Fabric Data Factory as an early access program. I wonder how many organizations are looking to still run SSIS packages, but upgrade to Fabric. I’m sure there must be some, and I’m interested if any of you are in this situation. Many of us buy SQL Server licenses, so I would hope Microsoft could invest in a few new SSIS tasks, like SFTP at the least.

    The rest of the post covers Fabric and Azure Data Factory, not SSIS. I understand that Microsoft would prefer everyone abandon SSIS and move to ADF/Fabric, but that’s not what a lot of customers want to do. Many of us have no real need for complexity beyond what SSIS does. What I’d really like to see is a local version of ADF. Many of us still run our own systems and plan to do so for years. If there isn’t going to be an investment in SSIS, which I do understand, then invest in a local version of ADF.

    I doubt Microsoft has much interest in doing work here, especially as the trend towards lakehouses and Parquet files seems to drift further from the idea of SSIS moving data between systems. However, there is still a need for many organizations that might want to build packages to export their data to a data lake on-premises. They might prefer to host their own storage, use Polybase to query those files, and maybe grow into Fabric and Power BI over time.

    Giving options is something Microsoft has often done in the past. Their successes often open the door wider for others to build on their platforms. Their failures are often in places where they try to force everyone to work in a particular way with a particular technology. Expecting everyone to move to ADF/Fabric feels like one of the latter decisions.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Learn SSIS and ADF for Free

    It’s not from SQLServerCentral, though I hope to do more training across the next few months.

    Andy Leonard is offering Free Training for People Who Have Lost Their Jobs. This is Andy’s gift to the community for those that are struggling. This isn’t an open class, and you do need to email Andy, but this is a great offer.

    There are a number of classes upcoming:

    08-09 April 2020: SSIS Administration
    13-14 April 2020: SSIS Data Flows
    27-30 April 2020: From Zero to SSIS
    11-12 May 2020: Expert SSIS
    13 May 2020: Master the SSIS Catalog
    14 May 2020 – Master the Fundamentals of Azure Data Factory

    If you’re struggling, if you’re looking for work and want to gain some skills without a big investment, take advantage of this offer.

  • ETL Security Holes

    The addition of DTS to SQL Server meant that more and more of us could grab data from a variety of sources, easily, and move it to SQL Server systems. SSIS enhanced our capabilities and made it fairly simple for non-programmers to grab web service data, and other formats, and quickly decompose them into relational formats. This made it more likely that we would build data warehouses or reporting systems, and easily keep them up to date with data from our OLTP systems.

    However the ease of using ETL (extraction, transformation and loading) processes to move data did not include the moving the same security controls and restrictions to these new systems. That can create a big security holes, especially when you have secondary systems used for decision support that might not be contained in the production network and not audited to the same level as other production systems.

    Whenever you move data around, there ought to be some set of guidelines for the security protocols required. That can be hard to track when you are pulling data from a secure system and your connections look like every other connection. One of the dangers that I suspect we will run into with Powerpivot is that secure data will be downloaded to Excel worksheets on insecure systems, and all of the controls that we have in place for protecting sensitive information are ignored when we pull data for analysis elsewhere.

    Our data security has gotten better, but we have a long way to go. If you are working with ETL processes, and especially with end-users, make sure that some sort of security policy is in place to help protect this data from being accidently disclosed. It’s not a great suggestion, but until we have better security tools to allow policies to follow the data, that is all we can do.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72157/)

    Podcasts