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.
45 questions
0
votes
1
answer
182
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 ...
- 5
3
votes
1
answer
7k
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://...
- 143
1
vote
1
answer
704
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.
0
votes
1
answer
311
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 ....
- 1
0
votes
1
answer
147
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 ...
- 115
0
votes
2
answers
629
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 ...
- 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 ...
- 101
0
votes
1
answer
705
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?
- 507
0
votes
1
answer
145
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 ...
- 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 ...
- 101
0
votes
1
answer
632
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 ...
- 335
0
votes
1
answer
326
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] == "" ? ...
- 691
0
votes
1
answer
280
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 ...
- 281
1
vote
1
answer
650
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 ...
- 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 ...
- 16.6k
1
vote
1
answer
326
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 ...
- 691
1
vote
1
answer
142
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 ...
- 691
0
votes
1
answer
40
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 ...
- 21
2
votes
0
answers
71
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 ...
- 24.3k
1
vote
1
answer
943
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 ...
- 691
1
vote
2
answers
436
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, ...
- 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 ...
- 691
0
votes
1
answer
165
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 ...
- 135
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 ...
- 680
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 ...
- 1
1
vote
1
answer
734
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).
...
- 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 ...
- 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 ...
- 971
1
vote
1
answer
149
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 ...
- 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 ...
- 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 ...
2
votes
0
answers
113
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 ...
- 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 - ...
- 7,984
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":"...
- 95
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 ...
- 11
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 ...
- 201
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 ...
- 679
1
vote
0
answers
7k
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
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....
- 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:
...
- 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....
- 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)
...
- 925
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 ...
- 43
2
votes
2
answers
823
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
...
- 679
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 ...
- 3,243