Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
1,064
questions
0
votes
0
answers
11
views
SSIS Package with "Analysis Services Processing Task" become Corrupted in MSDB
Working on upgrading from SQL Server 2017 to SQL Server 2019. When using a desktop or another server running SQL Server 2017, SSMS v18.12.1 is used to import SSIS packages into MSDB on a remote server ...
- 1
0
votes
0
answers
6
views
Exporting/Importing maintenance plan dstx with correct server connection
I'm trying to improve what is currently a manual process to create a bunch of maintenance plans with a new SQL server instance (identical database). I'm importing/exporting with dtutil since these ...
- 155
0
votes
1
answer
20
views
SQL Server user running SSIS package getting EXECUTE permissions error when they have that permission
We have a user running an SSIS package from Visual Studio.
The step that is failing is it is trying to run a stored proc but the error within Visual Studio is "The EXECUTE permission was denied ...
- 101
0
votes
0
answers
20
views
SSIS Update/overwrite only 1 row in my table
I want to only have 1 row in my table.
I am populating a row from Server1 to Server2 on a single table using SSSIS. I am updating Execution End time that I get from a table in server1 to a table in ...
0
votes
0
answers
18
views
Running ssis packages from sql server agent jobs with setting 'do not save sensitive data'
I have a simple SSIS package configured as 'do not save sensitive data'. The package itself uses 'windows authentication' to connect to sql server source and destination databases.
My question is, ...
- 23
0
votes
0
answers
17
views
Check Query & Populate Another Table
I want to write a query that checks a table that has an ETL execution information specifically the Execution End Time (there's a column for that) and go ahead and populate the Execution End Time into ...
0
votes
0
answers
14
views
SSIS Package executes succesfully but the package doesn't write data
I am working on an SSIS project where I have two packages. One package gets executed just fine when I execute it whether on Visual Studio or through the Integration Services Catalog using SSMS. But ...
0
votes
0
answers
12
views
Looking for step by step instructions on how to set up Microsoft Connector for Oracle
I am trying to use SSIS to extract data from an Oracle database. My environment is:
SQL Server 2019 Developer Edition
Visual Studio Community 2019 CU 16
SQL Server Integration Services 16.0.845.0
SQL ...
0
votes
0
answers
11
views
Unable to convert Unicode string to DT_Image in SSIS
I have a very basic pipeline: I would like to save hyperlinks from an excel file source into an SQL database as a varbinary. The issue arises when I try to convert the corresponding column from the ...
0
votes
1
answer
35
views
Better way to disconnect a group of users before the start of SQL data Load from Oracle
We have a bunch of users, AD groups as well as SQL IDs accessing the DBs from time to time.
During an important Data load (SSIS package) getting Data from Oracle to MSSQL, this load sometimes gets ...
0
votes
0
answers
25
views
Is the DMK restore step optional when restoring the ssisdb onto a server that has the same service master key (SMK)?
Link: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16#to-restore-the-ssis-database
When restoring an ssis db in the following scenarios, is the ...
- 2,872
0
votes
0
answers
14
views
On a fresh install (post OS drive crash), does the SSISDB database master key or initial password matter when same master db/sql svc account is used?
My sql server OS drive has crashed so I'm rebuilding sql server on a fresh install. The crashed machine had ssis and I don't have a backup of the SSISDB database master key or the initial password ...
- 2,872
0
votes
0
answers
17
views
Executing SSIS in Powershell
I am trying to execute an SSIS package from Powershell where th,e package has several configured environment references.
$null = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer....
- 101
0
votes
0
answers
22
views
SSIS package part breaks whole SSIS solution due to .designer files
I have troubles with usage of Package Parts in SSIS.
My environment consists of:
VisualStudio 2019 16.11.18
Add-on SSIS 4.1 to VisualStudio
Windows 10 21H2
SQL 2019 CU16 - Standard edition
The ...
- 105
0
votes
0
answers
29
views
SSIS package fails sometimes and works fine
We have a scheduled SQL Job which runs the SSIS package and process the files (Hourly Job, and 24 execution's daily). From past few weeks we are seeing minimum 2 failures in a day randomly, post ...
3
votes
0
answers
23
views
Automatically pass through Sort and Merge Join transforms without needing to manually tick lots of boxes
We have many SSIS packages that sort incoming data, and merge-join to other tables from other systems.
Some of these packages may have 10 or so joins (each preceded by a sort on the join fields).
In ...
- 156
0
votes
0
answers
63
views
SSIS ForEach Loop Container Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)"
I have done quite a bit of research on this and nothing has helped so far.
I have a multi-tab Excel file I need to load into a sql staging table. I created a new project/package in SSIS to loop ...
- 1
1
vote
0
answers
206
views
SQL server job agent fails to execute SSIS package, the package runs fine manually
I have a job which executes two packages. Step 1 is a package which is no longer executing under the job agent although it was executing fine before the server reboot. The package executes fine ...
0
votes
1
answer
26
views
Finding and locally deleting deleted records in a million-record table
I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to ...
- 1
0
votes
0
answers
144
views
SSIS suddenly stopped logging package execution details in SSISDB
Brief Summary:
A few days ago in production, SSIS suddenly stopped logging in the internal.operation_messages_scaleout, internal.event_messages_scaleout, internal.event_message_context_scaleout, and ...
0
votes
1
answer
48
views
SSIS Package is writing corrupt data, but only when run as a Job
Our environment is the following:
SQL Server 2019 (15.0.4153.1) + SQL Integration Services (15.0.2000)
We have a DTSX Package that uses a OLE connection manager to connect to an SQL Server to read ...
- 126
0
votes
0
answers
110
views
Error when trying to setup the ssisdb on new server (An error occurred during service master key decryption)
I have an SQL server 2017 ssisdb backup and password that was used when creating the catalog.
I have restored db on a new server (same sql version) by following the steps given here: https://docs....
- 2,872
0
votes
0
answers
29
views
Why are SQL SSIS counters zero even though there are packages running on the server?
Why are SSIS performance counters: Buffer memory, Buffers in use, Buffers spooled returning 0 even though there are SSIS packages on the sql server; agent jobs configured to run these SSIS packages.
I ...
- 2,872
0
votes
1
answer
121
views
How to execute SSIS packages on another SQL server's hardware?
I have 20 ssis packages deployed on SQLSvr1 in the SSIS catalog.
They are invoked via the SQL agent jobs on this server.
Suppose I re-create these agent jobs on another SQL Server (SQLSvr2), then will ...
- 2,872
1
vote
0
answers
18
views
Does SSIS `Buffer memory` include `Flat buffer memory` and `Private buffer memory`?
I am looking at ssis perf mon counters to check for memory pressure from running ssis packages.
Does SSIS Buffer memory include Flat buffer memory and Private buffer memory?
- 2,872
1
vote
0
answers
29
views
SSIS in Azure: operation_messages_scaleout event_messages_scaleout tables not populating
We are running SSIS packages in AZure. I am finding that for some reason we have stopped populating the SSISDB tables (operation_messages_scaleout, event_messages_scaleout). It does appear that the ...
- 11
1
vote
1
answer
149
views
Run MSSQL stored procedures containing SSIS packages using Windows Authentication from another computer
I am new to MSSQL. I have a question about executing MSSQL Stored Procedure.
Let me briefly talk about my development environment first:
PC400 (Computer/Server A), where SQL Server 2012 is installed
...
- 11
0
votes
1
answer
644
views
What is the recommended approach to migrate SSIS and related packages from sql server 2016 to sql server 2019?
I have SSIS packages in msdb database as well as in the ssis catalog (ssis db).
What is the recommended approach to migrate SSIS and related packages from sql server 2016 to sql server 2019?
For ...
- 2,872
0
votes
0
answers
51
views
SSIS packages who contains ('Power Query Source') can not be called by SQL Agent
I have ssis package who use a power query source where I introduced a power query in order to get clear data from a text file and then used a 'OLE DB Destination' to copy the data to a new table in my ...
- 13
0
votes
0
answers
45
views
SSIS Deployment The locale identifier (LCID) 14348 is not supported by SQL Server
When trying to deploy a SSIS package on my local machine I get the following error message
The locale identifier (LCID) 14348 is not supported by SQL Server
And ideas ?
0
votes
0
answers
46
views
Fire a TRIGGER when an SSIS package from the SISSDB catalog starts
I would like to know if there is any way to fire a TRIGGER when an SSIS package from the SISSDB catalog starts?
I need that when a package starts execution, the trigger inserts some information in ...
- 51
2
votes
2
answers
197
views
What is the recommended way to install ssis in a sql server fci configuration?
I am planning a sql server installation with fci.
There will be 2 sql server 2019 instances in active/passive mode; they share the same SAN.
This question is about ssis.
Link: https://docs.microsoft....
- 2,872
-1
votes
2
answers
546
views
SQL Server Agent not running SSIS package
I have followed many articles to create an SSIS pipeline package, deploy it and schedule it.
I'm trying to transfer data from two remote servers, an OLEDB driver that connects to a SQL Server Database ...
-2
votes
1
answer
70
views
SSIS Duplicate Entry for key PRIMARY
I'm creating a simple Pipeline from a SQL Server Database to a MySQL Database, the issue is the pipeline won't work just once, but the more data is added to the first added should also be added in the ...
0
votes
1
answer
170
views
SSIS 2017- Insert Update process not working
I am creating a package to validate data. I am using a lookup object, however, it is not inserting new records. It only updates records and sometimes creates duplicate records.
the original data ...
0
votes
0
answers
41
views
Can a SSMS job which runs an SSIS package be resource limited within a virtual server?
I have a SQL Agent job which runs weekly and executes an SSIS package. The job takes over two hours to complete and in that time it consumes over 90% of the server CPU, this means the replication ...
0
votes
0
answers
173
views
SSIS and SQL Agent job impersonation
I currently have a new SSIS machine. SSIS is used to automate maintenance task such as backups and restores of other servers on the network. We have over 600 servers in our line of business and each ...
- 135
2
votes
0
answers
119
views
SSIS: How to export more than 4000 characters to Excel
I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors
Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
- 194
0
votes
1
answer
59
views
MSSQL Server 2019 SSIS installation
Currently our DBA team is tasked with supporting SSIS however we would like to separate the SSIS service from the actual database engine. The reason is to be able to have different teams responsible ...
- 135
0
votes
1
answer
103
views
SQL Agent Job Import Data
I'm having a problem with importing data. I created a package in SSIS and a connection between SSIS and sql server. It all works fine outside SQL Server but I wanted to make a 'Job' SQL Server Agent, ...
0
votes
1
answer
88
views
SSIS detecting sequential duplicates
SSIS/Sql Server/Visual Studio 2019.
I've got a big csv file that has a lot of iffy data. One thing I wanted to do was at least try to recognize sequential lines that are the same and kick the ...
- 307
2
votes
1
answer
284
views
SSIS csv import going slowly
I'm new to SSIS and I'm starting with 2019.
I'm getting a csv with 330 million rows and some of the data is dubious. Some rows I can identify as bad based on a single column value. Other issues I ...
- 307
0
votes
2
answers
74
views
From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?
I'm working in a company that has two legacy data warehouses, which have evolved to unmaintainable monoliths throughout the time. Therefore, they are in dire need of re-form.
I'm investigating a ...
- 103
1
vote
1
answer
1k
views
SSIS package error based on database collation
I have this old SSIS package I was using to load utf8-encoded csv files into a database. A very small percentage of the data had unicode characters, and people didn't particularly care if we dropped ...
- 307
0
votes
1
answer
239
views
Inserting in db is extremely slow - SSIS OLE DB Destination
I have been experienced that my DB is experiencing a lot of performance issues and after some time I have observer that an SSIS jobs takes extremely long time to inserting new rows through SSIS OLE DB ...
- 13
0
votes
0
answers
69
views
Finding table name in encrypted SSIS package
I would like to find a table refence in SSIS package but if I check directly the package file, it is encrypted so I guess this is not possible, isn't it?
I tried to find it also on SQL Server agent db ...
- 13
0
votes
0
answers
23
views
SSIS - Attunity 5.0 Oracle destination running for hours yet row count shows data has arrived so why does the dest task look in a hung state?
Using SSDT v14
A simple package that extracts data from a SQL Server view and sends via Attunity to Oracle takes about 5 hours to run, of which 4 hours seems to be the Oracle destination task showing ...
- 257
-2
votes
1
answer
333
views
How to convert default Date format from MM/DD/YY to YYYY/MM/DD HH:MM:SS in SSIS packages?
I don't know what to write in Derived column expression.
Steps-
1.Source - Excel file
2.Derived column
3.Destination - OLE DB Destination
0
votes
0
answers
86
views
Issues connecting to postgresql DB using SSIS ODBC connector
I have an odd issue with ODBC connector: I built a very simple ssis package to read data from a Postgresql DB and then load the data into a sql server DB. To connect to the postgresql DB, i used ODBC ...
- 23
1
vote
1
answer
836
views
SSIS transforming numbers with decimal comma to decimal dot
I'm trying to create a very basic package that copies data from my SQL server into an Excel file.
The data that comes from the server comes with a decimal comma (e.g. 1234,56) - that is how I want it, ...
- 11