SSIS - Invoking / Running / Controlling one SSIS package from another

Refer Source: Invoking / Running / Controlling one SSIS package from another

Individual packages: Flat File Source - > Ole DB Destination

To get the Parent Package Variable value: 

Configurations:

i.Create a variable var_dir

ii.Right Click: Choose "Package Configuration" : choose : Parent Package Variable: map to  var_dir

iii.Expression: concatenate var_dir + file name

Note:

i.In Parent Package: a variable is defined like: var_file_dir  c:\data\

where all the text files are there that need to be loaded

The Parent Package: Which is calling these 3 Packages.

i.3 variables are declared for 3 packages which is boolean and value "True"

This is used to: make the individual package "Disabled" in the beginning.

Configuration to disable: Expression below "Disable"

ii.Package first runs a script task and read the variable values containing just directory information.

iii.File Exist task checks if  file are there or not , if there then set the  boolean value "True" , False

, that way enables the packages to run.

Interactive SSIS package

Refer Source:Interactive SSIS package

Script Task:

i. Take user input of  Table Name. Assign it a variable defined.

Execute SQL Task:

i.

DECLARE @TableName VARCHAR(100)

SET @TableName = ?

Select ? = Count (*) FROM INFORMATION_SCHEMA_TABLES  WHERE TABLE_NAME = @TableName

GO

Execute SQL Task:

Parameter Mapping:

Variable Name         Direction   Data Type          Parameter  

User:TableName      Input         VARCHAR        0

User:ValidateName Output       LARGE_INT...   1

Constraint:

Validation done on the variable.

DECLARE @TableName VARCHAR(100)

SET @TableName = ?

DECLARE @Count  INT

DECLARE @SqlString NVARCHAR(1000)

   SET  @SqlString =  '  SELECT   @OutCount = COUNT(*) FROM  ' =@TableName

EXEC  SP_EXECUTESQL 

                             @SqlString

                             , N'@OutCount INT OUTPUT'

                             ,@OutCount = @ Count OUTPUT 

                             Select ? = @Count

SSIS Performance Tuning Tips and Tricks

SQL Server Integration Services SSIS Best Practices

SSIS Best Practices I SSIS Tutorial I MSBI

Informatica vs. SSIS

SSIS - Incremental Data Load

Refer:

Using Temp Tables Across SQL Tasks - SSIS

Refer Source:Incremental Data Load - The SSIS Approach

Destination Connection Manager:

RetainSameConnection  :True

Approach1:

1.Insert,Update

Sequence Container:Data Flow Task

Ole DB Source:          Ole DB Target :

Sort  ( in both)

Merge Join (Left Outer Join)

Conditional Split(Where Clause)

      Insert  Records (  Id  not in Source)

      Update Record( Change record :   where source.name != Target.name)

example:

Select    from source  a left outer join target b on a.id=b.id

update  target b

set  col

from ...

where ...

note: the  update is done 1 rec at a time.delete 1 rec at a time.it is not set based method like

updating all at a time or many records at a time.

2.Delete

TargetDB

