SSIS 2016 - Command Line Deployment for Projects

Project based deployment can be automated from the command line.

I create a batch file for this and put PAUSE at the end so that I can review the output before the window closes!

isdeploymentwizard.exe /Silent /ModelType:Project /SourcePath:"E:\Codebase\SSIS\SSIS Solutions\My Solution\ProductionPackages\bin\Development\My Solution.ispac" /DestinationServer:"LIVESERVER.DOMAIN.LOCAL" /DestinationPath:"/SSISDB/Folder/My Solution"

pause

SSIS : The Lookup Component (bookmarks)

No point in reinventing the wheel, but some great reading on the Lookup component and some methods to adopt when using it.

SSIS 2008 lookup component
http://consultingblogs.emc.com/jamiethomson/archive/2007/11/16/Katmai_3A00_-SSIS_3A00_-Lookup-component-gets-a-makeover.aspx


Lookup Component Cache Modes
http://blogs.msdn.com/mattm/archive/2008/10/18/lookup-cache-modes.aspx

Using the SQL 2008 Lookup and Cache Transforms in a SQL Server Integration Services (SSIS) Package
http://www.mssqltips.com/tip.asp?tip=1511

SSIS : Incremental persistent cache updates
http://blogs.msdn.com/mattm/archive/2008/11/23/lookup-pattern-incremental-persistent-cache-updates.aspx

SSIS 2008 - Incrementally Updating the Lookup Cache File
http://agilebi.com/jwelch/2008/05/06/ssis-2008-incrementally-updating-the-lookup-cache-file/

Cascading Lookup
http://blogs.msdn.com/mattm/archive/2008/11/22/lookup-pattern-cascading.aspx

Lookup component - Ensure varchar columns are not padded
http://consultingblogs.emc.com/kristianwedberg/archive/2006/02/22/2955.aspx

Handling Early Arriving Facts in SQL Server Integration Services SSIS
http://www.mssqltips.com/tip.asp?tip=1446

SSIS : Error Handling & Checkpoints

SSIS : Error Handling & Checkpoints

Handling Multiple Errors
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx
http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx

Data Task Error Handler
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62662/

Package Level Error Handler
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

Checkpoints and Package Restarting
http://community.altiusconsulting.com/blogs/altiustechblog/archive/2009/09/09/ssis-2008-package-restarting-and-checkpoints.aspx

How to serialize error logging in SSIS
http://www.mssqltips.com/tip.asp?tip=1967

Implementing Logging in Packages
http://msdn.microsoft.com/en-us/library/ms138020.aspx

SSIS : Performance

SSIS : Performance

Faster Extraction Loading using SSIS
http://www.sqllion.com/2009/04/faster-extraction-loading-by-ssis/

Microsoft Whitepaper on SSIS 2005 Performance
http://technet.microsoft.com/en-gb/library/cc966529.aspx

Performance
http://jenstirrup.blogspot.com/2009/05/improving-data-flow-performance-in-ssis.html

SSIS Package Performance
Part 1 : Data Flow Task http://www.ssisbi.com/optimizing-ssis-package-performance-part-1/
Part 2 : Data Flow Components http://www.ssisbi.com/optimizing-ssis-package-performance-part-2-the-data-flow/

SSIS : Best Practices

SSIS : Best Practices

Top 10 SSIS Best Practices
http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

Best Practices
http://blogs.vertigo.com/personal/bellis/Blog/archive/2007/07/23/sql-server-integration-services-ssis-etls-and-best-practices.aspx

Best Practices & Naming Conventions
http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx

SSIS : Basics

Am finally getting round to tidying up my favourites and notes I've made during my recent SSIS projects. This is the first of 4 bookmark posts on SSIS reading material.

SSIS : Basics

MSDN
http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx

Technet
http://technet.microsoft.com/en-us/sqlserver/cc510302.aspx

SSIS 2008 Cribsheet
http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-ssis-cribsheet/

SSIS Data Types mapping to SQL Data Types
http://pragmaticworks.com/community/blogs/brianknight/archive/2008/02/16/ssis-connection-manager-data-types-mapping-to-sql-server-data-types.aspx

SSIS Operators
http://msdn.microsoft.com/en-us/library/ms137538.aspx

Integration services 2008 videos
http://msdn.microsoft.com/en-us/library/dd299421.aspx

SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64014/#

Bookmark : Why not use an SSIS SQL Server Destination

The opposite advice now applies, interesting....

BIDN : Why not use an SSIS SQL Server Destination (requires registration)

SSIS : OLEDB Error Codes

Some error messages I (ssis) generated today - 

-1071607116 : A rowset based on the SQL command was not returned by the OLE DB provider.

-1073450982 : component "Component Name" (1) failed the pre-execute phase and returned error code 0xC02092B4.

I had a strange sense of deja-vu and hence have to point myself back to this post to sort it!

Solace : SSIS, OLEDB and Stored Procedures

SSIS , OLEDB and Stored Procedures

SSIS , OLEDB and Stored Procedures.

SSIS cannot 'see' the metadata for returned data from stored procedures like it can for tables and views. Here is a collection of methods to try when using sprocs with SSIS.

On oledb connection managers, set -

DelayValidation = True

On data sources and components inside the data flow, set -

ValidateExternalMetaData = False

When calling your stored procedure, prefix as follows -

"SET FMTONLY OFF; EXEC dbo.myprocedure @param = 1"

In the stored procedure itself, create a header as follows -

CREATE PROCEDURE dbo.myprocedure (@param INT)
AS
BEGIN

-- {options to set...}
SET NOCOUNT ON; -- {supress 'rows affected' messages}

