Search

SQL Server Integration Services - DelayValidation property



Package and individual tasks are validated during design phase itself,to delay the validations till execution phase ,there is an option under Properties - > DelayValidation

We can set DelayValidation property to True/False at package/individual tasks level

By default , DelayValidation property  will be set to False



Example for DelayValidation property:

In this example,

1.)we are going to create a  Table(named “Test_DelayValidation”) and insert  some records using Execute SQL Task


2.)In the Data Flow Task, data from  Table - “Test_DelayValidation”  is moved to flatfile using OLE DB Source  & FlatFile Destination


SQL Server Integration services - Rename and move files from source folder to destination folder

In this post,i am going to explain about renaming & moving processed files from active folder to archive folder using
Foreach Loop Container and
File System Task




Step 1:

Under Toolbox -> Control Flow Items ,drag&drop Foreach Loop Container  into Control Flow tab as shown in below image





Step 2:
Double-click  Foreach Loop Container ,under Collection tab do the changes as shown in below image:

SQL Server Reporting Services - Example for Subreport and Drill-down report

In this post,let us see simple example for subreport & drill-down report


What is Subreport in SSRS?

Subreport is nothing but child report created to display the data 's based on data displayed by parent report.

/*Sample Data*/

CREATE DATABASE TECH_ORG

USE TECH_ORG
GO

CREATE TABLE Employee(
EMPID INT,
EMP_Name VARCHAR(30),
Designation VARCHAR(30),
DEPTID INT,
DEPT_Name VARCHAR(30))

INSERT INTO Employee SELECT 1,'Sathya','Programmer',1,'Database'
INSERT INTO Employee SELECT 2,'Sathish','Senior Programmer',1,'Database'
INSERT INTO Employee SELECT 3,'Praveen','Programmer',2,'Java'
INSERT INTO Employee SELECT 4,'Arun','Senior Programmer',2,'Java'
INSERT INTO Employee SELECT 5,'David','Programmer',3,'Flex'
INSERT INTO Employee SELECT 6,'Mike','Senior Programmer',3,'Flex'
INSERT INTO Employee SELECT 7,'Sunny','Programmer',1,'Database'
INSERT INTO Employee SELECT 8,'Hanshika','Programmer',1,'Database'



CREATE TABLE Employee_PersonalDetails(
EMPID INT,
EMP_Name VARCHAR(30),
Designation VARCHAR(30),
Gender VARCHAR(10),
Age SMALLINT,
Location VARCHAR(30))

INSERT INTO Employee_PersonalDetails SELECT 1,'Sathya','Programmer','Male',23,'Chennai'
INSERT INTO Employee_PersonalDetails SELECT 2,'Sathish','Senior Programmer','Male',28,'Pune'
INSERT INTO Employee_PersonalDetails SELECT 3,'Praveen','Programmer','Male',26,'Delhi'
INSERT INTO Employee_PersonalDetails SELECT 4,'Arun','Senior Programmer','Male',28,'Pune'
INSERT INTO Employee_PersonalDetails SELECT 5,'David','Programmer','Male',30,'Delhi'
INSERT INTO Employee_PersonalDetails SELECT 6,'Mike','Senior Programmer','Male',29,'Banglore'
INSERT INTO Employee_PersonalDetails SELECT 7,'Sunny','Programmer','Female',22,'Punjab'
INSERT INTO Employee_PersonalDetails SELECT 8,'Hanshika','Programmer','Female',21,'Kolkata'

/*Sample Data*/






If you are new  for creating SSRS report & not familiar with creating datasources and datasets,

Step 1: Create ParentReport - ParentReport.rdl,with dataset using below query as shown in below image:
/*ParentReport - Dataset - Query */

SQL Server 2008 - Table and Database Designers


In this post,i am going to explain about,how to automatically generate scripts ,when a table is created/altered through Design option(New Table...) in SSMS ,

& how to automatically prevent table definition changes,if you try to alter the table through
 Design option in SSMS

In SSMS 2008,Goto   Tools -> Options...





  


















Tools -> Options -> Table and Database Designers -> Auto generate change scripts

Look at the below image,


If you check this option - "Auto generate change scripts" - and then if you try to create /alter

table through Design option(New Table...) in SSMS,it will automatically prompt for saving the table definition scripts.

























 Example:Auto generate change scripts

Under Object Explorer,expand database and then right-click Tables - > New Table...
as shown in below image.











 

















Create a new table and then try to close the designer query window ,a screen will pop-up as shown in below image.



































Tools -> Options -> Table and Database Designers -> Prevent saving changes that require table re-creation


If you check this option - "Prevent saving changes that require table re-creation" - and then if you try to alter the existing

table definition through Design option in SSMS,it will automatically prevent from altering the table definition as shown in below image.




SQL Server - DBCC PAGE and DBCC IND



DBCC IND – used to know the page details of tables & indexes.

syntax:
DBCC IND ( {dbname}, {table_name},{index_id} )
Description of columns displayed from DBCC IND

PageFID
File ID of Page

PagePID
Page ID

IAMFID,IAMPID
File ID&Page ID -> IAM mapping


ObjectID
 object ID(Table)

IndexID
Clustered index (IndexID = 1),Non clustered index (IndexID = 2)