LookUP  ( 

OLE DB Command  (  update ... col1=?  )( ?  replaced by parameters set on it).

note:Deleting  the Id  not in staging but in source, in dw its not done,the data is not deleted.

Approach2:SET based approach

Sequence Container

Data Flow Task

Create TEMP Table  (## -  for  Global Temporary Table)

OLEDB Update

---

Data Flow Task:

OLE DB Destination:

    ValidateExternalMetaData:False

note:instead of   "OLE DB Command"  use "OLE DB Destination"

Approach3:Merge &  OLE DB Source & OLE DB Destination.

Sequence Container

Data Flow Task

Create TEMP Table

note:loading the source table  to tempdb of  target server.

MERGE  EMP_TAREGT      AS  T

USING    ##EMP_SOURCE  AS   S

ON (T.ID=S.ID)

WHEN NOT MATCHED BY TARGET

THEN INSERT (ID,NAME,DEPT) VALUES (S.ID,S.NAME,S.DEPT)

WHEN MATCHED AND T.NAME < > S.NAME

OR ISNULL(T.DEPT,'AA') < > ISNULL(S.DEPT,'AA')

THEN UPDATE SET T.NAME=S.NAME

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

note: OLEDB  Source  ,  ValidateExternalMetaData:False ( runtime the table is going to be created.)

SSIS - Encrypt

Refer Source:

Encrypt Your SSIS Package - Learn About Protection Levels

SSIS - Debugging

Refer Source:

SQL Server Integration Services (SSIS) Part 8 - Debugging

Run-time Errors:

Data Flow: Data Viewers

Control Flow: Break Points , Watching Variable

Package: Loading from excel file.

Control Flow:

       Data Flow:

                            Excel Source

                            Row Count Package: Loading from excel file.

Control Flow:

       Data Flow:

                            Excel Source

                            Row Count  (  It keeps  the Rowcount in a User variable say User:NumberRows , this is passed to script task)

    Script Task:

Note:

Script

ScriptLanguage: Microsoft Visual C# 2010

...

ReadOnlyVariables:User::NumberRows

----------------------

Runtime errors:

 View Menu - Output  ( show output window the error message )

----------------------

Error Message  shown in the output window:

"Excel Connection Manager":

The requested OLE DB  provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64 - bit 

driver is not installed , run the package in 32-bit mode.

...
The OLE DB  provider "Microsoft.Jet.OLEDB.4.0" has not been registed.



Solution:

Project Properties: 

               Debugging page:

                           Run64BitRuntime Property: false


Notes:

1. There is no 64 bit Jet driver, so that means no Excel or Access support on 64bit OS.
... To Resolve this issue run SSIS package using 32bit version of  DTSExec.

---

Data Flow:-> Right Click on any pipe of data ->Enable Data Viewer

In this one can choose which column values to see in "metadata", also see all the columns and their data type.

DISABLE when not needed, do not delete it as it.

---

Control Flow:->--

Control Flow:

Right click on "Control Flow Task" -> Edit Breakpoints

It would show all the events "OnPreExecute" or "OnPostExecute" event etc. ,  "Hit Count Type"  say after 10th file got loaded you want it to run in debug mode.

---

--

From Menu Debug -> Windows -> ( Breakpoints , Output , Immediate )

--

--

When the package runs it will have the Break Point on the first event say "OnPreExecute"  , now if one click on 

"Output" window one will see the messages. At this point if  again one choose Menu->Debug ( many options - used for very large system , typically)

will show.Not to choose any just click run the package runs.

--But at this point it's no use

--

Watch Variables:

Menu ->Debug->Local Window ( to view all variables.)

Note: This shows all variables the system variables & User variables too. Difficult to find the User variable.

--

Menu->Debug->Attach to Process

to do  tha above first choose Menu->Debug->Watch Window->Watch Window1

Click on the "Variable" ,  drag the variable to watch window.

--

Menu -> Quick Watch->Shift +F9

choose which variable to watch and even can add to the watch window.

--

SSIS - Passing values from Parent to Child package

Passing values from Parent to Child package in SSIS

~Variables

Variables: declared and "Expression" are used to take on runtime all values.

Variables: GetDB  of  System.Object Type

~Execute Sql Task

Execute Sql Task: Sql to get from sys databases online databases.

Resultset:

    Result Name Variable Name

0 User:GetDB

Foreach Loop:

Enumerator:Foreach ADO Enumerator

ADO object source variable:User::GetDB

Variable Mapping:

Variable Index

User:PassDB 0

Execute SQL query:

Declare @TableName SYSNAME

Declare @Database    SYSNAME

Declare @SqlString     NVARCHAR(2000)

SET  @TableName=?

SET  @Database=?

0

SET  @SqlString  = 'SELECT @OutRowCount=COUNT(*)  FROM '+@Database=' sys.tables where name=" +@TableName='"

EXEC sp_executed @SqlString,N'@OutRowCount INT OUTPUT

,@OutRowCount=? OUTPUT

Parameter Mapping:

Variable Name Direction Data Type Parameter  Parameter

User::TableName   Input VARCHAR 0 -1

User:ParentDB        Input VARCHAR 1 -1

UserFlag Output LONG 2 -1

Execute Package Task Editor:

Package

Location:File System

Connection:Child.dtsx

Password:

ExecuteOutOfProcess:false

----Child.dtsx

When you want to pass a variable vaue from parent package.

1.In Child package create a variable say  C_DBName

2.Right click-choose "Package Configuration" , in the drop down list box choose "Parent Package"

  write exact name as it is case sensitive:ServerName

  say map: Table Name: Variable: C_DBNAME

so now the child package has the "Server Name" from parent package.

SSIS- Pivot Transformation in SSIS (Part 1 of 4)

Pivot Transformation in SSIS (Part 1 of 4)

SSIS - What s New in SSIS 2012 and 2014

What s New in SSIS 2012 and 2014

SSIS - FTP Task File System

SQL SERVER SSIS Lesson15 FTP Task File System

SSIS - DTUtil command prompt utility

DTUtil command prompt utility - SSIS

SSIS - DTExec command line

DTExec command line - SSIS

SSIS - Ping Directory To check for file's arrival

Ping Directory To check for file's arrival - SSIS

SSIS- Zip Files

Zip Files using SSIS

SSIS - Copy Column Transformation

Copy Column Transformation In SSIS

SSIS - Tuning packages so they run faster.

Tuning SQL Server Integration Services (SSIS) packages so they run faster.

SSIS - Using Containers

SSIS Video Using Containers in SSIS

SSIS - Expression and Other Constraints

SQL Server Integration Services (SSIS) Part 16 - Expression and Other Constraints

SSIS - RowCount Transformation

RowCount Transformation In SSIS

Similar to count(*) or count(1)

SSIS -Store Images,Documents,PDF (BLOB) Files Into SQL Server

Store Images,Documents,PDF (BLOB) Files Into SQL Server Using SSIS

SSIS -Audit Transform

Audit Transform In SSIS

SSIS Pivot and UnPivotTransfromations

SSIS - Advanced Precedence Constraints

Advanced Precedence Constraints SSIS

SSIS - Using Temp Tables

Using Temp Tables Across SQL Tasks - SSIS

ssis we have connection pulling. So each task has a connection and the temporary table is available till the connection is their.

In "Connection Manager" , set  the "Retain Connection" property to "True" then throughout that connection is used the temporary table will be available.

SSIS - Generate XML Formatted File

Refer Source Video :Generate XML Formatted File From SQL Server - SSIS 

Option1:->

Execute SQL  Task:->

sql statement:

select... table joins

FOR XML AUTO, ROOT('Organization')

Resultset:xml

ResultSet:

Result Name        Variable Name

0                            User::var_xml_out

Script Task:->

Script:

ReadOnlyVariable: User::var_xml_out

note:

1.FOR XML  is the T-Sql which generates in xml format.

2.var_xml_out  - string type variable.

Option2:-> Data Flow Task

Data Access Mode:Sql Command

OLE DB Source:

Select(

select... table joins

FOR XML AUTO, ROOT('Organization'))) AS data

note:the above statement will return data as string unlike above the xml in option 1.

Data Conversion Transformation:-> Converting from unicode to nonunicode.

Create a flat file Connection:->

Advanced:

Column:XMLData

Datatype:string(DT_STR)

OutputColumnWidth:500

Option3:->

Select(

select... table joins

FOR XML AUTO, ROOT('Organization'))) AS data

 , C:\data\out\text.xml AS Path

