SSIS

1K

Solutions

21

Articles & Videos

1K

Contributors

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

Share tech news, updates, or what's on your mind.

Sign up to Post

Balanced Data Distribution
I have a large data set and a SSIS package. How can I load this file in multi threading?
0
[Webinar] How Hackers Steal Your Credentials
LVL 9
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Load multiple files in SSIS
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even we can use SSIS to download files from FTP server.
0
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
1
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
A very well written article on essential SSIS functionality dealing with record changes.  Voting Yes.
0
Fuzzy Lookup Transformation
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
2
 
LVL 50

Expert Comment

by:Vitor Montalvão
Comment Utility
Very nice
0
SSIS Sample Source to Target
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
8
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
Yes. I'm thinking that I'll need well-educated friends :)

Mike
0
 

Expert Comment

by:Srishitha kondreddy
Comment Utility
Hi Jim,

Good Evening.


I am searching for the sample document given to the ETL developer by Business Analyst and I have found that your document suite for me. Could you please provide the sample document for the ETL developer and kindly let me know what are the inputs do we need to know to prepare the excel sheet document for specifications.


Thanks,
Srishitha.
0
Web Service Task and XML Task
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and XML Source components in SSIS.
4
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Very well illustrated, and reads real well considering how difficult the subject is.  Voting Yes.
0
How to load multiple sheets
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
2
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Outstanding article on a common Excel and SSIS issue.  Voted Yes.
1
 

Expert Comment

by:Mahzar Ahsan
Comment Utility
Great article. Solved my issue.
0
SSIS - Alternate Impression of Business Rule Validation
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
6
 

Expert Comment

by:satish reddy
Comment Utility
Hi Admin,

You have an excellent collection of SSIS interview question,

Recently we have updated all the latest SSIS interview on our post, if you like our team efforts, please add our blog post to further reference which will help your loyal visitors.

ssis interview questions and answers :- http://www.freshers360.com/ssis-interview-questions-and-answers-for-freshers-and-experienced/

SSIS Interview Questions :- http://www.freshers360.com/ssis-interview-questions/

Regards,
Satish
0
In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard). 

My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text files by C# code, not the dtexec tool. One of my clients upgraded their system to SQL Server 2014 while other clients have kept the 2008 version, but are using the same 2008 SSIS packages. We don't want to duplicate packages because it will take more time to manage (one for SQL 2008 and another for SQL 2014). My problem is "how to run them in both environments successfully." Some components of 2008 SSIS refer to 10.0.0.0 version of .dll files such as Script Task component... which are not existed in new environment, so they will fail when run. We will see this error message (or something like it):

System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {BA785E28-3D7B-47AE-A4F9-4784F61B598A} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

After researching,  I found out the solution to resolve this problem by inserting some configuration codes into .config file of our application.
<runtime>
    <gcServer enabled="true"/>
    <disableCommitThreadStack enabled="true"/>
    <generatePublisherEvidence enabled="false"/>
    <assemblyBinding 

Open in new window

0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
Comment Utility
I had similar issues with DTS/SSIS and 32-bit/64-bit.
I re-implemented SSIS with C# and stored procedures - it has no dependencies from MS SQL Server version anymore.
0
 
LVL 8

Author Comment

by:Dung Dinh
Comment Utility
Hi Andrei,

Your approach is the simplest but it only works if you have several SSIS packages.

How long will you spend if you have several hundres packages.

Thanks,
0
Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package).

The latter is certainly a headache for developers, particularly for me. We had to make use of Environment variable, SQL Table or XML config file for that. From a deployment perspective, SSIS packages are deployed to MSDB database for SQL Server deployment.

From SQL Server 2012 (SSIS 2012) there is a new feature called Integration Service Catalog. I am loving the feature. Let me explain why.

The feature provides the whole project deployment with project level variable and parameter deployment as well. In SSIS 2008, you have to deploy on 3 different environments (Dev, Test and Production). From SSIS 2012 there is new thing called Environment and just change the Environment on single deployment.

Let go through an example step by step.

1. Create Integration Service Catalog to Deploy SSIS Project using new feature of SSIS 2012

To deploy the project you need the Integration Service Catalog in the SQL Server instance. Please see the attached screen for reference.

Create Integration Service CatalogNote: You can not create more than one Integration Service Catalog. It is available in Database Engine service.

2. How to deploy SSIS project.

To See the deployment, we are here to target the Project parameters and new feature of Environment in Integration Service Catalog. Hence, …
2
 

Expert Comment

by:sksamguru
Comment Utility
Hi PatelAlpesh,

