SSIS Real Time Question

How do I access Microsoft Excel 2007 from SSIS 2005 version. I am unable to access the files from SSIS

ANS:
Use ACE connector for Excel.
ACE OLEDB 12.0:

TYPE OLE DB ProviderUSAGE Provider=Microsoft.ACE.OLEDB.12.0MANUFACTURER Microsoft 

Xlsx files

This one is for connecting to Excel 2007 files with the Xlsx file extension. That is the Office Open XML format with macros disabled.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

SSRS Real time scenarios

SSRS (SQL Server Reporting Services)Real Time Scenarios:

Scenario 1:

 Show the report like Drill Down. Here I have 3 groups as shown in below.

Main Category Name 

     +  Sub Category Name1

               + Sub Details1

                             Details1Name     rate      qty    amount

                +Sub Details2

                               Details1Name     rate      qty    amount

                +SubDetails3

                               Details1Name     rate      qty    amount

       +SubCategory Name2

       +Sub Catagory Name3   

             .....


Scenario 2:

I have an SSRS Report that needs to be filtered via multi-value parameter selection. normally we can done for single column. But here we want to allow multi-select based on multiple columns. The column layout is as follows:

AFLAG    BFLAG    CFLAG     DFLAG
X                X               X           X
NULL          X             NULL      NULL
X               NULL           X         NULL
X                X                X         X
X              NULL            X         NULL
NULL         X                X          NULL
X               X               NULL     NULL
X               X               NULL    NULL

In my report parameter, when multiple AFLAG, BFLAG, CFLAG or DFLAG values are selected, I want to be able to display every Reports that contains an 'X' in each column once, where there are multiple values marked with 'X' in a single report.


Scenario 3:

Testing Procedure In SSIS

Test cases For SSIS Project :

Testing For a SSIS project Is very important question in interviews.


 @ Verify all the tables have been imported

@ Verify all the rows in each table have been imported

@ Verify all columns specified in source query for each table have been imported

@ Verify all the data have been recived without any truncation for each column

@ Verify the schema at source and destination

@ Verify the time taken /speed for data transfer

@ Fields truncated due to diffence in length of the field.

What is Report Server Data Base in Reporting Services?

What is Report Server Data Base in Reporting Services?

Report Server Data Base:
The report server database is the main store of the data in reporting services. It contains report defenations, report models, data source, schedules, security information and snapshots.Because of this this is critical that the database be backup regularly .
Now we are going to discuss about some tables in sql server reporting servicies with functinal areas.

1.Resources:

Catalog Table: It contains the report defenations and folder locations and data source information.
  
Data Source Table: It contains Every individual data source information.

2.Security:

Users Table: It contains the user name and security ID(SID) information for autherized users.

Policies Table: It contains a listing references to different security policies.

Policy Users Role Table: It contains an association of users / groups, roles, and policies.

Roles Table: It contains a list of defined roles and the tasks the roles can perform.

3.Snapshots:

Snapshot data Table:  It contains information used to run an individual snapshot, including query parameters and snapshot dependencies. 

ChunkData Table: it stores the report snap shots.

History Table: It stores a refernce between stored snapshots and the date they were captured.

4.Scheduling:

schedule Table: it contains information for different report execution and subscription delivery scedules.

Report Schedule Table: It contains an association between a given report ,its execution schedule and the action to take.

Subscriptions Table: it contains a listing of individual subscriptions ,including the owner,parameter and delivery extensions.

Notifications Table: It contains the subcription notification information such as data processed ,last run time, and delivery extensions.

Event Table:  It contains Temporary storage location and event notifications.

Active Subscriptions Table: It contains suscription sucess/failure information

Running Jobs Table: It contains the currently executing scheduled process.

5. Administration:

Configuration info Table:  It contains Reporting services configure information ,which should be administrated through prescribed interfaces and not by directly editing this table data.

Keys Table: It contains the public and private keys for data encryption.

ExecutionLogStorage Table: It contains a listing of reports that have been executed and critical meta data about the event.


6.Report Models:

ModelDrill Table: It contains information used when implementing report builder infinate drill down features.

ModelItemPolicy Table: It contains an association between a given report item,model  and policy.

ModelPrespective Table: It contains an association between a given report model and its perspective.

Explain the Reporting service life cycle?

Explain the Reporting service life cycle?

Ans: The reporting service life cycle as a a three phase process 

1.reports are autherd by end users or report specialists

2.those are managed as part of centralized reporting system

3.finally those  finished reports are delivered to end users through various means

What is report server temp db?

What is report server temp db?

The ReportServerTemp DB stores temporary reporting service information. This includes both session and cache data.

Reporting services will not function properly without the reportServerTempDB  database. If the data base is lost you can simply rebuild it. 

 i will give some tables and related functions in Reporting Server Temp DB

 

Chunk Data Table: It will stores report definition and data for session cached reports and cached instances.

Execution Cache Table: It will stores execution information including time out for cached instances.

PersistedStream Table: It will stores the session level rendered output for an individual user.

Session Data Table: It persists individual user session level information, including report paths and time outs for given session information.

Session Lock Table: It will storesTemporary storage to handle locking of session data.

SnapshotData Table: It will stores temporary snapshot.

ETL Process In Data Warehousing

ETL (Extraction Transformation Loading) Process  In Data warehousing:

When we do software development we have different stages like requirement gathering, designing, coding, testing. Similarly we have some steps in data warehousing also.

There are three stages in data warehousing:
1. Extraction
2. Transformation
3. Loading

In details of these three stages
1. Extraction: In this process we can extract the data from the source. In real time scenario the source files may be Excel,Access,Delimited text,CSV files,etc., so extraction involves understanding of data source and loading that into a structure of data warehouse.

2.Transformation: In this stage the data can be cleaned .in this source files the data is not in good format so in this stage the data will be formatted as per the client requirement.Its not necessary that after extraction process data is clean and valid.For instance all the financial figures have null values but you want to be ZERO for better analysis.so you can have some kind of stored procedures which returns through all the records and sets the value to zero.

3.Loading: After Transformation you are ready to load the information in to your final data warehousing data base.

SSIS Packages Deployment Procedure

Package Deployment In SSIS Using File System:

first we have to create a simple project for demonstration purpose .open the Business intelligence development studio and open the samples solution file that we have already created for showing examples.

 

After that open the Tutorial-sample-1 project under the samples solution root.

Right click on the tutorial-sample-1 project and select properties option.that will open a dialog box in that select deployment utility tab

.

Note that create deployment utility set to TRUE .default it is FALSE .the deployment output path specifies the location where will be the deployment file will be written.the default path is bin folder under the project folder .then click OK

Now Come to the solution Explorer window and right click on Tutorial-sample-1 and select the build option.then this will build the project and invoke the deployment utility.you will get the success messages in result page under the works pace area.

After that you can observer 2 files in Tutorial-sample-1 project bin folder.

those 2 files are 

These 2 files represents the deployment. you can deploy the project in production environment by double clicking on manifest file to perform the deployment.