Problem Usually when developing SSIS packages, you want the project to run on different servers, e.g. development, test and production server. If you have many environment variables creating all of them is tedious and unnecessary work. I’ll show you how you can copy environment variables between SSIS catalogues that are located on different servers. Solution … Continue reading Copy environment variables between SSIS catalogues on different servers
Category: MSSQL Server, SSIS & ETL
I spent some time on developing ETL packages with SSIS and MSSQL Server. Here are some tips & tricks.
Accessing MSSQL Server with R (RSQLServer with dplyr)
Recently I have been starting to use dplyr for handling my data in R. It makes everything a lot smoother! My previous workflow – running an SQL query, storing the results as CSV, loading it in RStudio – is now history. With dplyr you can directly query data from many different databases in a very … Continue reading Accessing MSSQL Server with R (RSQLServer with dplyr)
Easy and efficient way to log overwriting of a directory in SQL Server Integration Services (SSIS)
Recently I had the problem that I had a File System Task that moved a file but whenever the file was already there the package failed. So I set OverwriteDestination to TRUE. But now I lost complete control over which files were just moved and which did overwrite some already existing directory. My desired result … Continue reading Easy and efficient way to log overwriting of a directory in SQL Server Integration Services (SSIS)
Workaround: Restore failed (MSSQL Server)
That a database restore fails, can be due to several reasons. I actually didn’t figure out why it didn’t work for me but I found a workaround. Since it is a very similar process I will also show you how you can copy a database on the same server with a different name. Idea 1: … Continue reading Workaround: Restore failed (MSSQL Server)
Downgrading an ETL project from SSIS 2014 to SSIS 2012
Recently I came into the situation that I had a SSIS package built with Visual Studio 2013 and SQL Server Data Tools (SSDT) 2014 which should be deployed to SQL Server 2012. Running the packages from Visual Studio 2013 and inserting data in a database on SQL Server 2012 worked, it also worked to deploy … Continue reading Downgrading an ETL project from SSIS 2014 to SSIS 2012
[SSIS tips] Error when file path variable is not set at design time
A few weeks ago I had to create a foreach container in an ETL package to loop over subdirectories of a folder. There are many tutorials out there for this purpose: microsoft-ssis.blogspot.co.at blogs.msdn.com/b/changliw/ In each iteration of the loop some files of each subdirectory where read. And since the path of these files were created … Continue reading [SSIS tips] Error when file path variable is not set at design time
[SSIS Errors] The process cannot access the file because it is being used by another process
This is an error message that took me a few hours because I could not find that particular solution on the internet and … I did not look properly at the SSIS output So I’d like to share it with you. Information: 0x402090DC at Load Observations, Flat File Destination [268]: The processing of file “D:\test\Log.txt” … Continue reading [SSIS Errors] The process cannot access the file because it is being used by another process