Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
985
questions
0
votes
0answers
9 views
How can I sychronise an SQL db with DB2 db using SSIS?
For one of my new projects in 2021 I am looking for a way to synchronise an SQL database with a DB2 database.
I need the SQL to clean up the 'raw' data, make views and connect to it with PowerBI .
The ...
0
votes
1answer
16 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
69 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
41 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
0answers
20 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
54 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
11 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
31 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
27 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
46 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
16 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
62 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
62 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
43 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
50 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
23 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
33 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
34 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
6 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
19 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
18 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
40 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
95 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
34 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
63 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
175 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
46 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
175 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
35 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
40 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
108 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
25 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
57 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 ...
2
votes
1answer
79 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. ...
0
votes
0answers
212 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 ...
0
votes
1answer
26 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.
...
0
votes
3answers
296 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
1answer
227 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
vote
1answer
356 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
0answers
83 views
how to open SSIS 2016 with SSMS 18.5 or later?
I have just patched one of my sql server 2016 instances to the latest cumulative patch (at the moment CU 12 that gives you a version 13.0.5698.0 as you can see on the picture below).
I had a few ...
0
votes
2answers
164 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 ...
0
votes
0answers
199 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 ...
0
votes
0answers
15 views
What are the best practises to configure SSIS catalog and jobs on AlwaysOn [duplicate]
I have a 2 node Always On setup with shared nothing storage. If I want to run SSIS packages/jobs on any of the nodes(whichever is primary) in a seamless manner what is the best course of action?This ...
0
votes
1answer
40 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 & ...
0
votes
1answer
20 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 ...
0
votes
0answers
669 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 ...
0
votes
0answers
18 views
Custom class variable passing to foreach- SSIS
class object
public class formDb
{
public string InternalId { get; set; }
public string referenceNumber { get; set; }
public string formID { get; set; }
...
0
votes
0answers
310 views
SSIS code page / unicode conversion issue
We have a process that loads flat files into a staging table. The table has varchar(2000) columns, and the SSIS package is using code page 1252. We've noticed that some unicode characters are being ...
0
votes
0answers
14 views
SSIS Logging vs. SSISDB Catalog Reports
What is the useful functional difference between the SSIS package logging, and the SSISDB Catalog reporting (All Executions/Validations). I appears to be one is canned, the other allows customization? ...
0
votes
0answers
110 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 ...