Is there anything to do from scripts task with environment variables. My company follow standard procedure first in script task which check all the connection and serverName and so and so fort then data flow task.

There environment variables xml file don't have much info
1.generated person details
2. ConfigurationType, Path from variables
3. ServerName Path="\Package.Variables[User::Environment].Properties[Value]" ValueType="String">
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service.

Of particular interest, and the focus of this Article is SSIS. So, time to elaborate one by one some of these more important enhancements in SSIS.
 

1. SSIS Studio


1. BIDS is replaced by SQL Server Data Tool.

It has several enhancements like grouping of task and multi-line comments in Data flow task

Grouping  and multiline comment in Data flow2. Introduction of Parameter in SSIS.

As like command line or any other application you can pass parameter to SSIS package or even from Parent to Child package as well.

Same way you can create parameters at project level as well. The parameter define at project level is available and shared with all packages in project.
 
SSIS Parameter3. SSIS package / project Deployment Model.

Before SSIS 2012 all packages will be deployed to File system or on SQL Server.

Now onwards, the new feature Deployment  model is there and it's maintained in Database. To deploy the project Convert project to Project Deployment Model compatible using wizard  and create deployment Catalog that after.

SSISDB CatalogSSIS project deployment

2. SSIS Control flow Task


1. New CDC (Change Data Capture) Control flow Task

This task is useful to track the Change data (state) and manage that using CDC control task.

click here for more

2. Expression Task
2
 

Expert Comment

by:DipeshPrajapati
Comment Utility
Good Article Alpesh
0
From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a Scrip Task for you’re in the control flow and a Script Component in the Data Flow Task, let’s add a Script Task without configuring it as we’ll use it next. I’ll assume basic knowledge of SSIS, and skip how to create a project, solutions and the difference between Control Flow and Data Flows.

Let's create a variable      
Open the variable’s windows by going to View --> Other Windows --> Variables. Click on “Add Variable” button as seen on Figure 0.
Figure 0In the demo, we’ll create a DateTime data type variable called “DTLastWeek” , we’ll also use a custom NameSpace named “Demo”. For SSIS data types, please check
http://msdn.microsoft.com/en-us/library/ms141036(v=sql.105).aspx

Using Breakpoints in the Control Flow
The easiest and simplest to debug your variable value is to set a Break Point, it has several break conditions based on the task you’re setting the breakpoints on, for example when you’re using “ForEach Loop Container” you’ll get “Break at the beginning of every iteration of the loop”, and it’s simple a way to tell the SSIS control flow to take a break and pause to be able to debug the …
1
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.

Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve the problem of all SSIS developer.

The task analyze data in a SQL Server database and, from the results of that analysis, generate XML reports that can be saved to a file or an SSIS variable. By configuring one or more of the task's profile types, you can generate a report that provides details such as a column's minimum and maximum values, or the number and percentage of null values, or a column's min and max length with row count, or a column's value with row count, or a column's value's pattern with frequency, or column's functional dependency or a column's candidate key ratio etc.


 
Despite the variety of statistics that the Data Profiling task can provide, the task's practical applications might appear limited when you first try to implement it. After all, what use are data statistics to the automated processes in an SSIS package? However, by accessing the results generated by the Data Profiling task, you can design a workflow that automatically determines the appropriate actions to take based on the validity of the source data.

In this article, I describe a sample SSIS package that includes the Data Profiling task. The task is configured to generate a report based on the Value Inclusion profile type, which
1

Introduction

SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.)

But how far can you go?  When does the XML Source component become unusable?  Let’s find out!

To create the examples I’m using the Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64).
 

Basic Example

This first example is a really simple XML file containing a list of colors with their corresponding RGB code.

 
<colors>
  <color RGB="FF0000">Red</color>
  <color RGB="00FF00">Green</color>
  <color RGB="0000FF">Blue</color>
  <color RGB="FFFFFF">White</color>
  <color RGB="000000">Black</color>
</colors>

Open in new window


Let’s import this into a database.  Open up the BIDS, create an SSIS project and throw a Data Flow Task into the package and open it up.

The component that we’re now most interested in is the XML Source, one of the components in the Data Flow Sources category in the Toolbox.

 The XML Source componentAdd one of those to your Data Flow and double-click it to open up the XML Source Editor.

The Data Access Mode should be set to XML file location, which is the default setting.  The other options are XML file from variable – useful if you’ve got the file path and name of the XML file in a variable – and XML data from variable – interesting if your XML data is actually stored in a variable.

As XML Location, select the .xml …
5
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
Thanks Jim :)
0
 

Expert Comment

by:huyenb
Comment Utility
Hi Valentino

