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
2 answers

SSIS Http Get Request Json Deserialize

I am attempting to retrieve data from a JotForm form using SSIS and it works only as far as getting a response, however the data is very messy and needs to be deserialzed. Below is a sample of the response (I put a breakpoint in the script and just…
GBM
  • 99
  • 6
3
votes
1 answer

SSIS Derived Column Expression to skip null/blank value and take the next digits of string value

I am very new to SSIS and I am performing a task where I have to take the flat-file text document and then derive the columns with specific digit lengths. For eg, lines in the text document are: 101001A00000000000000309493020111139 …
3
votes
1 answer

Do I need to remove indexes on a table before a large load?

I am working in a data warehouse, and the standard setup for loading a table is to insert rows into the table for the nightly load that only loads changes. Still, if a full history load is run, all the indexes on the dimension or fact table are…
Dave
  • 61
  • 6
3
votes
1 answer

Extracting dtsx from Integration Service Catalog from C#

From a local C# program, I am trying to connect to an SSMS server's Integration Services Catalog (SSISDB) to read the .dtsx file(s) that are in there. I have the IP/u/p of the server, but after that, how would I get the .dtsx files?
bloodpresage
  • 109
  • 1
  • 7
3
votes
1 answer

SSIS Data Flow OLE DB To Excel Nvarchar Size Issue

Hopefully, this is not an ignorant question as I am still working to build SSIS Skills. I Have a package that takes an excel sheet and loads it into an SSMS SQL table so that I can run analysis and update statements to the data. I am now looking to…
Eric G
  • 55
  • 5
3
votes
1 answer

Quarterly data load in SSIS package

I just started using the SSIS tool and I need quick help to load data quarterly Here`s my scenario: I came up with a query ( source Database: DB2 ) that will extract data from 2010-01-01 to 2021-12-31,(11 years of data) however the data volume is…
3
votes
1 answer

OLE DB Destination: Invalid character value for cast specification

My table source: num_facture TYPE actif date 1 1 1 2010-01-31 00:00:00.000 2 2 1 2011-01-31 00:00:00.000 3 3 2 2012-01-31 00:00:00.000 4 4 2 2013-01-31 00:00:00.000 The Column data types are: Column Data…
chaneb
  • 67
  • 1
  • 6
3
votes
1 answer

Visual Studio 2019 - Cannot add Custom Pipeline Object to Toolbox SSIS

All I'm trying to accomplish right now is get my custom component to show up in the SSIS Toolbox. I've been looking everywhere I can think of for any information about creating a custom Data Flow Component in Visual Studio 2019. I have found plenty…
Rbailey56
  • 77
  • 5
3
votes
1 answer

How to get the goal name or goal ID from Google Analytics in SSIS?

Is it possible to get the goal name or goal ID from the Google Analytics Source task (ZappySys) in SSIS? I've been searching for it for a long time now and I just can't find it in the Dimensions or Metrics from the task. I also tried to use the…
3
votes
2 answers

What is the simplest way to delete files older than 90 days from a folder?

I have a folder into which logs are produced every day. I want to create an SSIS package that will run every day and delete files older than 90 days (based on created date). I have worked out a solution using the script task (C#) and wanted to know…
variable
  • 8,262
  • 9
  • 95
  • 215
3
votes
1 answer

SSIS MySQL ADO.net SQL Task Input & Output

I am trying to use a SQL Task in Visual Studio SSIS to get two output values stored to variables. I have done a range of Googling on the issue and have been able to get inserts working but I can't seem to see the output values to come out. I am have…
dcfretwell
  • 43
  • 5
3
votes
1 answer

How to execute SqlCommand using OLEDB connection in SSIS script component

I am using SSIS 2019 and am able to execute SQL Command with ADO.Net Connection Manager. I want to use OLEDB connection manager in a Script component within a Data Flow Task and I am getting the below error: System.InvalidCastException: Unable to…
AMDI
  • 895
  • 2
  • 17
  • 40
3
votes
3 answers

Bulk importing text files / VB2005 / SQL Server 2005

I've inherited a .NET app to support / enhance which reads in a couple of files of high hundreds of thousands of rows, and one of millions of row. The original developer left me code like :- For Each ModelListRow As String In…
cometbill
  • 1,619
  • 7
  • 19
  • 41
3
votes
1 answer

SSIS Conditional Split Error - The expression evaluated to NULL, but the "Conditional Split" requires a Boolean results

It is my first time using this software. I am trying to split the value but it is showing this and I do not have any null have when I see my source but when I preview it, it shows null value in the first 200 columns but I have only 15…
3
votes
0 answers

Load XLSX/XLSB format excel in SSIS

Trying to load Xlsx/Xlsb Excel format into SSIS I followed the below links but I cannot achieve the requirement: Import Excel binary files into SQL Db using SSIS The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the…
Reni
  • 33
  • 3
1 2 3
99
100