SSIS Development Best Practices

After searching for many Best practices and finally we found some which we fallow regularly

Development Best Practices

1.       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
  1. Only select columns that you need in the pipeline to reduce buffer size and reduce On Warning events at execution time
  2. 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 transactions if they are being implemented.
  3. 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.
5.       Use naming conventions for your tasks and components
The acronyms below should 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

6.       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
7.       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.
8.       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
9.       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
  1. 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)
11.    Use annotations wherever possible. At the very least each data-flow should contain an annotation
  1. Don't include connection-specific info (such as server names, database names or file locations) in the names of your connection managers. For example, "OrderHistory" is a better name than "Svr123ABC\OrderHist.dbo".
13.    Change the network packet size in the connection manager  , Higher value typically yield faster through put   Max value : 32767

Comments

Post a Comment

Popular posts from this blog

SSAS : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). + Solution

Problem : have installed Analysis service on my server machine through remote login from my service account. I am able to open Analysis services from there. When I open SSMS from the same service account on my local machine, I am not able to connect to that Analysis service. It is giving me error by saying : "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond 10.245.192.111:2382(System)." Solution : Based on my research, the issue was caused by the Browser Service account was changed to the domain account. In your scenario, you can try to connect to the Analysis Service using <Servername>:2383 instead of instance name. Or you can change the Browser Service account to "Local System"....
Read more

SSIS Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces

Image
Problem : When we have Multiple Namespaces in the Xml Source which we need to load the Data from   as we shown below Then we will face a issue in generating XSD for that XML Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio) Solution : First we need to avoid multiple namespaces to transform your source file to a format that doesn’t refer to the namespaces. SSIS has an XML task that can do the transformation. 1) Add the XML task to an SSIS   Control Flow and edit it. 2) Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem. 3) Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file ( ModifiedInput.XML). 4) Add the below script to a new file and save it with an xslt file extension (Input.XSL...
Read more

SSRS : [Teradata Database] 3939 There is a mismatch between the number of parameters specified and the number of parameters required.

Error : [Teradata Database] 3939   There is a mismatch between the number of parameters specified and the number of parameters required. When we get this Error : when we are creating SSRS reports on Teradata database and in which when we are using parameters in the dataset in wrong style\format. Just for Info :  to connect Terdata we need to have  .Net Data provider for Terdata  when we installed this on our machine  Then we are able to see Teradata data source type in SSRS. How to Solve this issue :  This error will come if we are not using correct syntax to create parameterized data set .  The correct way is we need to ?  for parameter The report parameters can be named or unnamed. Teradata only supports unnamed parameters. An unnamed parameter is denoted by a ‘?’, and is merely a placeholder for data that is going to be entered at report processing time. For example, the following is a dataset with a single-value ...
Read more