Home » Posts filed under SSIS 2012
Showing posts with label SSIS 2012. Show all posts
Showing posts with label SSIS 2012. Show all posts

SSIS 2012 SSIS 2008 Moving data from a text file to excel sheet

Posted by 20Fingers2Brains on Tags: , , ,


SSIS 2008 or SSIS 2012 .Moving data from a text file to excel sheet

In this SSIS 2008 demo we are moving data from a text file to an excel sheet.
The target and Destination are having the same data structure.
This demo is done in Business Intelligence development studio and SSIS 2008 but it may also work in SSIS 2012 as the steps are the same.

Step 1:

Open Business Intelligence . Select File -New Project .From the new project window select SQL Server Integration Services Project and name it as MySSISProject and select the paramters as shown in the below screenshots



Step 2:

Double click on Package.dtsx Package which is inside SSISPackages folder in the solution explorer. From the Toolbox drag drop DataFlowTask on the Package.it is as shown in the below screenshot.


Step 3:

Double click on the DataFlowTask.Now drag drop an flat file source and excel destination as we are moving data from a text file into the excel sheet.
Follow the steps as shown in the below screenshot


Step 4:

Now Double click on the FlatFlat file source it will open a window.
from the window select new and then it will  open another window Name the Connection as EMPText and then browse your text file.
Once the text file is loaded it can be easily viewed in Columns,Advanced and Previewmode. Once all that is done just click ok.
Tick the Check box Column names in first line.

Once the FlatFile connection manager is done link the flatfileTask to ExcelDestination task
Now we have created our source next step is about the excel destination




Step 5:

Similarly double click on Excel Destination and follow the steps as shown below.



After following this steps you will find that there are two Connection Managers in the connection manager window at the bottom

Now if you see after doing the connection from the flatfile to excel it is showing an error like cannot convert between Unicode and non Unicode string datatypes



To fix this error we need to change the data type of Flatfile connection manager .
So double click on Flatfileconnection manager and change the datatype of all columns



 Step 6:

Once the package is ready without errors we can run the package and it will turn green once it is successfully executed.



Conclusion :
After doing this SSIS demo you will be able to move data from an Flatfile source to Excel destination. This demo will work in SSIS 2008 and may also work in SSIS 2012.
In this example we have learned how to move flat file data to excel sheet.


If you have any queries or issues please leave a comment below 

SSIS 2012 2008 Connection manager Excel Connection Manager Error SSIS Error Code

Posted by 20Fingers2Brains on Tags: , , ,
SSIS 2012 or  2008  Connection manager Excel Connection Manager Error SSIS Error Code




SSIS Error :

DTS_E_OLEDB_EXCEL_NOT_SUPPORTED The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.


While executing a package an moving data from database to excel sheet I got this error . 
so decided to provide the solution to this error.

This is just a small issue. Looking at this error for the first time I thought it is related to some driver or I need to update some settings

Solution: 

Right click on the Solution and go to Properties as shown below
In the Configuration Properties select the debugging tab 





SSIS 2012 2008 Interview Questions with Answers

Posted by 20Fingers2Brains on Tags: , , ,


SSIS 2012 2008 Interview Questions with Answers

Today i will share with you the list of SSIS most frequently asked interview questions with answers.
SSIS 2008 or SSIS 2012 interview questions will help you prepare better for your interviews 

You can also check our Top 10 Interview Questions on SSAS and SSRS 
Top10 SSRS 2012 Interview Questions and Answers
Top10 SSAS 2012 Interview Questions and Answers


1. What are the different types of Data flow components in SSIS?
There are 3 data flow components in SSIS.
1. Sources
2. Transformations
3. Destinations
2. Explain Audit Transformation ?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are :
1. Execution of Instance GUID : ID of execution instance of the package
2. PackageID : ID of the package
3. PackageName
4.VersionID : GUID version of the package
5. Execution StartTime
6.MachineName
7.UserName
8.TaskName
9.TaskID : uniqueidentifier type of the data flow task that contains audit transformation
3. Explain Copy column Transformation?
This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.
4. Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.
5.Explain Multicast Transformation?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.
6.What is a Task?
A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language

7.What is a workflow in SSIS ?
Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages
8.What is the Control Flow?
 When you start working with SSIS, you first create a package which is nothing but a collection of tasks or package components.  The control flow allows you to order the workflow, so you can ensure tasks/components get executed in the appropriate order
9.What is a Transformation?
 A transformation simply means bringing in the data in a desired format. For example you are pulling data from the source and want to ensure only distinct records are written to the destination, so duplicates are  removed.  Anther example is if you have master/reference data and want to pull only related data from the source and hence you need some sort of lookup. There are around 30 transformation tasks available and this can be extended further with custom built tasks if needed.
