Wednesday, March 16, 2016

SSIS - Points to Ponder 1

01. SSIS Configuration File - C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml ( This file configures SSIS service )

02. A solution is a container that holds a series of projects.
03.           .dtsx      -> An SSIS package
.ds        -> A shared data source file
.sln       -> A solution file that contains one or more projects
.dtproj   -> An SSIS project file
.params -> A shared parameter file
.conmgr -> A shared connection manager

04. The Control Flow tab controls the execution of the package 

05. The Data Flow tab handles the movement of data.

06. When Package is executed, you enter debug mode and a new tab called "Progress" immediately opens. 

07. The Progress tab is where you go to debug when a package has a problem. 

08. You can also go to the Output window below to see a textual view of the same Progress tab. 

09. Once you stop debug mode by clicking the Stop button or by going to Debug -> Stop Debugging.

10. A package is essentially an xml file

11. Package Security is maintained in four options.
1. Encrypt sensitive with user key
2. Encrypt sensitive with password
3. Encrypt All with user key
4. Encrypt All with password

12. Microsoft encrypts your package by default with your Windows user key.
Windows User key is a key that protects your Windows user credentials on your PC.

13. A connection manager is a connection that can be leveraged and consumed once or many times in a package.

14. Project connection manager is a connection that can be leveraged from multiple packages.

15. When you upgrade packages to SSIS 2012, they are converted to use the package deployment model by default, which is the legacy way of developing packages. 

16. You can invoke the SSIS Package Upgrade Wizard by 
1. Simply opening your packages in SQL Server Data Tools (SSDT) 
2. By manually by running "SSISUpgrade.exe"
3. By the SSIS Package Upgrade Wizard executable.

17. If you want to fully upgrade your packages to use the new deployment model, you must run the "Integration Services Project Conversion Wizard".

18. The Integration Services Project Conversion Wizard does the below:
1. Apply project-level encryption.
2. Update Execute Package Tasks to use internal project references.
3. Replace configurations with project parameters.

CONTROL FLOW
------------
Using Precedence Constraints

19. Precedence constraints are the connections between the tasks that control the execution order of each task.
A predecessor task is any task with a precedence constraint that is connected to another task.

20. The precedence constraint arrows can be different colors (Red (failure), Blue (completion) and Green (success)) to represent different commands. 
They can also have an fx logo to represent an expression.

21. In the Precedence Constraint Editor, you can also set the logical AND/OR for the preceding task. 
In case of multiple predecessor tasks, you can configure the task to execute
        1. only if the group of predecessor tasks has completed (logical AND), 
            the connecting lines are solid
2. if any one of the predecessor tasks has completed (logical OR), 
            the lines are dotted (even if one or more predecessor tasks have failed)

22. The Precedence Constraint Editor has the below evaluation operations:
1. Constraint -> Evaluates the success, failure, or completion 
                                        of the predecessor task or tasks
2. Expression -> Evaluates the success of a customized condition 
                                        that is programmed using an expression
3. Expression and Constraint -> Evaluates both the expression and 
                                        the constraint before moving to the next task
4. Expression or Constraint -> Determines if either the expression or 
                                        the constraint has been successfully met before 
                                        moving to the next task

23. The arrows in the Data Flow tasks represents flow of good data (blue arrow) and flow of erroneous data (red arrow) to be sent to another destination separate from the good data.

24. Force Execution Value:
To create custom status information from running tasks and                 then use these values in precedence constraints to fine-tune the control flow
You can also return some user-defined data from a task, container, or package.
"ForceExecutionValue" set to True to turn on this feature. The default value is False.
"ForcedExecutionValueType" of the user-defined data that you intend to provide. 
                 The default value is Int32.
"ForcedExecutionValue" - Provid a value (Static or using SSIS expression).
                 The default value is 0 (zero). 

Manipulating Files with the File System Task

25. File System task - need to move, delete, or rename a file, or make just about any other changes to it.
1. Copy a directory
2. Copy a file
3. Create a directory
4. Delete a directory
5. Delete the contents of a directory
6. Delete a file
7. Move a directory
8. Move a file
9. Rename a file
10. Change the attributes of a file


26. The Rename file option actually performs two actions at once. 
1. Renames a file
2. Moves a file if the destination is different from the source

27. The property IsSourcePathVariable enables you to use a variable for the source

28. When you are using a File System Task to set attributes of a file or folder, 
you can set four attributes for a source file.
1. Hidden
2. ReadOnly
3. Archive
4. System

Coding Custom Script Tasks

29. In the Script node you have four properties to set:
1. The first is the Script Language.
Microsoft Visual Basic 2010
Microsoft Visual C# 2010
2. EntryPoint -> This is the location in the code where the task looks to execute the code 
                            first. Generally, this is left at Main because Main is automatically set 
                            up in the built-in starting script.