Export Column: transformation  takes the data copies to the mentioned Path.

SSIS - Checkpoints

Introduction To Checkpoints in SSIS 

SSIS - Load XML Files

Refer Source: Load XML Files into SQL Server Tables - SSIS   

XML Source:->

XSD Location:c:\output.\output.xsd

press button->Generate XSD

i.Use Data Conversion - for converting from unicode to nonunicode

ii.Consume the Dept & then the Employee separately. - specifying two different OLEDB destinations.

note:

i.download xsd.exe from internet & run any valid .xml file.

click on xsd.exe

xsd.exe  test.xml

Open in Visual Studio 2008:Show the join diagram

modify: the joins, Set Primary Key

SSIS Incremental Load - Data Hashing

SSIS Incremental Load - Data Hashing Approach   

SSIS - Parallel Data

Parallel Data Load - Optimize your DFT SSIS 

SSIS - ForEach ADO Enumerator -Flat Files Dynamically

ForEach ADO Enumerator - SSIS Load Flat Files Dynamically   

SSIS - Load Data from Multiple Source (Text & Excel)

Load Data from Multiple Source (Text & Excel) Into Single Table Using SSIS   

Isolation Levels In SQL Server

Comparing Isolation Levels In SQL Server   

Configure Database Mail, Operator and Notifications in SQL Server