SET FMTONLY OFF; -- {ensure full data retuned. incase FMTONLY ON was in effect}


-- {false header to pass metadata}

IF 1 = 0
 BEGIN
 SELECT
   CAST(NULL AS INT) AS [intColumn1]
 , CAST(NULL AS INT) AS [intColumn2]
 , CAST(NULL AS VARCHAR(5)) AS [varcharColumn1] 
 , CAST(NULL AS VARCHAR(255)) AS [varcharColumn2] 
 , CAST(NULL AS DATETIME2(0)) AS [datetime2Column1]
     , CAST(NULL AS VARBINARY(20)) AS [varbinaryColumn1]  
 END

-- {genuine query}

SELECT intColumn1, intColumn2, varcharcolumn1 ...

END
GO

SSISTalk: Phil Brammer - Stored Procedures and the OLE DB Source
ReplicationAnswers : Coping with no column names in the oledb source

Bookmark : SSIS Expression Editor & Tester

Allan Mitchell and Darren Green of SQLIS/Konesans have developed a tool for editing and testing SSIS Expressions.

If you've ever used SSIS in depth, you'll appreciate just how helpful this will be.

It's hosted over on codeplex >
CodePlex : SSIS Expression Editor & Tester

Thanks guys, will be trying it out shortly.

SSIS : Tuning Buffer Size

Tuning the Buffer Size for the data flow task in SSIS

The buffer size used is the smaller of 2 figures.

1) defaultbuffersize
2) defaultmaxbufferrows * row width

defaultbuffersize has a default of 10 MB (10485760 B)

You can change this to amaximum of 100 MB (104857600 B), which I have done and is the figure I have used here.

Calculating Row Width

I'm using a simple example of a single target table here.

The row width calculation gets messy if you try it from the table definition i.e. adding the sizes of columns (and remembering 2 bytes per character for unicode columns etc)

I chose to use the table size data and work out the average -

sp_spaceused 'dbo.bigtable'

name	rows	reserved	data	index_size	unused
bigtable	144451461	63064384 KB	44740776 KB	18317304 KB	6304 KB

So, data size in KB 44740776

data in Bytes = 44740776 * 1024 = 45814554624 B

Divide by row count ,
= 45814554624 / 144451461

317.16 Bytes/row

Row Width = 317 Bytes.

From the row width figure and the desire to put as much in the buffer as possible (to match the 100MB defaultbuffersize) we can calculate defaultmaxbufferrows.

Calculating DefaultMaxBufferRows

100MB buffer = 104857600 bytes
Row Width = 317 Bytes.

defaultmaxbufferrows = BufferSize / Row Width

= 104857600 / 317

= 330781 rows

So to ensure maximum buffer usage here we set -

defaultbuffersize = 104857600
defaultmaxbufferrows = 330781 (though I'd most likely round down to a the nearest hundred).

SSIS : OLEDB Destination and Maximum Insert Commit Size

SSIS : OLEDB Destination and Maximum Insert Commit Size

Default is 0 in SSIS 2005.

Default is 2147483647 in SSIS 2008.

Source : SSIS Junkie : Default value for OLE DB Destination FastLoadMaxInsertCommitSize in SQL Server 2008

"Using 0 (2005) or 2147483647 (2008) gives basically the same behavior - all records are committed as a single batch. Depending on exactly what you are doing, you may get better performance by setting MICS to a value between 10,000 and 100,000 - particularly if you have a clustered index on the table you are loading "
from - MSDN SSIS forums

Further reading -
Making Fast Load really fast on clustered indexed tables with SSIS

SQL Server Integration Services (SSIS) - Best Practices

Using Maximum Insert Commit Size with FAST LOAD on the OLE DB Connector, and a Warning

More SSIS Obscurity (SSIS error 0xC0010009)

Another long winded, difficult to decipher error message from SSIS today..

[Pass Maximum ID Back as variable [378]] Error: System.Runtime.InteropServices.COMException (0xC0010009): Exception from HRESULT: 0xC0010009
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)

Posting this incase anyone else (like me) finds themself googling SSIS error 0xC0010009.
It translates to 'Item in a collection not found' and in my case was caused by me using camelCase variable names in my SSIS package and attempting to reference them in lowercase. Argh!!!! Note to self, variable names are CASE SENSITIVE!
 
http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0010009.html

SSIS Contol Flow - For Loop with Breakout

Some screenshots on how to set up a For Loop that repeats until a variable is set.
The Variable is called Breakout in this example >

Bookmark : SSIS Tuning Tips

A timely post considering my workload right now, Jamie Thomson's SSIS Lookup Component Tuning Tips
Especially the ‘thinking outside the box’ section regarding dividing up lookup tasks.

(useful, will me trying out asap)

SSIS Annoyance ; Derived Column Data Types

SSIS wants to to use a Unicode String when you know a non-unicode will do.

Found this today, and it is covered perfectly here >

BI Monkey : SSIS Derived Column forcing data type changes

SSIS : Package Execution - Start without Debugging

As easy as it is to debug, running a package through BIDs prior to deployment is SLOW.

Hence the option many seem to miss, 'Start without debugging' >
Select it from the Debug window or use CTRL+F5 to do this.
The package then runs in a command window a lot faster (shown below).

SSIS Contol Flow - For Loop with Counter

Remove Splash screen from Visual Studio Startup

If you want a few seconds back every day, add this /NOSPLASH switch to your shortcut...

SSIS : ADO.NET Stored Procedure Performance

Andy Leonard's article 'SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers' has been updated. One of the new parts shows a considerable performance improvement when using an ADO.NET connection and setting IsQueryStoredProcedure to true (when using SPs).