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. 

Complete List Of Operations On FTP Task In SSIS

Introduction

This article provides complete information about  FTP Task operations and properties available in SSIS package.

Steps:

In this article we are going to see the different FTP task operations available. Before starting with the operations we will see on why are using the FTP Task operation. FTP task downloads and uploads some data file and manages servers on the remote server or even in the local server.

FTP Operations here means the list of operations the task supports in order to achieve some tasks like sending the file, receiving the file, looping through folder and selecting some files, looping through the folder and deleting some files, managing a directory etc.

Below table shows the list of operations available. 

Task Operation	Task Operation Description
Send Files	This operation is used to send some files to remote location
Receive files	This operation is used to download some files from remote location
Create local directory	This operation create a new folder on a local server
Create remote directory	This operation create a new folder on the remote server
Remove local directory	This operation delete a folder in the local server
Remove remote directory	This operation delete a folder on the remote server
Delete local files	This operation delete a file in the local server
Delete remote files	This operation delete a file on the remote server

How To Delete A Remote FTP Folder In SSIS

Introduction: This article provides complete information about how to delete a remote folder in ssis

simple 3 steps.
First create a simple project in BIDS. after creating a a project in SSIS we will see on how to use FTP task container. Drag and drop the FTP task as shown in the below screen.

Now double click on the task to open the property window and set the properties as shown in the below screen.

Sql Server Real Time Errors

With MS SQL Server, many communication errors arise that could be server-side errors, client-side errors.On getting these communication errors one should not fear at all as these errors do not indicate a problem with SQL Server, but rather a network, network configuration, or client application problem.

Find most common SQL Server errors and their respective reasons.

Server-side errors

17832 Unable to read login packet(s) - This happens when a client starts to connect, but never successfully completes the attempt because of a client operating system or application failure.

17825 Unable to close server-side connection - Happens when SQL Server gets an error while attempting to close the network connection to the client.

17824 Unable to write to server-side connection - Happens when SQL Server attempts to write to a client connection and the write fails. This could be caused by the client computer being hung or shut down, or due to network problems.

10058 Can't send after socket shutdown - Happens if a sockets client (which includes SPX) is actively sending or receiving data to or from SQL Server, and the client application hangs or traps, or is abruptly shut down.

10054 Connection reset by peer - This can happen if a sockets client (which includes SPX) is actively sending or receiving data to or from SQL Server, and the network connection is lost.

10053 Software caused connection abort - This can happen if a sockets client (which includes SPX) aborts the connection.

232 The pipe is being closed - This can occur if a named pipes connection is lost during a read.

109 The pipe has been ended - This can happen if a named pipes connection is lost during a write.
Client-Side Errors

10008 Bad token from SQL Server: data stream processing out of sync - Often caused by an abruptly terminated network connection, which causes a damaged Tabular Data Stream token to be read by the client.

10010 Read from SQL Server failed - The client was reading from the SQL Server network data stream, and the read returned a failure code.

10018 Error closing network connection - The client attempted to close a network connection and the close returned a failure code.

10025 Write to SQL Server failed - The client was writing to the SQL Server network data stream, and the write returned a failure code.

Immediate Opening for TCS - Hyderabad - Java 1 to 3 years

Immediate Opening for TCS - Hyderabad - Java 1 to 3 years
Apply only if you are ready for Face to Face interview on 10th- Sep (Monday)

Experience Required: 1 to 3 years
Job Location: Hyderabad
Salary: Do not disclosed by the recruiter


Qualification:

Any Graduation / Post Graduation  with minimum 50 percentage of marks

Must be good at programming skills

Proffered  : JSP, Servlets, HTML, Java Script

Struts framework would be an added advantage but not mandatory.

Candidates ready for a face to face interview could send in their updated CV along with your current contact details at the earliest to uarumilli@gmail.com along with the below details

Full Name:
Relevant Exp:
Current Location
Notice Period:
Can Attend a face to face interview within this week?(Y/N): 

Senior Database Engineer required for Thomson Reuters - Hyderabad / SQL Developer job at Thomson Reuters , Hyderabad

                                       

