Stack Overflow
Stack Overflow

Questions tagged [ssis]

SSIS (Microsoft SQL Server Integration Services) is a platform for building enterprise-level data integration and data transformations solutions. SSIS is used to perform ETL operations; i.e. extract, transform and load data.

SSIS is an ETL tool from a Microsoft product, it used to solve complex business problems by extracting the data from different sources and do the business transformations and load it to the respective target systems. The packages can work alone or together with other packages to address complex business needs. SSIS can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

These types of operations are commonly referred to in the industry as ETL, which stands for:
E​xtract, T​ransform and L​oad.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the GUI to create solutions without writing a single line of code, or you can program the extensive Integration Services object model to create more complex tasks.

The precursor to SSIS was released with SQL Server 2000, and was called Data Transformation Services (DTS).

  • SSIS was introduced with SQL Server 2005, and was a complete overhaul of the component, centering around control-flow-centric DTSX packages.
  • There were minor improvements with SQL Server 2008.
  • The SQL Server 2012 version contains many differences from the 2008 version, including changes to deployment, debugging, troubleshooting and other areas. It has also had a visual facelift, with more attention to ease of development.
  • SQL Server 2014 didn’t have any changes for SSIS, but on the side new sources or transformations were added to the product. This was done by separate downloads trough CodePlex (an open-source code website) or through the SQL Server Feature Pack (example: Balanced Data Distributor).
  • In SQL Server 2016 there were some updates to the SSIS product. Instead of deploying entire projects, you can new deploy packages individually again. There are additional sources – especially cloud and big data sources – and some important changes were made to the catalog, also it supports Backward Compatibility and you can build Package parts that can be reused in many packages.

SSIS and Visual studio versions

  • SQL Server 2022: SQL Server Integration Services Projects 2022 extension (public preview)
  • SQL Server 2019: Integration Services Projects extension
  • SQL Server 2017: SQL Server Data Tools for Visual Studio 2017
  • SQL Server 2016: SQL Server Data Tools for Visual Studio 2015
  • SQL Server 2014: SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence for Visual Studio 2013
  • SQL Server 2012: SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence for Visual Studio 2012
  • SQL Server 2008 / 2008 R2: Business Intelligence Development Studio from SQL Server 2008
  • SQL Server 2005: Business Intelligence Development Studio from SQL Server 2005

References:

21143 questions
209
votes
11 answers

'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

I get this error when I try to connect to any excel through MSSQL Server Data Import i.e. SSIS package The Microsoft.ACE.OLEDB.16.0 provider is not registered on the local machine. (System.Data) It is not the same version I think some other…
Suhail Abdul Rehman Chougule
  • 2,369
  • 2
  • 12
  • 23
95
votes
7 answers

Should programmers use SSIS, and if so, why?

As a .NET developer, for what reasons should I prefer SSIS packages over writing code? We have a ton of packages in production where I currently work, and they're a nightmare to both "write" (perhaps draw?) and maintain. Each package looks like a…
Charles
  • 6,199
  • 6
  • 50
  • 66
93
votes
5 answers

How to execute an SSIS package from .NET?

I have a SSIS package that eventually I would like to pass parameters too, these parameters will come from a .NET application (VB or C#) so I was curious if anyone knows of how to do this, or better yet a website with helpful hints on how to do it.…
Ken
89
votes
3 answers

Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column

I am using SQL Server 2008 import and export wizard. I need to import a database. I opened the SQL server import/export wizard and I went through the following actions:- for the destination I chose "SQL server native client 10". then I selected…
John John
  • 1
  • 72
  • 238
  • 501
89
votes
13 answers

SSIS Convert Between Unicode and Non-Unicode Error

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet in the Excel workbook, the…
Andrew
  • 1,728
  • 8
  • 28
  • 39
88
votes
4 answers

Watching variables in SSIS during debug

I have a project in SSIS and I've added an Execute SQL Task which sends its result out to a variable. I wanted to confirm the value because I was worried that it would try to write it out as a resultset object rather than an actual integer (in this…
Tom H
  • 46,766
  • 14
  • 87
  • 128
85
votes
7 answers

How do I view the SSIS packages in SQL Server Management Studio?

Argh! I created an SSIS package via an Import Wizard and I can't find the SSIS packages on the server using Management Studio. Execute an SSIS package doesn't appear as an option when I go into job scheduler, either.
Caveatrob
  • 12,667
  • 32
  • 107
  • 187
79
votes
4 answers

What is the SSIS package and what does it do?

Can someone tell me what the SSIS package is all about? I am totally new to data-warehousing and I am going crazy with the terms SSIS, DTL, ETL and so on. I am looking for a big picture and how it fits in the overall .NET/SQL Server architecture.
kjayakum
  • 1,205
  • 3
  • 14
  • 15
76
votes
12 answers

Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

I'm trying to import a flat file into an oledb target sql server database. here's the field that's giving me trouble: here are the properties of that flat file connection, specifically the field: here's the error message: [Source -…
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
74
votes
3 answers

Using SSIS BIDS with Visual Studio 2012 / 2013

I want to use SSIS /BIDS project in Visual Studio 2012. I have both Visual Studio 2010 and Visual Studio 2012 installed along with Microsoft SQL Server 2012. VS 2010 was installed first followed by MS SQL 2012 which got me BIDS integrated with VS…
frictionlesspulley
  • 11,070
  • 14
  • 66
  • 115
66
votes
7 answers

Text was truncated or one or more characters had no match in the target code page When importing from Excel file

I have an excel file with four text columns: one of them is called ShortDescription which has the longest value. I created a table in SQL Server 2008 database, with four columns and the ShortDescription column type is set to NvarChar(Max). but when…
Nour
  • 5,252
  • 3
  • 41
  • 66
66
votes
14 answers

.Net vs SSIS: What should SSIS be used for?

If I have the option of using .Net and can do data transformations just fine in .Net, when would I need SSIS? Is there a certain task that SSIS would be better for? Are the added benefits of transparency worth it? Is it just what I am more…
jle
  • 9,316
  • 5
  • 48
  • 67
66
votes
9 answers

Temporarily disable all foreign key constraints

I am running an SSIS package which will replace data for a few tables from FlatFiles to existing tables in a database. My package will truncate the tables and then insert the new data. When I run my SSIS package, I get an exception because of the…
HaBo
  • 13,999
  • 36
  • 114
  • 206
64
votes
16 answers

Import Package Error - Cannot Convert between Unicode and Non Unicode String Data Type

I have made a dtsx package on my computer using SQL Server 2008. It imports data from a semicolon delimited csv file into a table where all of the field types are NVARCHAR MAX. It works on my computer, but it needs to run on the clients server. …
Mike Thomas
63
votes
3 answers

How to fix the embedded text qualifier issue while exporting data to CSV flat file?

###RFC 4180: RFC 4180 defines Common Format and MIME Type for Comma-Separated Values (CSV) Files. One of the requirements of the RFC 4180 is stated as below. This is the point #7 in the RFC link. If double-quotes are used to enclose fields, then a…
user756519
1
2 3
…
99 100