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
Category: SSIS
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
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
SSIS: Excel Connection Manager Error “Class not registered”
Recently, we were working on SSIS to get data from AX to the staging tables. As part of the requirement, we were also using Excel Connection Manager to read the data from one of the excel file and load it to a table. After completing the development, we executed the package and it was successfully … Continue reading SSIS: Excel Connection Manager Error “Class not registered”
SSIS: Login failed for user “NT AUTHORITY\ANONYMOUS LOGON”
Recently, we were working on SSIS for ETL(Extract, Transform and Load) to get the data from AX and push to the staging tables. We were using linked server for AX and we were using Windows Authentication for connecting to it. While running the package from SSDT, it got executed successfully. So, we deployed the packages to … Continue reading SSIS: Login failed for user “NT AUTHORITY\ANONYMOUS LOGON”
SSIS: “The version number in the package is not valid. The version number can’t be greater that current version number”
Recently, while working on SSIS to get data from AX and putting in our staging tables, we faced a weird compatibility issue between SQL Server and SSDT version we were using. We were using database as SQL Server 2012 and SSDT for VS 2013. After completing our development, while executing the package from SSDT, it … Continue reading SSIS: “The version number in the package is not valid. The version number can’t be greater that current version number”
SSIS: An error occurred while assigning a value to the variable
Recently, while working on SSIS, we came across a scenario where we had to assign multiple values to multiple variables to use them in package later. We used "Execute SQL Task" to assign those values to the variables. The SQL query we used, was returning single row with 12 columns. We wanted to store the … Continue reading SSIS: An error occurred while assigning a value to the variable
SSIS: The parameter type for ‘@P1’ cannot be uniquely deduced; two possibilities are ‘sql_variant’ and ‘xml’
Recently, while working on SSIS we came across a situation to use parameters. We wanted to pass 2 parameters. We were using SQL Command as Data access mode in OLE DB Source Editor. After writing the query the below query: select ? from channelmaster where displaychannel=? When we clicked on parameters to provide the parameter … Continue reading SSIS: The parameter type for ‘@P1’ cannot be uniquely deduced; two possibilities are ‘sql_variant’ and ‘xml’
SSIS: The process cannot access the file ‘*.ispac’ because it is being used by another process
Recently, while developing an SSIS package we got the error "The process cannot access the file '*.ispac' because it is being used by another process". We tried to close SSDT and run it again but, we still got the same error while compiling. Then, after searching over internet, we got the solution: Go to Task … Continue reading SSIS: The process cannot access the file ‘*.ispac’ because it is being used by another process
Ajit Patra 