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
3
votes
1 answer

Unpivoting Data in SSIS

I am attempting to normalize data using SSIS in the following format: SerialNumber Date R01 R02 R03 R04 ------------------------------------------- 1 9/25/2011 9 6 1 2 1 9/26/2011 4 1 3 5 2 …
Brian Swart
  • 922
  • 1
  • 9
  • 25
3
votes
4 answers

SSIS Error while executing data flow task - import excel into sql table

I have created ssis package that is taking data from excel file and insert data into table I have one Excel Source and Ole db connection but i'm getting following errors: [Excel Source [1]] Error: There was an error with output column "F2" (18)…
Neo
  • 15,491
  • 59
  • 215
  • 405
3
votes
3 answers

How to run SSIS package without SQL Server?

On this link 2nd workaround method suggests to try running SSIS package on computer that doesn't have SQL instance. How is that possible? Do I need to have installed VS 2008 Business Intelligence or it's possible even without it?
ilija veselica
  • 9,414
  • 39
  • 93
  • 147
3
votes
2 answers

Create multiple flat file from running same query with different criteria

I have 10 Million customer numbers in my data base table. I want to run query every time for first 1 million customers and save that data in .csv file. How can I do that with SSIS package? Ex , First time: Select First name, Last name, Customer Id…
Sanjay
  • 41
  • 1
  • 3
3
votes
3 answers

How to calculate average time between intervals in SQL?

I have a SQL table where I need to work out the average time after each transactions: The data looks like: Tran1 07/09/2011 09:09:07 - CUSTOMER1 Tran2 07/09/2011 09:30:46 - CUSTOMER1 Tran3 07/09/2011 11:27:01 - CUSTOMER2 Tran4 07/09/2011…
user929153
  • 475
  • 2
  • 11
  • 25
3
votes
1 answer

Ignore records with null field when copying from one table to another using SSIS

Is it possible in a SSIS to ignore records that have a null field? For example i want to copy data from one database table to another, however Table A has some rubbish data in it and has some fields with nulls in them. I only want to copy records to…
Vince Ashby-Smith
  • 1,152
  • 4
  • 18
  • 36
3
votes
1 answer

Endpoint doesn't support BlobStorageEvents or softdelete exception

While trying to do a DataPreview or debug a pipeline, i am getting the below error stating "endpoint doesn't support blobstorage events or soft delete" I do not want to disable the soft delete
TheSacredKiller
  • 121
  • 3
  • 8
3
votes
0 answers

SQL Server Collation to Match SSIS Ordering

Example values from a SQL Server data set as they appear after an ORDER BY clause: X0000000-2009 X000000-1-2010 X0000001-2010 If I use ORDER BY Field COLLATE Latin1_General_bin they come out slightly differently…
Chris Mack
  • 5,148
  • 2
  • 12
  • 29
3
votes
3 answers

Azure storage connection to Datalake G2 in SSIS using Access Key

Test connection Connection manger in SSIS to the azure storage using access key succeeded. While copying data using Flexible file task in SSIS throwing an error "[Flexible File Task] Error: Could not load file or assembly…
Equator
  • 77
  • 8
3
votes
1 answer

How to use output parameter in SSIS execute sql task from a stored procedure

Using a parameter, @IsSuccess in stored proc. How to use that @IsSuccess parameter in SSIS Execute SQL Task and take that as output from that component to another component?
niveditha
  • 33
  • 1
  • 3
3
votes
1 answer

How to Get Sensitive Information Into SSIS Script Component in a Data Flow

I have an SSIS data flow that contains a script Component as a source. I'd like to generate the source data by running a script on an SQL Server database. The connection string to be used to connect to the database is set to be sensitive. How can I…
khidir sanosi
  • 161
  • 11
3
votes
1 answer

AddBlobData(Encoding.GetBytes(string)) Only grabbing first Character

I have been trying to assign/append a string to a blob column in an output buffer, on a C# script that's taking a number of input rows, and concatenating them where the related id is identical, passing onto the next row where the is new, and seem to…
Wenlocke
  • 155
  • 4
3
votes
1 answer

Execute SSIS package with Proxy - Could not get proxy data for Proxy_id

I am trying to execute an SSIS package using a Credential and Proxy - it works fine with a user with a SysAdmin role and we want to avoid using SysAdmin. I have followed all the steps to create a Credential and Proxy and set up the permissions for…
Ram
  • 527
  • 1
  • 10
  • 26
3
votes
1 answer

Script Editor won't open or change language in SSIS 2005

I have a task which creates a SSIS package using BIDS 2005. There are 2 problems when I work with BIDS 2005, but don't occur in BIDS 2008. Default language in Script Editor is Visual Basic (I prefer C#). When I click on the Design Script button, it…
QuachNguyen
  • 131
  • 11
3
votes
1 answer

SQL BULK INSERT using UNC path

I have a developer pc "A". And I have a SQL Server "B". My SQL Server is a Microsoft SQL Server 2019. On server B, I have a database with a stored procedure, which bulk loads data from a text file (using the BULK INSERT command). I have now created…
olf
  • 860
  • 11
  • 23
1 2 3
…
99
100