Script Task Project Properties in SSIS 2012

Calling external DLLs from a Script Task in SSIS 2012

The 2012 version of SQL Server has brought with it a number of new features, not least the new, Visual Studio 2010 shell-based Management Studio. However, the changes are more than just aesthetic, as I found out when I came to try and upgrade some SQL Server Integration Studio (SSIS) packages from SQL server 2008 to 2012. Most packages upgraded fine, but there were some issues when I tried to upgrade any packages that used Script Tasks containing references to external DLLs. Continue reading “Calling external DLLs from a Script Task in SSIS 2012” »

How To Define an XSD file for an SSIS XML Source using an expression

Invalid XML source in SSISI’ve been looking at migrating some SQL Server Integreation Services (SSIS) packages lately, and as part of the move, am parameterising a lot of settings to improve the ease of future configuration changes.  One of these changes involved a package that reads from an XML source and uses an XML Schema Definition (XSD) file to validate the source file.  However, with the XML source being in the data flow, you’re not allowed to set an expression on this property to assign a value at runtime.  Or at least not directly.  So, if you want to assign the XSD location from a variable or project parameter, just follow these simple steps. Continue reading “How To Define an XSD file for an SSIS XML Source using an expression” »

SQL Server Job Agent fails SSIS package with error: Option “/CALLERINFO” is not valid

I was editing some pre-existing SQL Server Agent jobs the other day, adding some interconnectivity and changing steps around as I’ve done hundreds of times before.  These job steps are used to execute SSIS packages (SQL Server Integration Services) on the server and have been running for months (years?) without fail.  However, after I made my changes they started failing on the steps I had edited, throwing the bizarre error:

Option “/CALLERINFO” is not valid.  The command line parameters are invalid.

Continue reading “SQL Server Job Agent fails SSIS package with error: Option “/CALLERINFO” is not valid” »

Example of required SSIS Control Flow

Inserting Large Variable Column Data Files with SQL Server Integration Services – Part 2

Far too long ago, I posted part 1 of an article solving the import of large data files with varying columns using SQL Server integration Services (SSIS).  This is the belated follow up.  In part 1, I explained some example file structures and how we approach the problem of inserting these files, which all have a varying number of columns with different data types.   The solution combines a mixture of SSIS script tasks, external C# code in a referenced DLL and a flexible SQL Server DB schema.  This post will walkthrough my overall SSIS Control Flow, as well as detail how we translate a code based, dynamic object model into something well-defined which we can insert into a SQL Server DB.

Continue reading “Inserting Large Variable Column Data Files with SQL Server Integration Services – Part 2” »

Inserting Large Variable Column Data Files with SQL Server Integration Services – Part 1

Just over a year ago I posted a question on my favourite programming Q&A site, Stack Overflow regarding an issue I was having with processing some large, variable column data files using SSIS (SQL Server Integration Services).  Thanks to the great community there, I received an answer rather quickly and was able to develop a solution to solve the problem.  I’ve been intending to document this for some time, as it’s a good solution, combining SSIS  Control and Data Flow logic with custom C# code and a flexible SQL server database schema.  I was working with a database schema which was set up to handle variable columns from a number of different files by transposing the columns into rows within the database.  The upside of this is that it could support any number of columns from different files, in any order and with any type of data.  The downside is that each row in a file, suddenly becomes row x column number of rows in the database, meaning processing large files can slow down exponentially if not handled correctly.

Continue reading “Inserting Large Variable Column Data Files with SQL Server Integration Services – Part 1” »