The next two properties enable you to list the variables from the package that you can use in the Script Task code:
3. ReadOnlyVariables are variables that you want to use in the Script Task code, 
                    but you do not want the values of the variables edited.
4. ReadWriteVariables are variables used in the Script Task that can have their values 
                    changed, meaning you can change the values of the variables to be used in the 
                    package after the Script Task completes.

30. Expressions is a collection of expression. 
The evaluation result of each expression is assigned to a property and replaces the value of the property.

Using the Execute SQL Task

31. This task is used to insert, update, select, and truncate data from SQL tables. 
Any normal SQL commands you would use can be used in this task. 
You can use parameters just like a stored procedure and can even call stored procedures 
        from the task.
The first screen on the editor lists four nodes in the left pane:
1. General - Name
, Description
, Forced Execution
, Result Set (None, Single Row, Full Result set, XML)
, SQL Statement
, ByPass Prepare(indicates whether the task should prepare the 
                             query before executing it because prepare does not support 
                             some Transact-SQL keywords if you are using a 
                             parameterized query in your execute SQL task you must 
                             insure this property is set True or the task will fail.)
, Delay Validation (- instructs to validate the task at run time).
2. Parameter Mapping - Configuring the parameters using variables
3. Result Set - Assigning the result set to the variable
The result set name is the name of the returning data. 
                This can be an alias you gave to a selected set of data. 
              If you did not give the data an alias, you would enter the number 0 to indicate the first 
                result set returned. The names of the parameters start at 0 and count up. 
                So, if you have three question marks in your SQL query, representing 
                three parameters, your parameter mappings will be 0, 1, and 2.
4. Expressions - 
5. TimeOut is the number of seconds you want the Execute SQL Task to run before the 
                task stops and reports a timeout failure. A setting of zero is infinite.
6. CodePage - Code pages are set based on the code page that is used on the SQL server. 
                 The code page option is available only for Excel, OLE DB, ADO.NET, SQL Mobile.
7. TypeConversionMode option is new in SQL Server 2012. This option allows the 
                 Execute SQL Task to convert data types when saving to a variable. 
 The data types for SSIS variables do not match exactly to the data types in SQL Server. 
 Set this mode to Allowed and the Execute SQL Task will convert some items to match 
                 the variable types when necessary.
8. IsQueryStoredProcedure -> This property is available on the ADO and ADO.NET options only. 
 This is set to True when the SQL command is calling a stored procedure from 
                 the ADO connection. 
 This stored procedure name can be stored in direct input, a file connection, or a variable.

32. The three buttons at the bottom of the Execute SQL Task on the General node are:
1. Browse -> Searches for .SQL files in the filesystem
2. Build Query -> Query builder, similar to the query builder in SQL Management Studio
3. Parse Query -> Parses the SQL query checking for syntax errors
                   These can be used to help build the SQL query for the task.

33. DelayValidation Property is available on Task level, Connection Manager, Container and on Package level. 

By default the value of this property is set to false that means that when the package start execution, it validates all the Tasks, Containers, Connection Managers and objects( Tables,Views, Stored Procedures etc.) used by them. 

If any object such as table or destination file etc. is not available then Package validation fails and Package stop execution.

By setting this property to True, We enforce our SSIS Package not to validate that Task, Connection Manager or entire Package at start but validate at run time.

34. When using the SSIS Execute Package task, one can use a Project Reference type of package for the invoked "child" package.

35. When specifying the parameter bindings, the UI lets you create a child package parameter "on the fly" (i.e. a parameter that doesnt exist in the child package).

But there are two issues with this:
1. This parameter is not available in the child after clicking OK, so while it seems you've created 
            a new parameter in the child package, this parameter is not available afterwards in the child 
            package
2. After running the parent package, the parameter binding using this "ghost" child package 
            parameter disappears

Using the Execute Process Task

36. The Execute Process Task enables you to call the windows or console applications.
   
37. Errors that occur in the outside programs can be captured in the SSIS package in a variable.
This variable value can be written to a log file or a table for auditing.
1. RequireFullFileName -> Tells the task whether it needs the full path to execute the command.
          If the file is not found at the full path or in the PATH 
                                  environment variables of the machine, the task will fail. 
Typically, a full path is used only if you want to explicitly identify the executable 
                        you want to run. 
However, if the file exists in the System32 directory, you wouldn’t normally have 
                        to type the full path to the file because this path is automatically known 
                        to a typical Windows system.
2. Executable -> Identifies the path and filename for the executable you want to run. 
         Be careful not to provide any parameters or optional switches in this property 
                         that would be passed to the executable. 
                         Use the Arguments property to set these types of options separately.
3. WorkingDirectory -> Contains the path from which the executable or command file will work.
4. StandardInputVariable -> Variable used to pass into the process as an argument. 
          Use this property if you want to dynamically provide a parameter to the 
                          executable based on a variable.
