Questions tagged [ssis-2016]

SSIS 2016 is Microsoft SQL Server Integration Services 2016, and is part of SQL Server 2016. SSIS allows SQL Server to interact with other technologies, including import and export of data to and from other formats, or ETL, and data warehousing.

Filter by
Sorted by
Tagged with
0
votes
1answer
54 views

improving join performance on sql server table

I have a table on sql server lets say TableA. and a another table as TableB.Both of these tables are heavily loaded. Table A is loaded with records 35883788. It is holding a lot of duplicates on ...
0
votes
1answer
14 views

SSIS 2016 conditional split with multiple columns in conditions

I am having an issue in executing a conditional split for upserting records into my production table, using SSIS 2016 and MSSQL 2016 (Standard Ed.) I am trying to load two separate files (produced ...
2
votes
1answer
37 views

How to automate the changes of parameters in ssis package run by a job?

I have a job that runs a package with specific parameters as you can see on the picture below: I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27. I need ...
1
vote
1answer
11 views

Use SSIS to get measures from a tabular model

I need to get source data from an Azure Analysis Services via SSIS. While I can easily use an OLE DB source in SSIS to fetch columns from tables, I do not see measures listed. Is there a way to get ...
1
vote
1answer
29 views

SSIS: Working with 3 constrains where 2 should be OR and one should be AND

So I have an SSIS flow in which I am working with three constraint within a task, but 2 of them must be an OR and one must be an AND. In pseudo-code would be something like: if ( (constraint-a OR ...
0
votes
1answer
24 views

SSIS To Move Schemas

I'm having one heck of a time trying to compare and move schemas for a new database. I've tried Red-Gate, ApeXSQL etc. and every time I turn around it's not working. I have a customer who has 5 ...
2
votes
0answers
24 views

Using SSIS Catalog with an old (updated) SSIS package. The driver is out of date. Any way to update?

We are in the process of upgrading a server from SQL 2008 R2 to SQL 2016. I've taken a 2008 SSIS project and saved it in SSISDB. I'm getting a validation error that the driver isn't available. This ...
1
vote
1answer
72 views

SSIS: Scripts in package part not being executed when target server is 2016

I am having an issue with SSIS packages where scripts included in package parts ar not executed when the target server version is SQL Server 2016. Create an SSIS project, set target server version to ...
0
votes
0answers
48 views

SSIS: Retrieving the value of a Sensitive Parameter in a Script within a Package Part

I've been reading Matt Masson's article on how to read the value of a sensitive parameter in a C# script. However the script in question is in a package part, which does not seem to be able to access ...
1
vote
2answers
52 views

All Data Transformations via SSIS

Some folks here at my current job have made the decision that all data transformations that go into our database must be done via SSIS. This means that any old code such as views, stored procedures, ...
0
votes
1answer
80 views

SSIS Ensure a sequence container is not marked as failed if a specific task fails

Consider the following scenario for an SSIS package: A Sequence Container contains two jobs for loading data from an Excel file: Job A: Attempts to load data from an Excel file following a specific ...
0
votes
1answer
46 views

SSIS Fault Tolerant Data Connection

I feel like a may be standing too close to this one. I've inherited an SSIS package that has two data connection objects. On OLE DB connection (to perform work) and an ADO.NET connection (to a ...
2
votes
1answer
221 views

SSIS Package when executed via agent job or manually on SQL Server does nothing, but shows it completed successfully. What gives?

I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX ...
0
votes
1answer
308 views

Looking for solution to add double quotes in field which has commas in SSIS

I have a field called something like TYPE & the values are variant in it. I want the SSIS to export the result to CSV & while exporting, if it finds the , in column TYPE only that line/value ...
0
votes
1answer
66 views

SSIS Installation on Dedicated Server

We are planning on installing SSIS 2016 on a new dedicated ETL server. The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server). ...
2
votes
3answers
1k views

SSIS: Passing variable from child package to parent package

I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order. The first package to be called from Main_Flow is ...
2
votes
1answer
2k views

Problems Exporting Long Columns to Flat File Using SSIS

We have an SSIS package that is going to generate files to be consumed by Google Big Query. The files will be gzipped .tsv files. One of the requirements is that the file be UTF-8. We have set this ...
1
vote
1answer
45 views

SSIS - sp logs counters in debug mode but not from agent job

Within an SSIS project I have a number of usp's that I execute to store row counters, like extracted row count, inserted row count, etc., during an ETL process. My SSIS project is setup to run via an ...
2
votes
0answers
495 views

Specified initialization vector (IV) does not match the block size for this algorithm

I built an SSIS project on "test" server, and deployed the project across to "live" server. The project has property ProtectionLevel set to EncryptSensitiveWithPassword. Everything deployed ...
2
votes
3answers
565 views

Should I use SSIS to write programming logic?

I have come across a fairly complex system where the business logic is written in SSIS. Since SSIS is not able to perform a big portion of this logic, it is heavily dependent on C# script tasks to ...
2
votes
0answers
40 views

SSIS - dynamically merging a group of files

I'm trying to create an import package for our new mssql database and I'm having to merge a group of 20 files together to be fed into a table on the database. However not all of those 20 files will be ...
0
votes
1answer
491 views

Source - ODBC is not compatible with this version of the DataFlow

After deploying an SSIS package from a development server to another environment, execution and validation of the package fail with the following error: Error: The version of #5) Source - ODBC - ...
0
votes
0answers
34 views

Parse file with non-standard layout into table

I have a file that I am getting from a vendor that I need to put into an SQL Server table. The data in the file looks like this: {"result":"success","ReqCtx_requestURI":"somethinghere","eventDate":"...
1
vote
1answer
2k views

Invalid object name 'SSISDB.catalog.customized_logging_levels'. (Microsoft SQL Server, Error: 208) [closed]

I am moving data and SSIS packages from SQL Server 2014 database server to SQL Server 2016 server. I began with building the new server and installing SQL Server 2016 Standard edition. Then I backed ...
1
vote
1answer
1k views

Error creating package, Failed to save package file, error 0x80040154 “Class not registered”

A few colleagues & I have installed the latest Visual Studio (SSDT) - BIDS. We are unable to create packages, in Solution Explorer right clicking on SSIS Packages > New SSIS Package results in ...
0
votes
1answer
608 views

Best way to automate the loading of data daily into google big query from SQL Server

Best way to load/copy/insert data from a SQL Server database into a table in Google Big Query on a daily schedule. I.E data from a table in a SQL Server database to a table in Google Big Query I ...
1
vote
0answers
2k views

SSIS execution timeout expired

Recently I started to get this error from time to time on my nightly SSIS jobs: Message Executed as user: "my user name". Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for ...
2
votes
1answer
1k views

Register dll to use in SSIS Script Component

I've been asked to register an existing dll file so that it can be referenced in an SSIS script component. I have a dim and distant memory of doing this using gacutil.exe so that was my initial go to....
2
votes
3answers
741 views

SSISDB AlwaysOn Support in 2016 - SSIS Failover Monitor Job fails on non-readable secondaries

Setting up SSISDB with the new AlwaysOn support in SQL 2016 Enterprise. This creates two jobs, one of which is the SSIS Failover Monitor Job. The secondary is not readable. The code for this job is: ...
1
vote
0answers
3k views

Fault Finding SSIS “Unexpected Termination” - SQL Server 2016

I have been getting an "Unexpected Termination" error from time to time on a bunch of SSIS jobs that I am running on a SQL Server 2016 instance (Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0....
3
votes
1answer
1k views

SSIS Error: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher [closed]

SQL Server 2016 SP1 Enterprise Edition Data Engine (with SQL Agent) installed on an FCI called inst1. SSIS 2016 SP1 Enterprise Edition installed on comp2. (comp2 is not in the inst1 cluster) ...
1
vote
1answer
534 views

SSIS database source imports as BLOB incorrectly

Friends, I am a beginner user to SSIS (Microsoft SQL Server Integration Services). While I have experience coding in other languages (Python, MATLAB, R, and others), I have never experienced a ...
2
votes
0answers
214 views

SSIS upsert from CSV File Source

I'm trying to determine the most effect way to do an upsert in SSIS with CSV source 100+ columns 1 x CSV Source (loop through multiple CSVs same structure 100 + columns) 1 x Staging Table Destination ...
1
vote
1answer
667 views

SSIS/SSAS 2016 intermittent fault: An error was encountered in the transport layer

I have an intermittent fault in my SSIS ETL process when I try and process my OLAP cube. There are no error messages in the SQL log, and there's nothing else significant running at the time. The ...