Configure Database Mail, Operator and Notifications in SQL Server

SSIS - Variables And Expression

Variables And Expression in SSIS



Dynamic SSIS Configurations Expressions and Variables

SSIS - Multicast transformation

Demonstration of Multicast transformation in SSIS 2008.

Duplicates the set of data.

SSIS - Merge Join transformation

Merge Join transformation in SSIS (Part 1 of 3).


Merge Join transformation in SSIS (Part 2 of 3).

Merge Join transformation in SSIS (Part 3 of 3).

SSIS - Aggregate & Data Conversion Transformation

Aggregate& Data Conversion Transformations usings SSIS (Part 1 of 2)


Aggregate& Data Conversion Transformations usings SSIS (Part 2 of 2)

SSIS - Using a Union All & Merge transformations

Using a Union All & Merge transformations to append datasets in SSIS.

SQL Server 2008 - Import-Export Wizard

SQL Server 2008 - Import-Export Wizard

MSSQL - How to configure SQL Proxy and Credentials

MSSQL - How to configure SQL Proxy andCredentials

SSMS

Security - Crendential

Credential name:Credential_DB_ADMIN

Can SQL Server views be updated

Viewsin sql server Part 39

CanSQL Server views be updated (SQL Server interview questions andAnswers) ?

1 base table data it can update.With joins multiple table data if one enter 1 table data it will update.

Indexedviews in sql server Part 41

Note: I.e Materialized view in Oracle

Conditions to create an Index view:

Create View vsTotal

With SchemaBinding

as

Select …...From Table Name.

I.The “SchemaBinding” Option to be their, as Base Tables should not change.

Conditions:

1.If the is Null then ISNULL( columname),0 – set a default value , it should not be null

2.If Group By Clause is used, then a COUNT_BIG(*) need to be used not Count(*)

3.base tables in view, should dbo.tablename ( should be 2 things like dbo & tablename , not 3

,the server name) (no 3 partname)

4.First Index should be “Clustered Index” as no data is their initially so nonclustered index can not

be used.

Create Unique Clustered Index UIX_TotalSalesByProduct_Name On vwTotal(Name)

sqlserver 2008 linked server

collation conflict

TheWHERE Clause: Case Sensitivity and Collations

Get all Collation list from below functions.

Select * from fn_helpcollations( )

ResolveCannot Resolve Collation Conflict Error - SQL in Sixty Seconds #047

Solution:

select * from CollateExample ce where ce.Collate1 COLLATE DATABASE_DEFAULT = ce.Collate2 COLLATE DATABASE_DEFAULT

Effectof Collation on Resultset - SQL in Sixty Seconds #026

Data Dictionary

Source:Data Dictionary - SQL Server 2012

 Table – right click – Properties – Extended Properties

Thats tells Table description I.e Data Dictionary

DatabaseDesign 4 - Creating a Data Dictionary

New Database Diagram – good( this does not do physical
change)

SQL Server 2012 create relationships and ER diagram

Howto Create a Database Diagram Using SQL Server Management Studio

 

 

 

