A Fine Slice Of SQL Server
Meagan Longoria deals with the unpleasantness:
Although you can now specify an Oracle database using the EzConnect format, it’s still very common to use a tnsnames.ora file. This file specifies a connection name and connection details for an Oracle database. It’s helpful in that you can provide a “friendly name” to the database specified in the file and then reference the file from multiple client tools.
Read on to learn how you can use it.
Comments closedOlivier Van Steenlandt hits the big red button:
I developed my package in Visual Studio 2019 and deployed my changes to my SSIS Catalog (SSISDB in my case). As soon as my SSIS Package was deployed I started the SSIS Package execution.
After a couple of minutes, I realised I made a mistake. I didn’t want to lose too much time, so I looked for a way to stop the SSIS Package Execution.
Since I was not executing the package from a SQL Server Agent Job, I couldn’t just stop the job. So I needed to find another way.
Click through for a way to do this via SQL Server Management Studio.
Comments closedKoen Verbeeck does a bit of installation:
For those of you that have been working on an older version of SSIS/SQL Server (2014-2016, something like that), the BI components (SSIS/SSAS/SSRS) are now extensions in Visual Studio. SQL Server Data Tools (SSDT) is no longer available as a separate download.
So you’ll need a full-blown version of Visual Studio (make sure you only install the workflows you actually need). The good news is that you can use the community edition of VS if you’re just using it for BI development. Anyway, install VS 2022 on your machine and download the SSIS extension here.
Read on for the full installation process and a couple of warnings.
Comments closedScenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There’s quite a bit of misinformation on the Internet about how to do this, hence this blog.
Read on to see how you can do this, supposing that you’re hosting in a Premium-Per-User or Premium workspace.
Comments closedThomas Williams checks the logs:
Due to its simplicity, I’m a fan of the default, in-built “SSIS log provider for SQL Server” logging, which writes to the SSIS database
SSISDBwhen the package is deployed to a SQL Server. This logging comes out of the box with very little setup required, and can be supplemented by custom messages – for instance, usingDts.Login a script task as per https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task.Best of all, because log messages are written to tables in the
SSISDBdatabase, end-users can run a query or report to troubleshoot errors.
Read on for a query which pulls the last seven days worth of error information from the built-in log.
Comments closedJon Morisi does some troubleshooting:
I just spent a long slog sorting out why I could not connect to my SSIS instance remotely. I work in a very secure environment requiring network approval for any and all ports. According to the following article, I was under the impression that a request to open incoming traffic on port 135, to a specific IP, would allow SQL Server Management Studio, on that specific IP, to connect remotely to SSIS:
After opening port 135, I was receiving the error message in the title of this article:
If you find yourself in this situation, read on to see how Jon was able to solve the problem.
Comments closedAndy Leonard beats the brakes off of some stuff:
It may help to define reference first. An SSIS Catalog Reference is a relationship between an SSIS Project and an SSIS Catalog Environment in the SSIS Catalog. One or more references to environments may be configured for a project. At execution time one and only one reference may be selected – like Highlander, “There can be only one.”
A broken reference occurs when one configures a reference between a project and an environment, and then the environment is deleted.
Read on to see what this looks like in the SSIS Catalog Browser and SISS Catalog Compare tools, as well as how you can prevent accidentally creating broken references.
Comments closedPeter Schott shows off a handy trick:
I’ve worked with SSIS for some time now, but a recent question and post pointed out an expression I’d never used before. We often have a need to split strings and pick out some portion of that string. Sometimes we have a need to pull in everything in the “nth” occurrence of a string. The TOKEN expression can be used to get that particular value. I tested this out by mocking up a really simple package.
Read on to see how.
Comments closedJitendra Yadeo has put together a how-to guide:
– There can be scenario where organization wants to migrate there existing SSIS ETL process on cloud so instead of rewriting SSIS package using Cloud specific ETL tools like Azure Data Factory we can directly migrate SSIS packages and call it through Azure Data Factory.
– Goal of this blog is to show how SSIS packages hosted on on-premise can be migrated to Azure Data Factory (ADF) using Azure-SSIS Integration Runtime (IR).
Read on for a step-by-step guide.
Comments closedOn 17 Feb 2022, I first saw the Microsoft announcement of the public preview of Azure-SSIS integration runtimes in Azure Synapse Analytics. I blogged about the announcement in a post titled Azure-SSIS Integration Runtime now available in Azure Synapse Analytics.
I am excited to share one way for you to provision an Azure-SSIS IR in Synapse Analytics, following these steps. To start provisioning a shiny new Azure Synapse Analytics Azure-SSIS integration runtime, open Synapse Studio:
Read on for the step-by-step guide.
Comments closed