5. StandardOutputVariable -> Captures the result of the execution by setting the 
                          StandardOutputVariable property to a variable.
6. StandardErrorVariable -> Any errors that occurred from the execution are captured in the 
                          variable you provide in this property.
7. FailTaskIfReturnCodeIsNotSuccessValue property -> Another option for validating if the 
                          executable completed successfully.
8. SuccessValue option -> The Execute Process Task will fail if the exit code passed from 
                          the program is different from the value provided in the SuccessValue option. 
                          The default value of 0 indicates that the task was successful in executing the 
                           process.
9. Timeout/TerminateProcessAfterTimeOut properties -> The Timeout property determines 
                           the number of seconds that must elapse before the program is considered 
                           a runaway process. 
                           A value of 0, which is the default, means the process can run for an infinite 
                           amount of time. 
                           This property is used in conjunction with the TerminateProcessAfterTimeOut 
                           property, which if set to true terminates the process after the timeout has been 
                           exceeded.
10. WindowStyle option -> You can set the executable to be run minimized, maximized, 
                           hidden, or normal. If this is set to any option other than hidden, users will be 
                           able to see any windows that potentially pop up and may interact with them 
                           during run time. Typically, these are set to hidden once a package is fully tested 
                           and deployed to a server to be run on a schedule unattended.

Using the Expression Task *New in SSIS 2012

38. Very useful in incrementing the variables in a loop and auditing the number of files that run in a loop.

39. You can also use the Expression Task in conjunction with the precedence constraints in the Control Flow.

Using the Send Mail Task

40. The Send Mail Task sends e‑mail via Simple Mail Transfer Protocol (SMTP) from a SQL Server Integration Services (SSIS) package.

41. The Send Mail Task is most commonly used as a notification tool.

42. Can be used in Control Flow tab, Event Handler. You can place the Send Mail Task at the end of a Control Flow to send e‑mail on the successful completion of a package. 
1. The event handler of a package is also a great place to place the Send Mail Task. 
2. To record the run time: You can place the task in the OnPreExecute 
                    Event Handler to notify you via e‑mail that a package has begun. 
3. The OnPostExecute Event Handler can send mail at the end of a package showing the 
                    start and end for a package, therefore enabling you to track the run time of a 
                    package. 
4. When you place a Send Mail Task in the OnError or the OnWarning Event, you can 
                    be notified when an error or warning occurs anytime during the running of a 
                    package.

43. A SendMail task can send attachments.

44. Other properties of the Send Mail Task are the basic fields of an e‑mail:
1. From -> The e‑mail address that will show as the sender
2. To -> The receiver of the e‑mail
3. Cc -> Sends a carbon copy e‑mail
4. Bcc -> Sends a blind carbon copy e‑mail
5. Subject -> Shows in the subject line of the e‑mail

45. The next property to set is the MessageSourceType. It has three options:
1. Direct Input -> Message is typed into the Send Mail Task
2. File Connection -> Message is saved in an external file
3. Variable -> Message is stored in a package variable

Using the FTP task

46. It enables you to send and receive files from an FTP location along with other FTP commands. 

47. Note: When an FTP Task is trying to connect, the package is stopped at that point in the Control Flow. 
So if the Time-out is set to a large number and has trouble connecting, the package may run for an extended period of time without actually performing any task.

48. The next option is "Use passive mode" -> If checked, this option connects the FTP server using the passive mode instead of active mode.

49. Chunk size -> This is the size of the data that is sent out in succession until the entire file is sent or received.

50. Stop on Failure -> This property stops the FTP from performing a transfer if there is a failure during the transfer process.

Monday, March 14, 2016

SQL Server Code Names and Version Numbers

Final name Codename Version Number
SQL Server 4.21 SQLNT
SQL Server 6.0 SQL95
SQL Server 6.0 Enterprise Manager Starfighter
SQL Server Agent Starfighter II
SQL Server 6.5 Hydra
SQL Server 7.0 Sphinx
OLAP Services 7.0 (Microsoft Analysis Services) Plato
SQL Server 2000 (32-bit) Shiloh Version 8
SQL Server 2000 (64-bit) Liberty
Reporting Services in SQL Server 2000 Rosetta
SQL Server 2005 Yukon Version 9
Analysis Services in SQL Server 2005 Picasso
Database Design and Query Tools DaVinci
SQL Server 2005 Mobile Edition Laguna
SQL Server 2008 Katmai / Akadia Version 10
SQL Server 2008 R2 Kilimanjaro Version 10.5
SQL Server 2008 Report Designer 2.0 Blue
SQL Server 2012 Denali Version 11
SQL Server Data Tools (Included in the CTP of SQL Server "Denali") Juneau
Power View (A data visualisation tool included in SQL Server "Denali") Crescent
SQL Server In-Memory OLTP ( In-memory database engine built into SQL Server 2014) Hekaton Version 12