10.How many difference source and destinations have you used?
It is very common to get all kinds of sources so the more the person worked with the better for you. Common ones are SQL Server, CSV/TXT, Flat Files, Excel, Access, Oracle, MySQL but also Salesforce, web data scrapping.

New SSIS Project 2012 and 2008 Integration Services

Posted by 20Fingers2Brains on Tags: , , , ,

SSIS 2012 or 2008 creating a new SQL Server Integration Project 

  1. In this SSIS 2012 demo i will explain how to create an SSIS integration project in SQL Server Integration services project in BIDS 2012 or 2008
  2. SSIS 2012 or SSIS 2008 is a powerful tool that is used for Data Migration and Data Integration and is an important part of Microsoft Business Intelligence (MSBI)
  3. The tools that we have used are SQL Server 2008 or 2012 ,Business intelligence development studio 2008 or 2012
Step 1:click start then open Business intelligence development studio(BIDS)
Step 2:Once the Business intelligence development studio(BIDS) is opened click on file-new project-a new project window will open
Step 3: From the project window select Integration services project .Then give the project name , solution name,project location and then click on ok
Step 4: The new SSIS 2008 or 2012 project will have Solution explorer Properties and the package in design mode
Solution explorer will contain Data Sources,Data source views,SSIS Packages with Default Package.dtsx package as shown in the below
Step 5: when we click on the SSIS Package.dtsx it gets opened in design mode
The design mode is divided into tabs such as
Control flow ,data flow,event handlers,package explorer




when we right click the control flow area we can find that there are many options available such as variables ,logging ,properties ,select all and many more

Step 6:At the bottom we have connection managers section. In this sections we can add the connections that are needed by our ssis packages to move data from source to destinations
Some of the connection managers types are Flat File connection,ADO.NET Connection ,OLE DB Connection and many more

Conclusion:
This SSIS demo will be helpful in making a empty project in SQL Server Integration Services Project


SSIS Package 2012 Bulk Insert Task with Examples

Posted by 20Fingers2Brains on Tags: , , , ,



Bulk Insert task in SSIS 2008 and SSIS 2012 
  1. In this i will explain the importance of bulk insert task in ssis 2008 or ssis 2012 and i will also give some examples
  2. The Bulk Insert task can only transfer data from a text file into a SQL Server table. 
  3. Bulk Insert task supports only Object Linking and Embedding, Database (OLE DB) connections for the destination database.
  4. While the Data Flow task can provide this capability, the Bulk Insert task is the most efficient method for this type of transfer, because it cannot perform transformations or error logging on the data while it is moving from the source file to the table.
  5. If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs. 
  6. If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task.
  7.  The Bulk Insert task is particularly useful in situations where data needs to be retrieved from a system that cannot be connected to SSIS. Text files can act as a great intermediary data source and the Bulk Insert task will minimise the time required to import the data into a SQL Server table.
The Bulk Insert Task Editor contains four property categories:
General.  
  1. This category includes a Name and Description property.
Connection. 
  1.  This category includes a File property to define the source text file connection. 
  2. The connection category also contains a Connection and Destination Table property to define the destination SQL Server database and table. 
  3.  The text file RowDelimiter and ColumnDelimiter property can be used to define the text file format or if the Format property is set to Use File, a FormatFile can be used to define custom mappings between the text file and SQL Server table. 
  4. If you have a format file that was created by the bcp utility, you can specify its path in the Bulk Insert task. 
  5. The Bulk Insert task supports both XML and non-XML format files.
Options. 
  1.  On the Options page, the Codepage and DataFileType property is used to define the type of text file. 
  2. The BatchSize determine how many rows are inserted into the table as a batch. 
  3. A setting of zero means all rows in the text file are inserted as one batch.
  4. If a batch size is set, each batch represents a transaction that is committed when the batch finishes running. 
  5. The FirstRow and LastRow properties determine the starting and ending row of the data in the text file. 
  6. You can also use the MaxErrors property to determine the number of errors that are allowed before the Bulk Insert task fails.

SSIS 2012 2008 Data Analysis Pivot Unpivot Data Mining Query transformation

Posted by 20Fingers2Brains on Tags: , , , , ,


Data Analysis Transformations in SSIS 2012 2008 

In this article i will explain SSIS data transformations  that enables you to analyse data, 
The SSIS Data transformations are as follows
  1. Pivot Transformation
  2. Unpivot Transformation
  3. Data Query Transformation
