Questions tagged [ssis]

SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.

Filter by
Sorted by
Tagged with
1
vote
0answers
31 views

How do SSIS connection strings send passwords?

I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(...
0
votes
0answers
21 views

Why does DTExecUI.exe run fine but DTExec.exe gives me an error?

When I try to execute an SSIS package with DTExec.exe with a batch file or in the command prompt it gives me error code 0xC0040019 Data Flow Task 1 (SSIS.Pipeline) data flow objects cannot be loaded. ...
1
vote
1answer
161 views

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero. From the DBA I ...
2
votes
1answer
29 views

Annotations for SSIS

Forgive me if this is an inappropriate question for this forum (being DBA and all, I thought it may fit). I have an SSIS package that I've recently upgraded and moved from BIDS to VS 2019. It's a very ...
0
votes
1answer
24 views

SSIS convert date in mmddyyyy format to date format using SSIS expression

I'm receiving dates in a mmddyyyy format but want to convert it to the date format in sql/ssis yyyy-mm-dd, how can I do so using an SSIS expresion. example of dates I'm reciving are 03051978 but want ...
0
votes
0answers
19 views

I am getting below error intermittently while running ssis package through sql agent job for sql server 2012 can anyone help me?

I am getting below error intermittently while running ssis package through sql agent job for sql server 2012 ERROR:-"Argument “OFF” for option “checkpointing” is not valid Argument “OFF” for ...
3
votes
2answers
151 views

How do I fix the Code Page in SSIS Lookup Transformation to be 65001?

I have an SQL server 2019 with databases and tables all set with the collation Latin1_General_100_CI_AS_SC_UTF8. The relevant table has code and desc columns which are both of data type varchar. In ...
0
votes
0answers
25 views

SSIS - DMA doesn't support migration assessment to SSIS in local SQL Server 2019

we will migrate from SQL Server 2008 R2 to SQL Server 2019 in near future, we already run DMA to analyze database, however DMA 5.2 doesn't support migration assessment type for intergration services (...
0
votes
1answer
21 views

SSIS Deploy Error

I made a package in SSIS that convert from a DBF file to the SQL server. Well in visual studio it works perfectly, but after i deployed my project in sql server it doesn't work. See the images: ...
0
votes
2answers
44 views

Extract from DB2 to SQL Server - where is the data in between the two servers?

I am trying to understand what happens when I extract data from DB2 from a SSIS package and load it into SQL Server. My question is: where is the data when it is between DB2 and SQL Server? On my ...
0
votes
1answer
33 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)...
1
vote
1answer
131 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 ...
1
vote
2answers
23 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 (...
1
vote
2answers
36 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 ...
1
vote
1answer
16 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 ...
1
vote
1answer
113 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 ...
1
vote
1answer
22 views

Installing SSIS package outside of SSIDB catalog

I'm trying to simplify deployment of 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 ...
0
votes
1answer
29 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
1answer
106 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 =============================...
1
vote
3answers
73 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 ...
0
votes
2answers
44 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 ...
0
votes
0answers
35 views

In SSIS, how to force an open connection to to SQL Server while package is executing (hint: its not retainsameconnection=true)

We have an SSIS package with an OLE DB connection to a SQL Server database. The database restores every 15 min from log shipping unless a connection is open. When the SSIS package runs (takes ...
1
vote
1answer
69 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
0answers
14 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 ...
1
vote
1answer
92 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 ...
0
votes
2answers
30 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 ...
0
votes
1answer
48 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 ...
0
votes
0answers
17 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 ...
0
votes
0answers
117 views

SSIS Package Fails when run from sql agent

I have an SSIS package that is loaded onto the a SQL Server. The package fails when executed as a sql agent job, but the package runs fine when the user goes into the ssisdb catalog. The job is ...
3
votes
1answer
154 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
0answers
48 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 ...
0
votes
1answer
102 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 ...
0
votes
1answer
38 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 ...
2
votes
1answer
34 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
1answer
44 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 ...
0
votes
1answer
7 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 ...
0
votes
1answer
21 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
1answer
27 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 ...
0
votes
0answers
45 views

Use of tempdb for bulk upsert operation

We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which ...
0
votes
1answer
135 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 ...
0
votes
1answer
40 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 ...
0
votes
1answer
139 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 ...
3
votes
2answers
254 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 ...
1
vote
1answer
57 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 ...
1
vote
2answers
292 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 ...
0
votes
1answer
38 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 ...
0
votes
1answer
64 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
vote
0answers
164 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 ...
0
votes
2answers
34 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
0answers
72 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
2 3 4 5
20