Recently, on SQL Server where our SSIS jobs were deployed, while trying to expand SQL Server Agent node, we faced error "Agent XPs Disabled" as shown below: To fix this issue, we executed below queries in the sequence: EXEC SP_CONFIGURE 'show advanced options', 1GO RECONFIGURE GO EXEC SP_CONFIGURE ' show advanced options' EXEC SP_CONFIGURE 'Agent … Continue reading SQL Server: Agent XPs Disabled
Category: SQL Server
Azure: Execute Stored Procedure using Azure Data Factory
In this blog post, we'll see how to execute Stored Procedure in Azure SQL Database inside Azure SQL Server. Let's go through the steps to see it in action. Login to Azure Portal.Click Create a resource --> Databases --> SQL Database. Give a unique name to the Azure SQL Database. Click on Server field to … Continue reading Azure: Execute Stored Procedure using Azure Data Factory
Azure: Copy Data from D365 CE to Azure SQL Database using Azure Data Factory
In this blog post, we'll see how to copy data of an entity "Contact" in D365 CE to Azure SQL Database. Let's follow the below steps to see it in action. Login to Azure portal.Create Azure SQL Database where we need to copy the data. Click Create a resource --> Databases --> SQL Database Give … Continue reading Azure: Copy Data from D365 CE to Azure SQL Database using Azure Data Factory
Azure: Execute SSIS Package using Azure Data Factory – Part 2
In the previous post, we created the required Azure resources. In the last step of the previous post, we created Azure SSIS IR which is basically responsible for creating SSISDB in the Azure SQL Server where we'll deploy the SSIS package. In this demo, we are going to execute a SSIS package which will load … Continue reading Azure: Execute SSIS Package using Azure Data Factory – Part 2
Azure: Execute SSIS Package using Azure Data Factory – Part 1
In this blog post series, we'll learn how to execute SSIS package using Azure Data Factory. This topic is divided into 2 parts: In the first part we'll create the required Azure resources and in the second part we'll see how to deploy and execute the package. To begin we need following Azure resources: Azure … Continue reading Azure: Execute SSIS Package using Azure Data Factory – Part 1
SSMS: Integration Services Catalog node not visible
Recently, while working on Azure SQL Server to execute SSIS package, we found a strange issue. We created the Azure SQL Server from Azure Portal. We designed the SSIS package using SSDT. To deploy package to Azure SQL Server and execute there we need Azure-SSIS Integration Runtime(Azure-SSIS IR) to be setup. We created Azure Data … Continue reading SSMS: Integration Services Catalog node not visible
Azure: Copy data from one database to another using Azure Data Factory – II
In the previous post, we discussed how to create Azure SQL Server and Azure SQL Database. Now that we are ready with source data/table and destination table, let's create Azure Data Factory to copy the data. 3. Azure Data Factory: Click on Create a resource --> Analytics --> Data Factory Fill the mandatory fields and … Continue reading Azure: Copy data from one database to another using Azure Data Factory – II
Azure: Copy data from one database to another using Azure Data Factory – I
In this post, we will talk about how to copy data of a table from one database to another using Azure. Here we are using the following Azure resources: Azure SQL Server Azure SQL Database Azure Data Factory To begin, let's login to Azure portal. After successful login let's go through the below steps to see … Continue reading Azure: Copy data from one database to another using Azure Data Factory – I
Azure: Error while connecting to Azure SQL Server “Cannot open server ‘…’ requested by the login. Client with IP address ‘…’ is not allowed to access the server”
Recently, while accessing azure SQL server from SQL Server Management Studio, we got the below error: "Cannot open server 'server_name' requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server". To fix this issue, in Azure portal, we added the IP address of machine from which we were trying … Continue reading Azure: Error while connecting to Azure SQL Server “Cannot open server ‘…’ requested by the login. Client with IP address ‘…’ is not allowed to access the server”
SSIS: The component returned a failure code when the pipeline engine called PrimeOutput()
Recently, while working on SSIS package, we encountered the error after few minutes of execution of package. To get rid of this issue, we implemented one workaround: Go to Data Flow --> Properties Decrease the default buffer size to 1MB i.e. 1048576 Bytes Decrease the default Max Buffer rows to 1000 as shown below: After … Continue reading SSIS: The component returned a failure code when the pipeline engine called PrimeOutput()
SSIS: The column with the sortkeyposition value of 1 is not valid. It should be 0
Recently, while working on one of the existing SSIS package, we found that there were number of sort blocks in SSIS where as it could have been sorted at the source itself and informing the SSIS package that the data is already sorted at the source and can be processed further. Another disadvantage of using … Continue reading SSIS: The column with the sortkeyposition value of 1 is not valid. It should be 0
SSIS is Case Sensitive
Yes, SQL is not case sensitive but SSIS is. Recently we came across an issue which was very difficult to trace and find the root cause. The requirement was to update the sales data for each product. The logic was working fine for all the records and data was getting updated properly except for couple of … Continue reading SSIS is Case Sensitive
Ajit Patra 