Pivot transformation
  1. The Pivot transformation takes data from a normalised result set and presents the data in a cross tabulated or denormalised structure. 
  2. For example, a normalised Orders data set that lists customer name, product and quantity purchased typically has multiple rows for any customer who purchased multiple products, with each row for that customer showing order details for a different product. 
  3. By pivoting the data set on the product column, the Pivot transformation can output a data set with a single row per customer. 
  4. That single row lists all the purchases by the customer, with the product names shown as column names, and the quantity shown as a value in the product column.
  5.  Because not every customer purchases every product, many columns may contain null values.
The Advanced Editor for Pivot dialog box contains three tabs to configure the properties:
  • Component Properties tab. The Component Properties tab allows you to specify a Name and Description for the OLE DB Command Task. You can also specify a locale with the LocaleID property and whether the data flow is validated at run time or design time by using the ValidateExternalMetadata property.
  • Input Columns tab. The Input Columns tab consists of the Available Input Columns property that enables you to select the columns that the Pivot transformation operation will affect. When a column is selected, it appears in the Input Columns list. The Output alias allows you to define the name of the output column. The Usage Type property defines if the data imported is READONLY data or READWRITE data.
  • Input and Output Properties tab. The Input and Output Properties tab allows you to set the properties of the input and the output columns. The most important property here is the PivotUsage property. This determines what role the input column will play in creating the pivot table and can be configured with the following values:
    • 0. The column is not pivoted, and the values are passed through to the transformation output.
    • 1. The column is part of the set key that identifies one or more rows as part of one set.
    • 2. The column is a pivot column. At least one column is created from each column value. This data must be sorted input column.
    • 3. The values from this column are placed in columns that are created because of the pivot.

Unpivot transformation

  1. The Unpivot transformation takes data from a denormalised or cross-tabulated result set and presents the data in a normalised structure. 
  2. The Unpivot transformation can be configured with the following properties.
  3. At the bottom of the Unpivot Transformation Editor dialog box is the Pivot key value column name. 
  4. Here, you define a column heading for the column that will hold the pivoted data that is converted into normalised data such as Products or Fruits.
  5. The Available Input Columns property enables you to select the input columns that the Unpivot transformation operation turns into rows. 
  6. When a column is selected, it appears in the Input Columns list. Any columns that are not selected are passed through to the data flow output. The Destination Column allows you to define the name of the destination column in the normalised output.
  7. In the Unpivot scenario, multiple input columns are usually mapped to one destination column. 
  8. For example, the Available Input Columns may consist of column headings such as Apples, Pears and Peaches.
  9.  All of these input columns are mapped to a destination column named Fruits that may be defined by the Pivot key value column name property.
  10. The Pivot Key value property specifies the value that is used in the rows in the normalised result set and, by default, uses the same name as the input column but can be changed.
Data Mining Query transformation
  1. The Data Mining Query transformation enables you to run Data Mining Expression (DMX) statements that use prediction statements against a mining model.
  2.  Prediction queries enable you to use data mining to make predictions about sales or inventory figures as an example. 
  3. You can then create a data flow output of the results. One transformation can execute multiple prediction queries if the models are built on the same data mining structure.
  • Mining Model tab. The Mining Model tab is used to provide an existing Connection to the Analysis Services database. You can specify a new connection by clicking the New button. The Mining Structure allows you to specify the Data Mining Structure that is to be used as a basis for analysis. A list of mining models is then presented.
  • Query tab. The Query tab allows you to write the DMX prediction query. A Build New Query button is provided to build the DMX prediction query through a builder.

SSIS 2012 For Begineers Introduction to SQL Server Integration Services

Posted by 20Fingers2Brains on Tags: , , ,