I really like your article, very useful and helpful as you integrated images so I can follow your steps
0

Introduction

In my previous article I showed you how the XML Source component can be used to load XML files into a SQL Server database, using fairly simple XML structures.  In this follow-up article I will demonstrate how to tackle the complex XML issue.
 

The Complex XML Example

You probably know that SSRS reports, RDLs, are actually XML files.  And they’re not the easiest types of XML files around.  To humans they are still readable but the structure can be quite complex.  So there we’ve got our example: an RDL.  More specifically I’ll be using the RDL that’s available for download in one of my earlier articles.
 

The Goal

Every good example has got a goal.  Our goal today is to retrieve a list of datasets and fields as defined in the RDL.  Shouldn’t be too difficult, right?
 

Using The XML Source Component

Let’s try to get this done through the XML Source component with which we’re very familiar by now.  You know the drill: drag an XML Source into your Data Flow, open it up and configure the XML and XSD locations.

Note: to be able to do this I cheated a bit by manually manipulating the RDL a little.  More precisely I removed all the namespace references from the <report> tag and further down the XML (removed “rd:”).

With both files configured, let’s have a look at the Columns page:

 The XML Source component handling a really complex XML file
7
 

Expert Comment

by:Giuseppe Serra
Comment Utility
Hi. How can I fetch an XML from a remote URL that also required credentials login? I have no clue how set up the package.
0
 

Expert Comment

by:Giuseppe Serra
Comment Utility
Hi. How can I fetch an XML from a remote URL that also required credentials login? I have no clue how to set up the package.
0
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternative technique you could gain much more performance out of your SSIS flow.

The recommendations and suggestions below represents real life experience and excerpts from the Microsoft Technet article : http://technet.microsoft.com/en-us/library/cc966529.aspx along with influence from the other links referred to at the bottom of this Article. While this Article presents a summary and major points to focus on from those resources, you are encouraged to follow the links for more specific and in depth discussion.

SSIS (SQL SERVER INTEGRATION SERVICES) is using a buffer-oriented architecture to efficiently load and manipulate datasets in memory, the benefits is avoiding the I/O to the disk and physically copying the data to the disk, and therefore it’s transferring the data from the source to the destination through the pipeline without touching the disk.

As this data flows through the pipeline, SSIS attempts to reuse data from prior buffers as much as possible when additional operations are performed. How buffers are used and reused depend on the type of transformations that you use in a pipeline.

There’re 3 types of transformations types:

1-Row
5
 
LVL 21

Expert Comment

by:Alpesh Patel
Comment Utility
Good one, foundsomething advanced.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Excellent article.  Studying for the 70-463 exam it was helpful to have another resource to read for blocking data flows and optimization tips.
0
This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing.

I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common question of my colleagues has inspired me to write an article on Experts Exchange.

In short, parameters are passed to SSIS packages in five different ways.
Note: You can see all of these options from the SSIS => Package Configurations Menu.
 

1. XML Configuration File

An XML configuration file works the same as a normal .config file.
 
