Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
1,060
questions
0
votes
1
answer
374
views
Issues deploying SSIS package to SQL Server in Azure AD Domain Services (AADDS) environment
We use Azure Active Directory (Azure AD) in my organisation, and we recently deployed Azure AD Domain Services AADDS) to handle a SQL Server 2019 we use internally.
My Azure AD domain is (fictitiously)...
- 691
1
vote
1
answer
321
views
Configure Connection String to Use Target Machine Credentials
I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one ...
- 93
1
vote
2
answers
67
views
Alternative to cross server query in SSIS
I need to do a cross-server query in SSIS and have been having a hard time figuring out how to do it. Here is what I am trying to achieve:
SELECT Name, Email
FROM Server1.dbo.table1
WHERE UniqueID IN (...
- 13
1
vote
2
answers
242
views
Create DB snapshot without specifying the FILENAME attribute
I create DB snapshot as one of the tasks in SSIS package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded FILENAME attribute.
CREATE DATABASE Sales_snap ON ...
- 143
1
vote
1
answer
619
views
(SQL Server /SSIS) Managing IS Catalog permissions on a shared server?
I'm hoping for some ideas on how you and your team manage this. I'm supporting a shared SQL Server that will have several different departments deploying their SSIS packages via the IS Catalog. I ...
- 163
2
votes
1
answer
1k
views
Possible Double-Hop issue. SSIS User changing to anonymous when making a remote query
I am attempting to execute a PowerShell script that uses Microsoft.SqlServer.Management.IntegrationServices in an Azure pipeline. The SSIS package is successfully invoked, however, the package fails ...
- 453
2
votes
1
answer
352
views
Installing SSIS package outside of SSIDB catalog
I'm trying to simplify deployment of a SSIS package to a remote server. At the moment, I have
Local Visual Studio SSDT and SSIS package that has 4 connection managers - DEV environment
Remote server ...
- 143
0
votes
1
answer
524
views
importing csv file with ragged right format and " text qualifier
I'm importing csv files in ssis that are in a ragged right format, I've been able to create the columns perfectly fine but for some of the data rows it has included a " text qualifier into the ...
0
votes
1
answer
1k
views
SSIS cannot deploy package to SQL server - An error occurred during decryption 15466
After recovering and re-importing a backup of a SQL Server 2019 instance none of my SSIS packages will deploy from Visual Studio.
Whenever I try and deploy I get the error
=============================...
- 101
1
vote
3
answers
115
views
I have a bunch of SQL databases. How should I put these into Azure? [closed]
I have a bunch of on-premise SQL databases that contain data related to HR. One database could be pure employee data, such as staff records, leaver records or recruitment records. Another database ...
- 113
0
votes
2
answers
545
views
convert date in string format to date datatype sql [duplicate]
I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the ...
1
vote
1
answer
308
views
SSIS installation on the same server as SQL Server
We currently have an SSIS installation on the same server as SQL Server. This SQL Server installation is strictly to run SQL Server Agent to schedule the jobs that execute the SSIS packages deployed ...
1
vote
0
answers
117
views
importing Chinese characters into MySQL via SSIS
I faced a problem when I try to import Chinese characters from Hive into MySQL via SSIS. The setting in SSIS is below
If I use the same setting to import into SQL server the data is correct. But when ...
- 11
1
vote
1
answer
745
views
issues updating records in ssis using lookup and oledb command
im working on an ssis project loading data between two sql azure databases that is updating matching records and inserting new records between them using a lookup and ole db command but I'm getting an ...
1
vote
2
answers
442
views
Optimizing syncing of large dataset between 2 servers with SSIS
I have two DBs on different servers, Server A and Server B. Both are SQL Servers. I need to keep second DB in sync with the first one. DB schema and tables are identical, but Server A periodically ...
- 143
0
votes
1
answer
247
views
SSIS Package Duration is big before it begins executing
hello i have this question.
From Visual Studio i have deployd package in Sql server and i am manualy executing Package.
BUt i have noticed that package need 6 sec to do all things but Duration is 8 ...
- 3
0
votes
0
answers
59
views
Failing to connect to Integration Service with SSMS on one VDI but on another it works fine
I have two VDIs with the exact same version of SSMS installed. On one, I can log into the standalone Integration Service of a remote server running SQL Server 2017, on the other I cannot.
Every ...
- 1
3
votes
1
answer
1k
views
SSISDB master key in an Availability Group
I activated Always On support for SSIS and everything works fine until a fail over operation happens. Turns out the secondary nodes have a different service master key than the primary node and after ...
1
vote
0
answers
79
views
SQL Graph DB Tables not supporting OLE DB Fast Load?
I try to use SSIS to load in SQL Server Graph DB an edge table, based on the documentation from MSFT it should behave like any normal table, if I use the OLE DB Destination with regular table load it ...
- 2,857
0
votes
2
answers
469
views
SQL Server Data Tools - Data Link Properties - Server Name Drop Down Empty
I'm using VS 2019 with the latest version of SQL Server Integration Services Projects plugin. I have 2 local SQL Server instances installed on the same machine which are up and running (including ...
- 727
0
votes
1
answer
311
views
Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work
Using SSMS, I have changed the SSIS Catalog Logging Level from Basic to Performance to a Custom Logging Level in a 2017 SQL Server Instance and then I run the report: "All Executions". It ...
- 33
2
votes
1
answer
91
views
Why when I use variables on SQL the Database Engine avoid the indexes?
I'm improving our SSIS process passing the variables through a declare and I found this:
Here you have the plan execution using variables brentozar.com/pastetheplan/?id=rkSxs7VPv for the following ...
1
vote
1
answer
277
views
Which server actually runs the SSIS package when SQL Agent and SSISDB are on 2 different servers
We have a SQL Server where we deployed the SSIS packages to its Integration Service Catalog (SSIS DB).
And we have another SQL Server where we scheduled SQL Agent to run those SSIS packages.
My ...
William Xu
0
votes
1
answer
273
views
How to get SSIS to stop waiting/processing at task above time limit?
So I have a couple SSIS packages that are run sequentially, scheduled through SQL Server Agent. Right now, I'm getting some errors where certain input data steps just wait indefinitely-- i.e access ...
- 113
0
votes
1
answer
35
views
SSAS- Dimension Attribute needs to be sorted
In one of my SSAS- cube one attribute(inset_date) which is Date datatype and format is yyyy-mm-dd, when I checked in excel pivots it is not coming in order.
I have changed the order by property from ...
0
votes
1
answer
203
views
What are the implications of creating an Integration Services Catalog (SSISDB) where msdb-deployed packages exist?
I have inherited a production 2016 SQL Server with SSIS installed, which has many SSIS packages deployed to msdb (mostly warehouse ETL workloads that run nightly).
I would like create a new ...
- 172
0
votes
2
answers
428
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 ...
- 348
0
votes
1
answer
148
views
SSIS Import .csv
I’m new to SSIS and I’ve got an existing SSIS task which is importing a CSV file that has some unusual formatting.
The CSV has formatting like this:
TaskID, TaskName
ABC,Test Entry ABCD,Test’s ...
- 103
0
votes
1
answer
577
views
Get SSIS to check if excel file is updated?
So I have a SSIS package that reads from an excel file and populates a Sql server database. It assumes that the file will be updated monthly, and consequently, the data pull to update it is also done ...
- 113
2
votes
2
answers
796
views
Consistent data in dimension and fact tables from multiple incrementally loaded staging tables
To create a data model for our data warehouse we use tooling supplied by the ERP vendor. This does probably matter due to the fact that is has it's limitations. We inherited this environment with a ...
- 495
1
vote
1
answer
609
views
Execute SSIS (package) in SSMS using T-SQL and return a value
Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I ...
- 11
1
vote
2
answers
943
views
SQL Server 2019 standard edition CDC with SSIS standard edition
The primary question is:
Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise ...
- 13
0
votes
1
answer
415
views
Copy SQL Server Maintenance Plan between instances on same server
I have multiple SQL Server 2019 instances on the same server. I've created and exported the first instance's maintenance plan, edited its exported DTSX file to change the instance and plan names to ...
- 103
0
votes
1
answer
347
views
VS 2019 to SQL 2019 deployment issue - SELECT permission was denied on the object 'server_principals' [duplicate]
Good day,
We are currently facing an issue with SSIS deployment from Visual Studio 2019 to SQL Server 2019. Using the Visual Studio "Integration Services Deployment Wizard" to deploy even a ...
- 1
1
vote
0
answers
440
views
I manage to connect to Azure Analysis Services from SSMS, but not from SSIS
I'm new to the Microsoft Server Suite.
I've downloaded SSMS and connected to Azure Analysis Services from it. I'm able to query my data using mdx without any problems.
However, I actually intend to ...
- 11
0
votes
2
answers
124
views
Does "allow remote connections to this server" in sql server impact SSIS packages?
I'm wondering if anyone has experience with this setting "allow remote connections to this server" in sql server. I am partly responsible for a few hundred sql servers which run SSIS in ...
0
votes
0
answers
144
views
Oracle source with SSIS 2008
I am trying to use an Oracle OleDb source in my SSIS package with no luck, every time I try to connect to the server I get the error:
ORA-12154 tns could not resolve the connect identifier ...
- 1
3
votes
1
answer
541
views
Microsoft SQL Server - Permission to create a Snapshot
I've developed a set of SSIS packages to load a data warehouse.
In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse. ...
- 33
0
votes
0
answers
1k
views
Creating blank/deleting rows form excel using SSIS
I have an .xlsx file I import data from nightly.
Once the import is complete I delete the tables(sheets/tabs) in the excel file using an Execute SQL Task so that the file is blank for the user but ...
- 123
0
votes
1
answer
170
views
Upload multiple files to SQL Server without access to the Server Box
I am trying to create a process that would bulk upload multiple csv files periodically to individual tables in SQL server. The issue I am having is those files are not located on the same server.
...
- 476
0
votes
3
answers
4k
views
SSIS Data Import from CSV to SQL Table Failing [closed]
I have been trying to import the data from CSV file to SQL Table. I have 7 columns which are:
Refresh Date - Db_Date
Report Date - DB_Date
Report Period - Four byte singled Int
Participated - Four ...
0
votes
1
answer
1k
views
Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination
I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using psqlodbc_12_01 driver downloaded from here
My SSIS package has a single data flow task which just gets data ...
- 1,403
1
vote
1
answer
2k
views
Unable to start SQL Server Integration Services 12.0 service (SQL Server 2014) after upgrade to SQL Server 2019 and patched to CU4
I upgraded a SQL Server 2014 Standard instance to SQL Server 2019 Standard (also required upgrading the OS from Windows Server 2012 R2 to Windows Server 2019), and then patched to CU4.
During the SQL ...
0
votes
2
answers
1k
views
In SQL Server Always On 2019 environment should SSRS/SSIS be on a separate server?
I am looking for some advice in setting up a new SQL Server 2019 Always On instance.
We have a 2 server cluster right now, moving to Always On with a similar two virtual server setup.
I want to ...
1
vote
0
answers
975
views
Deploy a SSIS package to remote SQL Server
I am trying to deploy a SSIS package to a remote SQL Server.
In the deployment wizard, when i want to create a new project with SQL Server Authentication, it gives me this error:
The operation ...
- 29
0
votes
1
answer
71
views
Should every job, SSIS project, SSRS have its own sql login
Apologies for asking an opinion type question, but I'd like to know what is best practice.
I have a bunch of sql servers and every job as well as every connection manager for every SSRS report & ...
- 123
0
votes
1
answer
119
views
Does visual studio (2015 or above) needs to be installed in a server in order to transfer/create SSIS packages there?
At work we have an old server and we're moving everything from there to our new server. We're a very small team, and one of my team-members says without visual studio 2015 in the new server he can not ...
- 1
0
votes
0
answers
3k
views
Installing Oracle 32 or 64 bit on a Windows Server 2019 to Import data into SQL server and use with Power BI
I am an Oracle noob and really struggling with this concept of 32 and 64 bit business with Oracle.
All I really want to do, is import data on a daily basis from an Oracle 12c DW into a local 2019 ...
- 1
0
votes
0
answers
667
views
SSIS OLEDB runs fine locally but fails when deployed and ran on server
I have an SSIS package that has a data flow task that runs fine when I run the package locally in SSDT 2017 but when I deploy the package to the SSISDB catalog and run it, I am getting data conversion ...
- 3,225
2
votes
1
answer
2k
views
SSIS - Setup OLEDB Connection to SQL Server Always On Listener
I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).
I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
- 523