Search results matching tags 'SSIS' and 'repost'

Suggested Best Practises and naming conventions

jamiet — Sun, 29 Jan 2012 18:08:53 GMT

Once upon a time I blogged at http://consultingblogs.emc.com/jamiethomson but that ended in August 2009 when I left EMC. There is a lot of (arguably) valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.

This is the third such blog post in which I suggest some best practises and naming conventions that you may choose to employ and which was originally published here (I have changed it slightly since then – spotters badge if you can find the differences!). The first post in this series can be found at [SSIS] OnPipelineRowsSent and the second at Dataflow mechanics.

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices. These are my own opinions and are based upon my experience of using SSIS over the past 18 months. I am not saying you should take them as gospel but these are generally tried and tested methods and if nothing else should serve as a basis for you developing your own SSIS best practices.

One thing I really would like to see getting adopted is a common naming convention for tasks and components and to that end I have published some suggestions at the bottom of this post.

This list will get added to over time so if you find this useful keep checking back here to see updates!

If you know that data in a source is sorted, set IsSorted=TRUE on the source adapter output. This may save unnecessary SORTs later in the pipeline which can be expensive. Setting this value does not perform a sort operation, it only indicates that the data it sorted.
Rename all Name and Description properties from the default. This will help when debugging particularly if the person doing the debugging is not the person that built the package.
Only select columns that you need in the pipeline to reduce buffer size and reduce OnWarning events at execution time
Following on from the previous bullet point, always use a SQL statement in an OLE DB Source component or LOOKUP component rather than just selecting a table. Selecting a table is akin to "SELECT *..." which is universally recognised as bad practice. (http://www.sqljunkies.com/WebLog/simons/archive/2006/01/20/17865.aspx). In certain scenarios the approach of using a SQL statement can result in much improved performance as well (http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx).
~~Use SQL Server Destination as opposed to OLE DB Destination where possible for quicker insertions~~ I used to recommend using SQL Server Destinations wherever possible but I've changed my mind. Experience from around the community suggests that the difference in performance between SQL Server Destination and OLE DB Destination is negligible and hence, given the flexibility of packages that use OLE DB Destinations it may be better to go for the latter. Its an "it depends" consideration so you should consider what you prefer based on your own testing.
Use Sequence containers to organise package structure into logical units of work. This makes it easier to identify what the package does and also helps to control distributed transactions if they are being implemented.
Where possible, use expressions on the SQLStatementType property of the Execute SQL Task instead of parameterised SQL statements. This removes ambiguity when different OLE DB providers are being used. It is also easier. (UPDATE: There is a caveat here. Results of expressions are limited to 4000 characters so be wary of this if using expressions).
Use caching in your LOOKUP components where possible. It makes them quicker. Watch that you are not grabbing too many resources when you do this though.
LOOKUP components will generally work quicker than MERGE JOIN components where the 2 can be used for the same task (http://blogs.conchango.com/jamiethomson/archive/2005/10/21/2289.aspx). Note that this is not always the case so test and measure, test and measure, test and measure!
Always use DTExec to perf test your packages. This is not the same as executing without debugging from SSIS Designer (http://www.sqlis.com/default.aspx?84).
Use naming conventions for your tasks and components. I suggest using acronyms at the start of the name and there are some suggestions for these acronyms at the end of this article. This approach does not help a great deal at design-time where the tasks and components are easily identifiable but can be invaluable at debug-time and run-time. e.g. My suggested acronym for a Data Flow Task is DFT so the name of a data flow task that populates a table called MyTable could be "DFT Load MyTable".
If you want to conditionally execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the "Disable" property of the task.
Don't pull all configurations into a single XML configuration file. Instead, put each configuration into a seperate XML configuration file. This is a more modular approach and means that configuration files can be reused by different packages more easily.
If you need a dynamic SQL statement in an OLE DB Source component, set AccessMode="SQL Command from variable" and build the SQL statement in a variable that has EvaluateAsExpression=TRUE. (http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx)
When using checkpoints, use an expression to populate the CheckpointFilename property which will allow you to include the value returned from System::PackageName in the checkpoint filename. This will allow you to easily identify which package a checkpoint file is to be used by.
When using raw files and your Raw File Source Component and Raw File Destination Component are in the same package, configure your Raw File Source and Raw File Destination to get the name of the raw file from a variable. This will avoid hardcoding the name of the raw file into the two seperate components and running the risk that one may change and not the other.
Variables that contain the name of a raw file should be set using an expression. This will allow you to include the value returned from System::PackageName in the raw file name. This will allow you to easily identify which package a raw file is to be used by. N.B. This approach will only work if the Raw File Source Component and Raw File Destination Component are in the same package.
Use a common folder structure (http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)
Use variables to store your expressions (http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx). This allows them to be shared by different objects and also means you can view the values in them at debug-time using the Watch window.
Keep your packages in the dark (http://www.windowsitpro.com/SQLServer/Article/ArticleID/47688/SQLServer_47688.html). In summary, this means that you should make your packages location unaware. This makes it easier to move them across environments.
If you can, filter your data in the Source Adapter rather than filter the data using a Conditional Split transform component. This will make your data flow perform quicker.
When storing information about an OLE DB Connection Manager in a configuration, don't store the individual properties such as Initial Catalog, Username, Password etc... just store the ConnectionString property.
Your variables should only be scoped to the containers in which they are used. Do not scope all your variables to the package container if they don't need to be.
Employ namespaces for your packages
Make log file names dynamic so that you get a new logfile for each execution.
Use ProtectionLevel=DontSaveSensitive. Other developers will not be restricted from opening your packages and you will be forced to use configurations (which is another recommended best practice)
Use annotations wherever possible. At the very least each data-flow should contain an annotation.
Always log to a text file, even if you are logging elsewhere as well. Logging to a text file has less reliance on external factors and is therefore most likely to contain all information required for debugging.
Create a new solution folder in Visual Studio Solution Explorer in order to store your configuration files. Or, store them in the 'miscellaneous files' section of a project.
Always use template packages to standardise on logging, event handling and configuration.
If your template package contains variables put them in a dedicated namespace called "template" in order to differentiate them from variables that are added later.
Break out all tasks requiring the Jet engine (Excel or Access data sources) into their own packages that do nothing but that data flow task. Load the data into Staging tables if necessary. This will ensure that solutions can be migrated to 64bit with no rework.
Don't include connection-specific info (such as server names, database names or file locations) in the names of your connection managers. For example, "OrderHistorySystem" is a better name than "Svr123ABC\OrderHist.dbo".

The acronyms below can be used at the beginning of the names of tasks to identify what type of task it is.

Task	Prefix
For Loop Container	FLC
Foreach Loop Container	FELC
Sequence Container	SEQC
ActiveX Script	AXS
Analysis Services Execute DDL	ASE
Analysis Services Processing	ASP
Bulk Insert	BLK
Data Flow	DFT
Data Mining Query	DMQ
Execute DTS 2000 Package	EDPT
Execute Package	EPT
Execute Process	EPR
Execute SQL	SQL
File System	FSYS
FTP	FTP
Message Queue	MSMQ
Script	SCR
Send Mail	SMT
Transfer Database	TDB
Transfer Error Messages	TEM
Transfer Jobs	TJT
Transfer Logins	TLT
Transfer Master Stored Procedures	TSP
Transfer SQL Server Objects	TSO
Web Service	WST
WMI Data Reader	WMID
WMI Event Watcher	WMIE
XML	XML

These acronyms should be used at the beginning of the names of components to identify what type of component it is.

Component	Prefix
DataReader Source	DR_SRC
Excel Source	EX_SRC
Flat File Source	FF_SRC
OLE DB Source	OLE_SRC
Raw File Source	RF_SRC
XML Source	XML_SRC
Aggregate	AGG
Audit	AUD
Character Map	CHM
Conditional Split	CSPL
Copy Column	CPYC
Data Conversion	DCNV
Data Mining Query	DMQ
Derived Column	DER
Export Column	EXPC
Fuzzy Grouping	FZG
Fuzzy Lookup	FZL
Import Column	IMPC
Lookup	LKP
Merge	MRG
Merge Join	MRGJ
Multicast	MLT
OLE DB Command	CMD
Percentage Sampling	PSMP
Pivot	PVT
Row Count	CNT
Row Sampling	RSMP
Script Component	SCR
Slowly Changing Dimension	SCD
Sort	SRT
Term Extraction	TEX
Term Lookup	TEL
Union All	ALL
Unpivot	UPVT
Data Mining Model Training	DMMT_DST
DataReader Destination	DR_DST
Dimension Processing	DP_DST
Excel Destination	EX_DST
Flat File Destination	FF_DST
OLE DB Destination	OLE_DST
Partition Processing	PP_DST
Raw File Destination	RF_DST
Recordset Destination	RS_DST
SQL Server Destination	SS_DST
SQL Server Mobile Destination	SSM_DST

Dataflow mechanics [SSIS]

jamiet — Tue, 25 Oct 2011 07:48:00 GMT

Once upon a time I blogged at http://consultingblogs.emc.com/jamiethomson but that ended in August 2009 when I left EMC. There is a lot of (arguably) valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.
This is the second such blog post in which I discuss the internals of the SSIS Dataflow. The first post in this series can be found at [SSIS] OnPipelineRowsSent.

During my activity on the SSIS forum I've noticed that much of the content is in regard to the dataflow task and that's not a surprise given that its the most useful tool in the SSIS box and also the most complex. This post is me brainstorming some of the stuff that I know about the dataflow and hopefully it proves useful to some of you.

Buffer Architecture. If I'm ever interviewing you for a job as a SSIS developer you can lay a lot of money to say that I'll ask you to tell me what a buffer is. Buffers are fundamental to the dataflow - they are what the dataflow uses to move data around. A buffer is essentially an area of memory and by default consists of approximately 10000 rows (usually slightly less than that) and that's why when you execute a dataflow within BIDS the row counts on the data paths go up in approximate increments of 10000. Part of performance tuning a SSIS dataflow is about manipulating various properties until you find the optimum number of rows in each buffer and you can read more (much more) about that here.
Dataflows contain components which are generally categorised into synchronous and asynchronous. The most definitive description of these is that the output from a synchronous component uses the same buffer as the input; asynchronous components create a new buffer for their output. All source adapters are asynchronous components, all destination adapters are synchronous. Synchronous components are generally quicker than asynchronous components.
Asynchronous components are further categorised as partially-blocking or fully-blocking. Fully-blocking components require all rows from upstream before they put any data into the output; partially-blocking components will start to output data before they receive all upstream rows.
Execution trees. Each asynchronous component creates what is called an execution tree in the dataflow. In SSIS 2005 (but not in later versions) each execution tree uses one execution thread so another part of performance tuning is to fully utilise all processors on your hardware. Read more here.
OnPipelineRowsSent. All executables in a SSIS package throw events and one of the events throws by the dataflow is OnPipelineRowsSent. When a component outputs a buffer of data then it throws a OnPipelineRowsSent event and thus enables us to know how many rows each component has processed. When you execute a dataflow within the development environment (aka BIDS) these events are consumed and are used to change the rowcounts that you see increasing as more rows are processed.
Spooling. I said earlier that all buffers are a space in memory but of course memory is finite so if there is more data in the pipeline than can fit in memory then buffers will get spooled to disc. The location on disc is defined by BLOBTempStoragePath & BufferTempStoragePath. Spooling will severely impact dataflow performance so avoid if possible.
A lot of people ask if its possible to remove columns from the dataflow once they have finished using them. For example, if columns called [FirstName] & [LastName] are concatenated together to make [FullName] its likely that those two columns won't be needed anymore. The simple answer though is no. Once the data is in memory it would be an overhead to remove the data and "squeeze" the buffer up to make it slower which is why those columns still appear downstream. This is nothing to be concerned about - its highly highly unlikely they are heavily impacting performance. Of course, if an asynchronous component is encountered then a new buffer will be created on the output and the unrequired columns will (probably) be removed. This issue is further discussed here.
Following on from the previous point...its intuitive to think that columns that begin at a component don't exist prior to the data being processed by that component. In fact that's not true. Prior to dataflow execution the execution plan for a dataflow is determined and it is at that point that all columns are defined and thus created (i.e. space is set aside in memory). So, all columns that will be used in a buffer exist even before the buffer gets any data.
The datatypes of columns in the dataflow are different from datatypes used for SSIS variables. To this day I don't understand why the SSIS team opted to use different datatypes in the control flow and data flow and I hope this changes one day. (UPDATE: SSIS Development Manager Jeff Bernhardt addresses this issue in A potted SSIS history via Connect.)
The stock components (i.e. those provided out-of-the-box) are mostly written in native code (XML Source and the Script Component are exceptions to this rule). SSIS provides a .Net API that enables you and I to build our own components and hence it is tempting to think that these custom components won't work as quickly as stock components. This is probably true but really the difference is negligible. The majority of the work (validation, memory management, buffer editing etc...) is done by native code so you're not going to suffer severe performance problems by implementing custom components.
The BLOB data types (i.e. DT_TEXT, DT_NTEXT, DT_IMAGE) can severely impact dataflow performance so try and avoid them if you can.
Raw files can be used to pass data from one dataflow to another - even if those dataflows are in different packages. Raw files have a proprietary file format that is essentially a match of the data in memory and hence reading to and writing from them is extremely quick. People often seem reticent to place data into raw files but I don't hesitate to recommend using them if you need to.
There is an important property on each component output called IsSorted. A lot of people think that setting this property to TRUE will cause the data in that output to be sorted. That's not true - this property only informs the dataflow engine that the data is sorted, nothing more. If you set this property to TRUE and the data is not sorted then you will probably be creating problems for yourself later on (for example a downstream Merge Join component will not fail but it won't produce the correct results either).
Source and destination adapters maintain external column collections which are used to store the metadata of the external data sinks that those adapters connect to. There are two reasons for this as far as I can determine. Firstly to enable offline development (a big criticism of SSIS's predecessor DTS was that offline development wasn't possible) and secondly to enable the dataflow to validate itself. More information here.
Although it appears in BIDS as though the data in a buffer "moves" from one component to another that isn't actually the case. Data in a buffer doesn't actually move about in memory. My fellow MVP Phil Brammer (blog | twitter) once used an analogy of cars travelling on a road to describe this. The buffers are analogous to cars on the road and milestones along the road are analogous to the components. Instead of thinking of the cars moving along the road to reach the milestones, think of the cars as being stationery and the road moving along underneath the cars.
Back pressure is an important concept in an SSIS dataflow. Backpressure occurs when a dataflow is producing data to a destination faster than the destination can consume it (a common phenomenon when inserting into a relational database table) - this creates contention further back down the dataflow, hence the term "backpressure". Michael Entin (one of the original developer geniuses that built the dataflow engine) talks more about back pressure at SSIS Backpressure Mechanism.

I'll probably add to this post over time as new things occur to me. In the meantime if you want a more detailed description of how the dataflow works then Kirk Haselden's book has a whole chapter devoted to it. You can also pose questions in the comments although I'd urge you to post questions to the SSIS forum where more people will be available to answer and where your question may already have been answered.

@Jamiet

[SSIS] OnPipelineRowsSent event

jamiet — Fri, 19 Aug 2011 11:48:00 GMT

Once upon a time I blogged at http://consultingblogs.emc.com/jamiethomson but that ended in August 2009 when I left EMC. There is a lot of valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.
This is the first such blog post in this series in which I talk about the little-known OnPipelineRowsSent event.

An interesting discussion today on the SSIS Forum made me realise that perhaps not all SSIS developers are aware of the OnPipelineRowsSent event in SSIS and how valuable it can be, certainly when debugging data-flows. This isn't all that surprising because Books Online is very sparse in this area. Hence I thought it would make a useful blog topic.

OnPipelineRowsSent is a type of event in SSIS which means the information that it provides can be logged by whatever log provider you happen to be using. In order to understand the information that OnPipelineRowsSent provides it is critical to understand the buffer architecture of the SSIS data-flow.

To explain that VERY simply, a buffer is an area of memory that contains rows of data as they pass through the pipeline. Each data-path in the data-flow will contain one of more buffers and each of those buffers (except for the last one) will contain the same number of rows. If you want to understand more about the buffer architecture of the data-flow then Kirk Haselden's book has a chapter devoted wholly to it.

Here is what Books Online DOES say about OnPipelineRowsSent:

OnPipelineRowsSent

Reports the number of rows provided to a component input by a call to the ProcessInput method. The log entry includes the component name.

In slightly plainer english that means that for every buffer of data that is consumed by a component, you get a log entry. So, if you have (e.g.) a Derived Column Component that receives 105000 rows and the size of the buffer is 10000 rows, you'll get 11 OnPipelineRowsSent log entries for that component (there will only be 5000 rows in the last buffer). Basically it enables you to know how many rows are passing through each data-path in the data-flow.

The available information is the same as you get for any eventhandler. i.e.

event
computer
operator
source
sourceid
executionid
starttime
endtime
datacode
databytes
message

and here is a portion of a logfile containing those records:

OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1228 : Merge Join Output : 1225 : Union All 1 : 1226 : Union All Input 1 : 9936
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1475 : Union All Output 1 : 1470 : Sort 3 : 1471 : Sort Input : 9936
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1228 : Merge Join Output : 1225 : Union All 1 : 1226 : Union All Input 1 : 2085
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1475 : Union All Output 1 : 1470 : Sort 3 : 1471 : Sort Input : 9936
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1475 : Union All Output 1 : 1470 : Sort 3 : 1471 : Sort Input : 2085
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 2121 : Sort Output : 2113 : Merge Join 2 : 2114 : Merge Join Left Input : 9936

The important stuff is what comes in the last field, the message so let's break that down. Here is an example message:

Rows were provided to a data flow component as input. : : 1030 : OLE DB Source Output : 1025 : Sort 2 : 1026 : Sort Input : 9972

And here's what it is constituted of:

Rows were provided to a data flow component as input. - That's consistent in each message, so to be honest they could have left it out
1030 - ID of the data-path providing the buffer
OLE DB Source Output - Name of the data-path providing the buffer
1025 - ID of the component receiving the buffer
Sort 2 - Name of the component receiving the buffer
1026 - ID of the input receiving the buffer
Sort Input - Name of the input receiving the buffer
9972 - Number of rows in the buffer

As you can see, this information will enable you to determine exactly how many rows are output from each component. Thus, if you are not receiving the expected number of rows at a destination this event will help you to deduce where you are losing them from.

You may not know this but you actually encounter OnPipelineRowsSent every time you run a data-flow within the SSIS Designer in BIDS. Notice how the designer shows the number of rows passing through the pipeline and through each component:

How does the designer know how many rows are passing through the data-flow? Simple! Its because the SSIS Designer consumes the OnPipelineRowsSent event that the executing package "throws" up and then presents that information visually as the numbers that you see flashing in front of your eyes as the data-flow executes.

I think that just about covers the simple stuff around OnPipelineRowsSent. If you have any questions then post them here as a comment.

@Jamiet