SQL QUERY replace NULL value in a row with a value from the previous known value

SQL QUERY replace NULL value in a row with a value from the previous known value

Excel to SQL Server -Bulk Insert

SQL Training Online - Excel to SQL Server - Part #1 - Introduction 

SQL Training Online - Excel to SQL Server - Part #2 - Import Export Wizard 

Source: SQL Training Online - Excel to SQL Server - Part #3 - Bulk Insert 

 

text:file

BULK INSERT ExcelImportTraining.dbo.product

FROM 'C:\Demo\product.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

firstrow = 2

)

note:

'\n' - refers to the carriage return

Excel file: Products.xlsx ( excel 2007)

save as :Product.csv

Open in google Note++ Product.csv

there are rows "Cost"  within " "

Open the Excel file, choose the "Cost" whole column , right click choose as "text".

Then run the "BULK INSERT"  command in query analyzer.

 

Importing CSV into SQL Server - SQL in Sixty Seconds #

 

 

Import Excel unicode data with SQL Server Integration Services


Cannot convert between a unicode and a non-unicode string data types

Import Excel unicode data with SQL Server Integration Services

Returning a result set with multiple rows based on max date

Returning a result set with multiple rows based on max date

Transform rows into columns in sql server

 Transform rows into columns in sql server

Loop through Flat Files in SQL Server IntegrationServices

Howto loop through Excel files and load them into a database using SSISpackage?

Scheduling SSIS packages to run using SQL Server Agent Service

Scheduling SSIS packages to run using SQL Server Agent Service

SQL INSERT INTO SELECT Statement -Update

SQL INSERT INTO SELECT Statement



INSERT INTO table2   SELECT * FROM table1; 


Check if table exists in SQL Server


IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_TYPE='BASE TABLE'
           AND TABLE_NAME='mytablename')
   SELECT 1 AS res ELSE SELECT 0 AS res;


TRUNCATE TABLE (Transact-SQL)


IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_TYPE='BASE TABLE'
           AND TABLE_NAME='FamilyTree')
  Truncate Table FamilyTree

select * into #TEMPALL
from
(
select * from #TEMPdata1
UNION
select * from #TEMPdata2
)#TEMdata12

Part2 - Create Tables, Add Data

------------------------------------------------------

The SQL UPDATE Statement

UPDATE table_name

SET column1=value1,column2=value2,...

WHERE some_column=some_value;

------------------------------------------------------

SQL Server 2008 Update Query with Join and Where clause in joined table

UPDATE 

    ust

SET  

    ust.isUnsubscribedFromSystemEmails = 1

FROM         

    UserSetting AS ust 

INNER JOIN

    [User] ON ust.userID = [User].userID 

AND 

    [User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses)

TRY..CATCH block - RAISERROR - THROW statement

Source: when-does-raiserror-fire-in-a-stored-procedure

execution to stop, you need to raise the error within the TRY block

 and then raise the error again in your CATCH block this will make sure that the error is "raised" to the caller.

 Or you could add a RETURN statement after your RAISERROR statement in the CATCH block. This will exit the procedure and return to the caller.

Also, as suggested by MSDN you should try to use the THROW statement instead of RAISERROR since it (the RAISERROR) will be phased out.

RETURN following the RAISERROR() in the CATCH block and the stored proc works fine now.

Source:When does RAISERROR fire in a stored procedure?

 

That's not how it works in T-SQL. Nothing in the documentation for TRY...CATCH or RAISERROR specifies any special cases that would override:

When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

If you want the stored proc to exit, you need a RETURN statement as well.

....

It depends on the severity level that you use. There's a lot more information in the below link:

http://technet.microsoft.com/en-us/library/ms178592.aspx

But to quote the article:

The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run...

So if your severity level is 11 or higher then the control will be immediately transferred to the CATCH block.

 

What is the difference between SQL, PL-SQL and T-SQL?

Source: Whatare the differences between T-SQL, SQL Server and SQL [duplicate]

