Skip to main content

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
1 answer
85 views

SSIS Variables as expressions to generate Start and end dates of a quarter with input year and month passed as variable

Can someone help me create SSIS expressions for variables to generate QuarterStart(DateTime) and QuarterEnd(DateTime) as a variable with year(int32) and quarter(int32) passed on as user variables. The ...
mk SQL's user avatar
  • 5
0 votes
0 answers
45 views

Passing the file path and file name and getting data in SSIS using foreach loop container

Can I do the same without using Foreach loop file enumerator? If I do the automation to pick file using foreach loop file enumerator i am facing errors like: Error at Package1: The connection "{...
S_T's user avatar
  • 1
0 votes
0 answers
27 views

Download files from multiple FTP folders inside particular files in SSIS

I have an FTP server with a folder named CIV.2-130211067212254633. Inside this folder, there are files with names like 066075254035232083135067074114053142221015071213.Upload. ...
karthik sanapala's user avatar
3 votes
1 answer
6k views

Unable to install msoledbsql despite installing the visual c++ redistributable prerequisite

I am trying to use Visual Studio 2022 to successfully load and build an SSIS package that targets SQL Server 2016. Documentation suggests that this is indeed supported. Per the online docs (https://...
tnk479's user avatar
  • 143
0 votes
1 answer
576 views

How to create Destination table name with Getdate in SSIS OLEDB destination

How to create Destination table name with Getdate in ssis destination task. I have one table like Test..but i want to create new table like Test_09Feb2023 in OLEDB destination.
karthik sanapala's user avatar
0 votes
1 answer
259 views

SSIS package loses .NET provider password even though EncryptSensitiveWithPassword is enabled

I have a new SSIS project developed in VS and intended to run on a SQL Server 2016 instance. The project has the ProtectionLevel of EncryptSensitiveWithPassword set. A package has a connection to a ....
renMike's user avatar
0 votes
1 answer
131 views

SSIS debuging tool in SSMS 2016

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...
Alloylo's user avatar
  • 115
0 votes
2 answers
565 views

SSIS package randomly fails with "WriteToServer requires an open and available Connection" error

Our SSIS package randomly fails with the following error message: An exception has occurred during data insertion, the message returned from the provider is: WriteToServer requires an open and ...
GrzegorzO's user avatar
  • 338
0 votes
1 answer
2k views

How to make variable update for each instance of for-each loop?

I have a for-each loop that goes through a file directory. These files are transaction information from different clients. When there is an error with a file (i.e. can't be processed), the Event ...
Dom Vito's user avatar
  • 101
0 votes
1 answer
671 views

Granting access to users to run specific SQL jobs that runs SSIS packages [duplicate]

We need to grant a couple of users access to run only specific SQL jobs that runs SSIS packages. Is there a solution that can be used to accomplish this?
Feivel's user avatar
  • 507
0 votes
1 answer
140 views

Design Sql Server physical configuration from existing VM

We have 4 virtual machines that we used to test our BI platform (PoC), the final dimensions are: SSIS (16 vcpu), SSAS (8 vCPU), SSRS (4 VCPU) et Data Engine (24 vCPU) What are the recommendations ...
Benn's user avatar
  • 1
0 votes
1 answer
1k views

Agent Job: Execute T-SQL on different server?

While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link). We were moving our SSIS packages and agent jobs on ETL ...
Ankit Mahajan's user avatar
0 votes
1 answer
596 views

SSIS Package Failing since Database failed over to Secondary (Basic Availability Group)

Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to ...
Ali's user avatar
  • 305
0 votes
1 answer
312 views

SSIS: Conditional operator in expression builder ignores previous text

Consider the following simple expression in SQL Server SSIS, where a variable is built with the following expression containing a conditional operator: "Preamble " + @[$Package::myParameter] == "" ? ...
pmdci's user avatar
  • 691
0 votes
1 answer
274 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 ...
vikrant rana's user avatar
1 vote
1 answer
573 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 ...
Kulstad's user avatar
  • 55
2 votes
1 answer
2k 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 ...
Marcello Miorelli's user avatar
1 vote
1 answer
269 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 ...
pmdci's user avatar
  • 691
1 vote
1 answer
133 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 ...
pmdci's user avatar
  • 691
0 votes
1 answer
39 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 ...
Trent Adams's user avatar
2 votes
0 answers
61 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 ...
Kenneth Fisher's user avatar
1 vote
1 answer
893 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 ...
pmdci's user avatar
  • 691
1 vote
2 answers
354 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, ...
Alex's user avatar
  • 11
0 votes
1 answer
2k 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 ...
pmdci's user avatar
  • 691
0 votes
1 answer
159 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 ...
Steve_Malcolm's user avatar
1 vote
1 answer
2k 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 ...
Eric Swiggum's user avatar
0 votes
1 answer
3k 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 ...
Atulya's user avatar
  • 1
1 vote
1 answer
691 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). ...
Kevin's user avatar
  • 533
3 votes
2 answers
7k 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 ...
pmdci's user avatar
  • 691
7 votes
1 answer
17k 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 ...
Alf47's user avatar
  • 961
1 vote
1 answer
139 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 ...
DimUser's user avatar
  • 382
3 votes
0 answers
1k 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 ...
DimUser's user avatar
  • 382
3 votes
3 answers
1k 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 ...
Hooman Bahreini's user avatar
2 votes
0 answers
99 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 ...
Exostrike's user avatar
  • 173
0 votes
1 answer
2k 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 - ...
Tony Hinkle's user avatar
  • 7,964
0 votes
0 answers
45 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":"...
Bill Hughes's user avatar
1 vote
1 answer
10k 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 ...
user114165's user avatar
1 vote
2 answers
5k 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 ...
Jeremy Thompson's user avatar
0 votes
1 answer
1k 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 ...
davey's user avatar
  • 689
1 vote
0 answers
6k 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 ...
Tautvydas Versockas's user avatar
2 votes
1 answer
4k 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....
Molenpad's user avatar
  • 1,784
3 votes
3 answers
3k 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: ...
Bendy22's user avatar
  • 89
1 vote
0 answers
7k 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....
Ash's user avatar
  • 131
3 votes
1 answer
2k 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) ...
SQL Hammer's user avatar
1 vote
1 answer
2k 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 ...
Jonathan's user avatar
2 votes
2 answers
699 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 ...
davey's user avatar
  • 689
1 vote
1 answer
2k 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 ...
Sir Swears-a-lot's user avatar