A URL can be broken up into multiple parts. 1 If you break up the URL into its parts, how do you put all the pieces together to form the full URL in Azure Synapse Analytics (#AzureSynapse) pipelines or Azure Data Factory? This post gives an example of how to create a valid URL from […]
Azure Synapse Analytics pipeline expression to create dynamic URL Read More »
One of the things that messes up processing delimited files is when the delimiter character is actually part of the data rather than acting as a delimiter. So look at the following: The second line has 4 commas but the first has only 3 commas. The second comma is actually part of the data: One
Selective text qualifiers in SSIS Read More »
When using dynamic SQL there is always the possibility that someone will inject SQL that does harm. They can put queries that can drop objects or can create sysadmin logins. Running the scripts as low permission users can reduce the potential harm. But that takes time to research and is easy to cut. You can
Limiting permissions with “Execute as” when using dynamic SQL Read More »
One of the tasks that eats a lot of my ETL development time is documenting the views and procedures I write. I need to know where exactly the data is coming from. sp_describe_first_result_set provides a flag for outputting the source for many of the columns in a query. exec sys.sp_describe_first_result_set N’exec [Integration].[GetOrderUpdates] ”20100101”, ”20170122”’, null,
Getting metadata from query using sp_describe_first_result_set Read More »
Recently someone posed a puzzling question on the MSDN forums (How to reorder a column in a table which also has a foreign key). They needed to be able to add columns to a table in a specific order. If you know basics of relational theory, you would understand that the order of columns in
Using view as source for application Read More »
One problem that you might encounter with data is where you are gathering information from multiple lines and want to output only one line. If you are going to simply aggregate the results, then you can use the Aggregate transformation. But what do you do if you want to create simply append one row’s value
SSIS Script Component – Last Row Read More »
When you pivot data, there are three dimensions that you need to set: the Set Keys, the Pivot Keys and the Pivot Values (to use the SSIS terms). In Excel it is very easy to select multiple Set Keys (ROWS), multiple Pivot Keys (COLUMNS) and even multiple Pivot Values (Values). However, using the SSIS Pivot
SSIS Pivot Transformation with Multiple Set Keys Read More »
One of the problems I have encountered is trying to unpivot a file that has a variable number of columns. There are a set of columns that are fixed, that represent the Row keys. And then there are variable numbers of columns. This is especially true when your columns are based on dates. This sample
SSIS Flexible UnPivot Read More »
Sometime your data source has duplicates. SSIS has an out of the box tool to remove these duplicates. The Sort transformation has a “Remove rows with duplicate sort values” property (https://www.mssqltips.com/sqlservertip/3036/removing-duplicates-rows-with-ssis-sort-transformation/). But it has a problem. It is very random as to the values that it removes. How do you specify which row to remove?
SSIS Removing Duplicate Rows Using Conditions Read More »
One of the regular issues that is discussed on the SSIS forum is what to do when a source file changes the order of columns. You build your SSIS package to handle Col1, Col2, Col3 and you get the files with the following order Col2, Col3, Col1. Or worse, you get Col2, Col1. How do
Reordering Columns Using PowerShell Read More »
You must be logged in to post a comment.