SQL is the basic ANSI standard for accessing data in a relational database. When you see "MSSQL" it is referring to Microsoft SQL Server, which is the entire database architecture and not a language. T-SQL is the proprietary form of SQL used by Microsoft SQL Server. It includes special functions like cast, convert, date(), etc. that are not part of the ANSI standard.

You will also see things like plSQL, which is Oracle's version of SQL, and there are others as well (mySQL has its own version, for example, and Microsoft Access uses Jet SQL.)

It is important to note the the ANSI standard for SQL has different releases (for example, 92 or 99, representing the year it was released.). Different database engines will advertise themselves as "mostly ANSI-92" compliant or "fully ANSI-99" compliant, etc, and any exceptions will usually be documented.

So although "SQL is SQL", every engine uses its own "flavor" of it, and you do have to do a little reading on the particular platform before you just dive in.

A further note - the SQL extensions, like T-SQL, are generally considered full-fledged programming languages, complete with looping, if/then, case statements, etc. SQL itself is limited to simply querying and updating data and is not considered a true programming language.

Wikipedia has a decent article for an overview here: http://en.wikipedia.org/wiki/SQL

SQL - String Functions

SQL Server String Functions

SQL Server query execution plans – Viewing the plans

Source:SQL Server query execution plans – Viewing theplans

 ..

If you look at the Query item in the SQL Server Management Studio menu, you’ll see two options related to query plans – Display Estimated Execution plan and Include Actual Execution plan
An estimated execution plan is a SQL Server query plan that is generated without actually running the query (or stored procedure) the plan is created for.
...

 

 

Create aTransact-SQL Job Step

SQL SERVER – Understanding Table Hints

Source:SQL SERVER – Understanding Table Hintswith Examples

 ...

The hints override any execution plan the query optimizer might select for a query.

....

 

 

SQL SERVER – Difference between Temp table,Table variable and CTE

Source: SQL SERVER – Difference between Temptable,Table variable and CTE

CTE

CTE does not store data in tempdb rather it uses memory to store data.

QueryAnalyzer VS. Query Profiler

Source: QueryAnalyzer VS. Query Profiler Reads, Writes, and Duration Discrepencies

These two utilities do different things:

• SQL Profiler watches what's happening and reports on it.
• Query Analyzer looks at the SQL code and figures out how to run it.

Transaction:IMPLICIT - Explicit Transactions

Source:SET IMPLICIT_TRANSACTIONS (Transact-SQL)

SET IMPLICIT_TRANSACTIONS { ON | OFF }

When OFF, it returns the connection to autocommit transaction mode

...

When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is ON.

...

Source:Explicit Transactions

An explicit transaction is one in which you explicitly define both the start and end of the transaction.
DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.

BEGIN TRANSACTION

Marks the starting point of an explicit transaction for a connection.

COMMIT TRANSACTION or COMMIT WORK 

 Source:What does a transaction around a singlestatement do?

...

In MS-SQL, is there any benefit from wrapping single selects, single updates, single inserts or single deletes in a transaction?
...

 

It does nothing. All individual SQL Statements, (with rare exceptions like Bulk Inserts with No Log, or Truncate Table) are automaticaly "In a Transaction" whether you explicitly say so or not.. (even if they insert, update, or delete millions of rows). ... ... "ACID" requirements of a relational database? That "A" stands for Atomic, meaning that either the statement works in its entirety, or it doesn't--and while the statement is being performed, no other queries can be done on the data affected by that query. BEGIN TRANSACTION / COMMIT "extends" this locking functionality to the work done by multiple statements, but it adds nothing to single statements. ... Note also that "nolock" does not apply to inserts/updates/deletes -- those actions always required locks.

 

 

How to capture the performance monitor counters and objects using PERFMON

Source:How to capture the performance monitor countersand objects using PERFMON?

 

running PERFMON in the “Run…” textbox. 

Add the desired counters. Please see my previous blog posts on identifying memory, processor, disk, and overall SQL Server performance pressures and bottlenecks for suggestions. 

1. Specify the location to save the file.