Introduction to SSIS 2012 For Beginners 

  1. In this article i will give a basic idea about ssis 2012 and the different scenarios when to use ssis
  2. SSIS represents the foundation of a BI solution. 
  3. While the impact and importance of this tool is not readily seen by the end users, it is critical for providing the data that would be used to populate a data warehouse that would be eventually used by Analysis Services or Reporting Services. 
  4. SSIS can also be used to perform unplanned data transfers to meet business needs as they arise, some of which can be handled by the Import and Export wizard. 
  5. The Import and Export wizard is usually the first tool that database professionals use to interact with SSIS. However, SSIS itself is an extremely flexible tool that can go beyond the capabilities that the Import and Export wizard has.
  6. SQL Server Integration Services (SSIS) is an Extract, Transform and Load tool (ETL) that can be used to move and transform data within an organization. 
  7. SQL Server Integration Services holds the entire logic of ETL operations within a package. 
  8. A package represents a single unit of work. Within the package, SSIS uses two engines to control the workflow of the SSIS package; the control flow engine and the data flow engine.
  9. The control flow engine contains tasks that direct how the package should operate as a whole by using precedence constraints. 
  10. The tasks that are available in the control flow can be broadly categorised into data-dependent tasks and data-independent tasks.
  11. Some data-independent tasks include the Send Mail task, File System task and Execute Process task.
  12. These types of tasks do not work with data directly, but can be added to a package to complement other tasks that may load the data. For example, you may use a Send Mail task to run when another task in the control flow fails and you need to inform the administrator by e-mail of the error.
  13. Data-dependant tasks include the Bulk Insert task and the Data Flow task. 
  14. These types of tasks are typically responsible for the movement of data between source and destination servers. All tasks within the control flow engine have their own properties window that requires configuration; however, the Data Flow task is unique.
  15. It is the only task that has its own designer interface. The Data Flow task exposes the data flow engine. Within this, you can focus specifically on how the data movement occurs. 
  16. This can involve defining data flow sources and destinations. You can also configure and finely tune the type of transformations that will take place within the Data Flow task.
  17. Collectively, the control flow and the data flow engine work at the heart of SSIS to provide the ability to move and transform data. 
  18. Additional components of SSIS include event handlers that expose the same tasks that are available within the control flow engine. The difference is that the tasks in the event handler will only run on a specific event. 
  19. This allows you to create robust packages. Variables can be used to add dynamic capabilities to packages, passing values between tasks and other packages for custom data loads.
  20. Package configurations can be used to ease the deployment of packages into a production environment by populating package settings at run time. 
  21. This is useful when you may not exactly be aware of the setup of your production environments

Scenarios for Using SQL Server Integration Services

SSIS may primarily be used to populate a data warehouse within an organization to support its BI infrastructure. 
In this scenario, the primary objective of SSIS is to:
  • Extract the data from source systems.
  • Transform or change the data so that it is cleansed and represented in a standard format.
  • Load the data into the destination system, typically the data warehouse.
SSIS can also be used to facilitate other tasks within the organization outside the scope of the data warehouse. 
These scenarios include:
  • Using the Import and Export wizard to perform unplanned data transfers.
  • Using SSIS to move databases, database objects, custom error messages and SQL Server Agent jobs from one instance of SQL Server to another
  • Merging and archiving data into a separate data store

SSIS 2012 SQL Server Integration Services Packages and Variables

Posted by 20Fingers2Brains on Tags: , , , ,

SQL Server Integration Services Components in SSIS 2012 and 2008
  1. There are core components of SSIS that enables you to create an ETL solution that can be used to facilitate a part of a BI solution. 
  2. In this article i will be explaining package ,variables and package configurations 

Package in SSIS 2012 or 2008

  1. A package is the highest object in SSIS and represents a single unit of work.
  2. A package can host multiple packages within it to control the flow of multiple packages. 
  3. Typically, it contains control flow and data flow elements to manage data movement and transformations.
  4. You can create packages by using Business Intelligence Development Studio or the Import and Export wizard
Variables in SSIS 2012 or 2008
  1. Variables can be used to provide additional functionality to SSIS packages. 
  2. SSIS provides a host of system variables. These system variables can be used for customisation of a package. 
  3. An example could include using the system variables to populate package information to a table using custom logging.
  4. SSIS also allows you to create user-defined variables.
  5. The names of user-defined and system variables are case-sensitive. These variables can be used to hold a value that may be used elsewhere in a package. 
  6. For example you may use a GETNOW() function to populate a variable. This variable may then be used in the WHERE clause of an Execute SQL Task to return results back where the ModifiedDate column of a table is equal to the value in the variable.
  7. In this respect, variables can prove valuable in providing package logic.
Package configurations in SSIS 2012 or 2008
  1. Package configurations are a useful tool to use to set package properties as the package executes.
  2. In scenarios where it is difficult to anticipate package properties before it is deployed, package configurations can help.
  3. For example, you may have a data source destination within a Data Flow task, but you do not know the name of the server. 
  4. You can use a package configuration to populate the data source destination's ServerName property with the environmental variable, ComputerName. 
  5. When the package executes, it will use the environmental variable ComputerName to populate the ServerName property at run time.
  6. Package configurations can be provided from one of five sources:
  • Environmental variables
  • Registry
  • SQL Server
  • XML configuration file
  • Parent package variable

SSIS 2012 Data flow in Integration Services