<DTSConfiguration>
	<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
  		<ConfiguredValue>Data Source=[SERVERNAME];Initial Catalog=[DATABASENAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> 
  	</Configuration>
</DTSConfiguration>

Open in new window


You can assign values to .config file key (app key) and assign the property to a local variable within your package. When the package executes, the values of the local variable are replaced with the .config file property (key) value.
 

2. Environment Variable

Another option is to use an environment variable, which needs to be configured through Advanced System Properties of My Computer. Illustration below:
My Computer Property

3. Registry Entry

A third option, though somewhat risky, is to use a registry entry.  As with making any registry changes, if something goes wrong with registry, there is a chance the whole system could crash or go down.
 

4. Parent Package Variable

1
 

Expert Comment

by:sksamguru
Comment Utility
Hi PatelAlpesh,

Could you please describe little more on environment variables part. what is difference between selecting from configuration type and configuration location is stored in environment variable as indirect? Please see my attachment.
env-variable.jpg
direct-Env-Variable.jpg
0
Expressions are in essence, code. If you had an expression 2 + 2 it would evaluate to 4. A string expression of “Hello” + “ “ + “World” would evaluate to “Hello World”.

Expressions are an excellent choice for working with dynamic variables, especially when the value may change more than once during runtime, an expression evaluates the value each time it is referenced in the package. Expressions can combine other variables together to form a single variable.

The purpose of this example is not to teach expression building so I will limit the complexity.

To set a variable by an expression.
 

1. Select the Variable

Select the variable in the Variable Windowselectthevariable.png
 

2. Properties

Go to the Properties Window. Set EvaluateAsExpression to True and click on the elipse on the Expression line to open the Expression Builder.evaluateasexpression.jpg
 

3. Expression Builder

Click on the elipse and open the Expression Builder.ssisexpressionstep3.jpg

In the Expression Builder you can choose from Variables and Functions to create a variable. Since this is a string variable I also used the DT_WSTR function to convert the GETDATE() function to a string. Pressing on Evaluate Expression will let you test your results. If you get an error it will not let you press OK and save, it has to be error-free. I wish that was different.

Here is another expression that adds the date in a filename format.
expressiondate.png
Note, when I press OK on this I get an error.
expressionerror.png
0
In a previous article I've shown you how to import data from an Excel sheet using the OPENROWSET() function.  And I concluded by stating that it's not the best option when automating your data import.

Today I'll repeat the Excel data import process by using SQL Server Integration Services, also known as SSIS.

I'll be using SQL Server 2008 R2, but I'm quite sure that the process is very similar to the first release of 2008, and even to 2005.  The Excel file that I will be importing is the one used in my previous article, and I'll also refer to some parts of that article, so you may want to have a read over that one when something here isn't clear.

Furthermore I'm using a Windows 7 64-bit machine, with the ACE 14 driver (beta) installed.  To avoid any discussion about versions and for my own (future) reference, here's the result of a SELECT @@VERSION:
 
Microsoft SQL Server 2008 R2 (CTP) - 10.50.1352.12 (X64)   Oct 30 2009 18:06:48   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
 

Create SSIS Package To Import Excel Data


Usually you will start by creating a new package in an Integration Services project, add an Excel source to a new Data Flow, throw in some Data Flow Transformations and end your flow with an OLE DB …
5
 

Expert Comment

by:marvo2010
Comment Utility
Hey Buddy, Thanks for your article. I know alot stuff in it already but I like reading . Reading = IT professional or you will become out dated mate. Thanks for writing it.
I am also focused with BI now . Its exciting and rules the world cos we make businesses to know what they are doing.

cheers
0
 
LVL 8

Expert Comment

by:Dung Dinh
Comment Utility
'By the way I just published an article on How to load multiple sheets of an Excel File in SSIS', please read it and click the 'Good Article' button if it helped you.
0
Sometimes, you need to use PIVOT in SSIS to ensure your data matches the output requirements of your users. So, what is PIVOT and what is SSIS and how can that help ?

Firstly a quick explanation of those acronyms as described by Microsoft :
 
Microsoft SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including the extraction, transformation, and loading (ETL) of packages for data warehousing. SSIS is the new name assigned to the component formerly branded as Data Transformation Services (DTS).
 
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output
Sound OK so far ? So, what we will be doing is using the SSIS Tool to perform a Query and transform the output into a PIVOT result. This tutorial provides a complete pivot sample on AdventureWorks sample SQL Server database with SSIS 2008... The database is downloadable from the Microsoft website.

But first, we do need some kind of "real world" scenario as a basis of our tutorial. So, let us assume the requirement that your Logistics manager wants to find order quantities for each product by year and have the output provided as a seperate output file (maybe a spreadsheet).

You may use the query to get the result:
 
select

Open in new window

3
 

Expert Comment

by:zolf
Comment Utility
Could not view/open the images for this article
0
 
LVL 66

Administrative Comment

by:Jim Horn
Comment Utility
Reza_rad - Would you mind editing this article, posting the images as images and not as file attachments?  Thanks in advance.
0
Today I will describe a subset of an ETL I made and I think that could be useful for you and it will be useful for me if I receive your feedback.

1. The Problem

I have customer's data (sent by an application by text files to a specific file system share), that must be imported to a SQL Server 2005 database, more precisely, to the customers database table.

All Fridays, all the data in the text file, must be extracted, transformed and loaded into the SQL Server 2005 database. After the extraction the file must be moved to another directory in the file system. (&\ProcessedFiles)

The text file is a fixed column type, and there is a column that describes the event to follow when ETL update the Customers table.
N (means new customer to insert)
A (means update existent customer)
D (means update/inactivate existent customer)
 

2. The Solution

The data will be extracted from the text file into a preTable (preCustomer) without any transformation/rules (to avoid risks). For each row imported to this preTable, I will have an extra column that is a foreign key to my audit table information. Next, I read all the records from the last extraction, transform and load into Customer table.
ArchitectureThe end user, from an asp application, will be able to correct or ignore the redirected rows, and next time the ssis package executes, these errors will disappear. (Note: the warning and error rows will be stored in redirectCustomer table, until some action …
3

SSIS

1K

Solutions

21

Articles & Videos

1K

Contributors

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.