Designation: Senior Database Engineer

Job Location: Hyderabad
Notice Period: Must be ready to join at the earliest (Max 30 days)
Experience : 4 Years & above
CTC: As per Industry Standard
Job Type: Permanent

Hi,

We are pleased to inform you that, we have an urgent opening with our organization Thomson Reuters – Hyderabad.

Qualification:

 1. At least 4 years hands on experience in design and development of databases using Microsoft SQL Server 2005/2008
2. Expertise in designing and developing database solutions for enterprise transactional and reporting applications
4. Having knowledge in designing and modeling relational databases is an added advantage.

5. Strong ability to understand complex business logic and requirements to derive effective solutions
6. Extensive experience in T-SQL programming, developing, debugging and analyzing stored procedures with error handling, cursors, nested stored procedures, etc.
7.Expertise in creating/optimizing/debugging Stored Procedures, Functions, and Views
8.Good knowledge of data security best practices
9. Bachelor's degree in Computer Science or Math

10.  Dotnet frame work is an added advantage.

11. Must be able to debug/understand ADO dot net code.

12. Extensive knowledge in handling XML quires and dynamic quires

If you are interested in above opening then please help us with

updated profile along with below details ASAP:  

1) Total Exp:  
2) Relevant Exp:
3) Current CTC:
4) Expected CTC:
5) Notice Period:
6) Current Location:
7) are you ready to relocate (Hyderabad)? (Y/N):
8) Date of Birth:

Frequently Useful DBCC Commands In Sql Server DataBase

Microsoft SQL Server  provides a set of commands that will help you, called the DBCC commands. Originally that stood for Database Consistency Check commands, but they’ve been expanded since then to do a bit more. The DBCC commands are divided into four main categories: 

1. Status commands
2. Validation commands
3. Maintenance commands
4. Miscellaneous  commands.
 Let’s take a look at a few of the more common ones. 

How Import multiple images to SQL Server using SSIS

Sometimes we need to import thousands of images to SQL Server. This example shows how to import a list of images to SQL Server using SQL Server Integration Services.

Requirements

• SQL Server Enterprise or Standard (in this case I am using SQL Server 2008 R2, but it can work with SQL Server 2005 as well).
• SSIS installed (it is included in the SQL Server installer).

Demonstration

1. First of all, it is necessary to create a flat file named listImages.txt (or the name of your preference) with the paths of the images that you want to import to SQL Server such as the following:
   
   

Get the data from excel sheet to sql server

we can use the query according to our system requirement for example i have my system configuration like below:

os :windows 2003 server

sql server: 2005

ms excel: 2007

i select data from excel using the following configuration and query.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

SELECT * FROM

OPENROWSET

(

'MICROSOFT.ACE.OLEDB.12.0',

'EXCEL 12.0;DATABASE=D:example.XLSX;',

'SELECT * FROM [SHEET1$]'

)

What is Data Base Schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Creating a Database Schema

1. Complete the details in the General tab for the new schema. In this example, the schema name is "person" and the schema owner is "Homer".
   
2. Add users to the schema as required and set their permissions:
   
3. Add any extended properties (via the Extended Properties tab)
4. Click OK.

Add a Table to the New Schema

Now that we have a new schema, we can add objects such as tables, views, and stored procedures to it. For example, we could transfer the table that we created in the earlier lesson to the new schema.
When we created that table (called "Individual"), it was created in the default database schema ("dbo"). We know this because it appears in our object browser as "dbo.Individual".
To transfer the "Individual" table to the person "schema":

1. In Object Explorer, right click on the table name and select "Design":
   
2. From Design view, press F4 to display the Properties window.
3. From the Properties window, change the schema to the desired schema:
   
4. Close Design View by right clicking the tab and selecting "Close":
   
5. Click "OK" when prompted to save

Your table has now been transferred to the "person" schema.

Confirm your Change

To confirm the change:

1. Refresh the Object Browser view:
   
2. You will now see that Object Browser displays the new schema for the table (person.Individual):