PartitionNumber
 Number of Partition which holds data & index pages

PartitionID
ID of Partition which holds data & index pages

iam_chain_type
In-row data,Row-Overflow data

PageType
1=Data page,2=Index page,3&4=Text page,10=IAM

IndexLevel
0=leaf level

NextPageFID,NextPagePID,PrevPageFID,PrevPagePID
Next & Previous Page ID 's & File ID 's of a page




 

DBCC PAGE – used to see the contents of  a Page.

syntax:
DBCC  PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )



DBCC PAGE & DBCC IND both are undocumented commands.

Few examples for DBCC PAGE & DBCC IND:

XQuery - Singleton Error

value( )  method always returns a scalar value of specified SQL Server data type.

Example: for value ( ) method & how to avoid singleton error.

CREATE DATABASE TEST_XML

USE TEST_XML
GO

CREATE TABLE College_Master
(College_ID INT NOT NULL,
College_Name VARCHAR(20),
College_Details XML)



INSERT INTO College_Master VALUES (1,'BCC UNIVERSITY','<STUDENTINFO>
  <student ID="1" name="Sathya">
    <subject ID="1" Name="Electronics and Communication" />
    <subject ID="2" Name="Circuit Analysis" />
    <subject ID="3" Name="Mobile Communication" />
  </student>
  <student ID="2" name="Deepak">
    <subject ID="4" Name="Data Structure" />
    <subject ID="5" Name="Java" />
    <subject ID="6" Name="Database Management System" />
  </student>
  <student ID="3" name="sathish">
    <subject ID="7" Name="Soil Mechanics" />
    <subject ID="8" Name="Steel Design" />
    <subject ID="9" Name="Concrete Design" />
  </student>
</STUDENTINFO>')



If you execute below query,

 SELECT College_Details.value('(/STUDENTINFO/student/@name)','varchar(20)') FROM College_Master
WHERE College_ID = 1;


you will get below error,because value ( ) method always returns a scalar value

Msg 2389, Level 16, State 1, Line 1
XQuery [College_Master.College_Details.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'



In the above example ,if you notice XML instance ,it has 3 student element nodes,

So to return first student name,execute below query


SELECT College_Details.value('(/STUDENTINFO/student/@name)[1]','varchar(20)') FROM College_Master
WHERE College_ID = 1;


to return second&third student names,execute below queries,

SELECT College_Details.value('(/STUDENTINFO/student/@name)[2]','varchar(20)') FROM College_Master
WHERE College_ID = 1;

SELECT College_Details.value('(/STUDENTINFO/student/@name)[3]','varchar(20)') FROM College_Master
WHERE College_ID = 1;


 --DROP DATABASE TEST_XML

SQL Server 2008 - Data Collection



What is Data Collection in SSMS 2008?


Data Collection option in SSMS 2008 is used to gather data about Disk Usage,Query Statistics,Server Activity and generate report about 

1.)Disk Usage Summary:
disk space used by databases and
data file & log file growth rate of a database in a SQL Server 2008 instance.

2.)Query Statistics History:
execution count of a query,
total duration for a query execution,
I/O cost & CPU utilization of a query in a SQL Server 2008 instance.

3.)Server Activity History:
resource consumption details like CPU,memory,disk i/o & network usuage,
SQL Server waits,
SQL Server activities like (user connections,logins,logouts,transactions,batch requests and SQL compilations & recompilations) for a SQL Server 2008 instance and also for host OS where data collection option is configured.

Where  Data Collection in SSMS 2008 is located?


Object Explorer - > Management - > Data Collection





 Process involved  in Data Collection:



SQL Server Integration Services - Logging in Packages



In this post, I am going to explain about logging in SSIS.

We can maintain log about  package & system informations ,
various events for the container can also be logged as shown in below image:























We can enable logging in two ways as shown in below two images.


































SQL Server Integration Services - Error handling for truncation error



In this post ,I am going to explain about handling truncation error,while importing data from CSV file to table in database.


Step 1:


From the below link,you can download sample test data(CSV file)




Step 2: create new SSIS project




Step 3: drag & drop  Data Flow Task on to the  Control Flow tab as shown in below image.




All about XML in SQL Server

SQL Server handles two types of data

1.)Relational data
2.)XML data

I have covered almost all topics about handling XML in SQL Server and posted in my site.

SECTION - 1:

      How to convert relational data to XML data ?

      answer :       use FOR XML clause

For more information about FOR XML clause, refer below link

http://www.allaboutmssql.com/2013/11/handling-xml-data-in-sql-server.html


SECTION - 2



How to traverse through XML document/fragment to fetch/modify attribute values or element nodes?

answer use XQUERY & XPATH

 How to shred XML data into columns of relational tables? 

 answer : use nodes() &  OPENXML

 How to validate structure & values of XML,each time when it is inserted/updated?

 answer : use XML SCHEMA COLLECTION

 How to improve the performance of querying XML data ?

  answer : use XML INDEX - primary & secondary index.



For more information about XQUERY,XPATH,XMLINDEX,XMLSCHEMA clause, refer below link

http://www.allaboutmssql.com/2012/09/xqueryxpathxmlschemaxml-index_6.html