Posted by 20Fingers2Brains on Tags: , , , ,

Data flow in SSIS 2012 or 2008 Integration Services
  1. In this article i will be explaining the data flow components in ssis 2008 or 2012 
  2. Data flow is the component of SSIS that focuses specifically on the movement and transformation of data within the package. 
  3. There are three core components that can be used within the data flow:
Sources. 
Sources extract data from data stores such as tables and views in relational databases, files and Analysis Services databases.
Destination. 
Destinations load data into data stores or create in-memory datasets.
Transformations. 
Transformations make modifications to the data as it is moved between a data source and a data destination. 
The types of transformations that are available include:
  • Data Formatting transformations to change the format of the data.
  • Column transformations to copy, export or import columns.
  • Multiple Data Flow transformations to merge, split or join data together.
  • Custom transformations using .NET and ActiveX to perform custom transformations.
  • Slowly Changing Dimension to manage dimension tables in a data warehouse.
  • Data Analysis transformations such as data mining and Pivot for data analysis.
  • Data Sampling transformations to perform row counts and sampling.
  • Audit transformations to audit data changes.
  • Fuzzy transformations to apply fuzzy logic to data within a transform to standardise data.
  • Term transformations to extract specific data from text.
  1. Additionally, Integration Services provides paths that connect the output of one component to the input of another component. 
  2. Paths define the sequence of components and let you add annotations to the data flow or view the source of the column.

SSIS 2012 Control Flow Tasks Precedence constraint Precedence constraint

Posted by 20Fingers2Brains on Tags: , , , ,

Control flow Componets  in SSIS 2012 or 2008   Integration services 
  1. In this article i will be explaining you the control flow components in ssis 2012 
  2. Control flow is responsible for managing how the entire package operates
  3. There are three core components that can be used in the control flow to manage a package execution:
Tasks. 
  1. Tasks are commonly used to add functionality to a package. 
  2. This functionality is wide and varied and provides the ability to perform the work in the package. 
  3. The categories of tasks that are performed are as follows:
  • Data tasks work with data.
  • File and network tasks interact with the file system and FTP sites.
  • Scripting tasks add custom transformations and logic to SSIS packages.
  • Database object transfer tasks manage the movement of SQL Server objects.
  • Package execution task control the execution of SSIS and Data Transformation Services (DTS) packages.
  • Analysis Services tasks interact with Analysis Services.
  • Maintenance tasks perform common SQL administrative functions.
  • Windows Management Instrumentation (WMI) tasks interact with WMI.
Precedence constraint. 
Precedence constraints are used to control the workflow between the tasks within a package that will dictate what will proceed a task based on a failure, completion or successful execution of a previous task. You can also use expressions to control the workflow.
Containers.
Containers are used to group tasks together as one unit so that you can further refine the structure of the workflow within a package.

SSIS 2012 and 2008 Event Handlers with examples

Posted by 20Fingers2Brains on Tags: , , , ,


Event handlers in SSIS 2012 or 2008 Integration Services

  1. In this article i will explain the event handlers in SSIS 2012 
  2. Event handlers are a component that execute tasks based on an event that occurs at the package, container or task level at run time. 
  3. The tasks that can be performed within an event handler are the same tasks that are available within the control flow. 
  4. The events that can be defined include:
  • OnError. This event is raised by an executable when an error occurs.
  • OnExecStatusChanged. This event is raised by an executable when its execution status changes.
  • OnInformation. This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.
  • OnPostExecute. This event is raised by an executable immediately after it has finished running.
  • OnPostValidate. This event is raised by an executable when its validation is finished.
  • OnPreExecute. This event is raised by an executable immediately before it runs.
  • OnPreValidate. This event is raised by an executable when its validation starts.
  • OnProgress. This event is raised by an executable when measurable progress is made by the executable.
  • OnQueryCancel. This event is raised by an executable to determine whether it should stop running.
  • OnTaskFailed. This event is raised by a task when it fails.
  • OnVariableValueChanged. This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined.
  • OnWarning. This event is raised by an executable when a warning occurs.
Event handlers can perform the following tasks:
  • Clean up temporary data storage when a package or task finishes running.
  • Retrieve system information to assess resource availability before a package runs.
  • Refresh data in a table when a lookup in a reference table fails.
  • Send an e-mail message when an error or a warning occurs or when a task fails.
For example, 
An OnError event is raised when an error occurs. You can create custom event handlers for these events to extend package functionality and make packages easier to manage at run time.

Copyright © 2014 20Fingers2Brains. All rights reserved. New Super Seo modified by CB Blogger. Powered by Blogger