Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
1,131
questions
0
votes
1
answer
13
views
SSIS project window turns white right at the beginning of debugging, status "Running", tricky to get the project back. How do I avoid this crash?
This takes up SSIS package with status running is not running - Stack Overflow.
SSIS project window turns white right at the beginning of debugging, status "Running":
Right click closing ...
0
votes
0
answers
29
views
SSIS Double-hop... how to solve?
I have what I assume to be a pretty standard setup:
ServerA running a web application as ServiceAccount1
ServerB running SSIS as ServiceAccount2.
ServerC hosting a network share that ServiceAccount1 ...
0
votes
0
answers
6
views
The debugger only stops at the breakpoints of the first Script Component. How do I debug more than one Script Component in an SSIS Data Flow Task?
This takes up How to debug a script component in SSIS which was asked for the 2008 version and is now outdated. That is why this question needs to be revived for the versions of nowadays.
I put a ...
1
vote
2
answers
77
views
DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table alive?
Links
This takes up:
Using temporary tables in SSIS flow fails - Stack Overflow
Is it possible to use a temp table in a data flow source?; answer is:
'No', since it cannot be passed without being ...
0
votes
0
answers
24
views
SSIS - Save Multiple Query Results to one CSV file
I have around 50 select statements, some with different column names, that I would really like to automate using SSIS. I'm still fairly a beginner with using SSIS. My normal process is to navigate to ...
0
votes
2
answers
58
views
How do I create and fill a temporary table with incoming data from a Data Source item without leaving the SSIS Data Flow Task C# Script Component?
I came to ask this question after coding in a script component for some time, testing mainly how to loop over columns of the Row object, see Looping Through Columns in SSIS Script Component - Stack ...
0
votes
0
answers
27
views
Can I redirect rows on error in SSIS if the error happens within an "if condition", not while writing the column value?
I know how I redirect rows on error or truncation. That works if you have an error while you insert a value into the column.
Truncation: Say you have a string of 10 characters and you insert it into ...
0
votes
1
answer
41
views
Move a file from local folder to One Drive (Cloud) by using SSIS Package
I tried to move the file from a local folder to OneDrive (cloud). So, I have to use the Premium File Transfer Task from KingswaySoft (Free Developer License). Then I created the SSIS Package and ran ...
0
votes
0
answers
25
views
What to pay attention when installing SSIS in a cluster?
I'm improving my previous questions with more info here.
we have a 2 node cluster environment and one of them died. we created a new node to replace it and added to the cluster ( no sql or ssis yet )....
0
votes
0
answers
13
views
FoxPro ODBC/OLEDB in SSIS result inconsistencies
I am using SSIS to extract data from a .dbf-based ERP system. In SSIS, I have the Visual FoxPro OLEDB provider as a source and SQL Server OLEDB provider as a destination. The data copies quickly from ...
0
votes
0
answers
32
views
How to retrieve permission information from SSISDB packages?
I'm currently working on a project where I need to retrieve permission information from packages stored in the SSISDB, but I haven't found a satisfying solution yet. My experience with SSIS is limited,...
0
votes
0
answers
21
views
SSIS package failing when trying to deploy : Assembly Error
I had a 2016 sql server and we needed to migrate to 2019 but in doing so we wanted to keep the same IP & hostname as it was the easier route for other apps that connect to this server to still be ...
0
votes
0
answers
27
views
SSIS Package hanging with two Constraints pointed at one Task: Can you have multiple constraints pointing to one Task?
In the example I pasted in the image, I have 5 Tasks. I don't think it's important what kind of Tasks I'm using--but I could be wrong, of course--because the main issue seems to be how the constraints ...
0
votes
0
answers
29
views
How to find SSIS package thats calling / Executing a stored procedure?
How to find SSIS package that's calling / Executing a stored procedure?
May I know a SQL query OR some other way to see which SSIS package is calling / executing a stored procedure?
Thanks,
Kumar
0
votes
1
answer
54
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 ...
0
votes
0
answers
159
views
Ado net source fail to acquire connection
I have a SSIS package package that connects with azure sql database through using ADO net connection manager with SQLclient data provider. Authentication method used is Integrated security. The SSIs ...
0
votes
0
answers
70
views
Connection Retry via OLE DB VS ADO.NET in SSIS
Problem: We want to achieve Connection resiliency in SSIS Packages(OLE DB vs ADO.NET Connection manager)
Environment Details:
Database: SQL Server 2016
SSIS Connection manager: OLEDB
OLEDB Provider : ...
1
vote
1
answer
156
views
Can SSIS read the results of a stored procedure and write them to CSV, without populating a table?
My current workflow is as follows:
A 'Execute SQL Task' task runs a stored procedure that populates a table.
After this, a 'Dataflow Task' task does the following:
2A. Read every row from this table ...
1
vote
1
answer
76
views
SSIS Execute Package task is slow to start running the first time
I'm migrating an SSIS package from SQL Server 2012 to a new 2022 server. The package uses the Execute Package task inside a loop to run another package in the project. I'm finding that the first ...
0
votes
0
answers
37
views
Migrate SSIS packages from MSDB to SSISDB in place
We have several server that are running SSIS packages stored in MSDB. I'd like to change that to use SSISDB instead. What's the easiest way to do this?
These are not pure SSIS servers, so I don't want ...
0
votes
0
answers
155
views
SSIS Error: "Metadata could not be determined because procedure uses temp table"
We have recently restored SSIS Packages and I'm facing this error whenever I try to execute a SSIS package:
Unable to determine metadata because « INSERT INTO #File_Results EXEC Master.dbo....
0
votes
0
answers
19
views
This operation can only be performed on output buffers - SSIS Kingswaysoft error
We have recently migrated to Visual Studio 2022 from Visual Studio 2014 , one of my package is failing in Visual Studio 2022 when trying to use the kingswaysoft data conversion tool stating "...
0
votes
0
answers
35
views
SSIS - "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied."
SSIS error:
Recently the following error "Access to the path '\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring.
The SSIS package is executed through SQL Agent using a Domain ...
0
votes
1
answer
81
views
How to access an Intersystems Cache database using SSIS dataflow task
We have been trying to ETL data from an Intersystems Cache database to an SQL Server 2019 database, using SSIS in VS 2019.
We have installed the drivers from Intersystems on the server, using their ...
0
votes
0
answers
40
views
Passing the file path and file name and getting data in SSIS using foreach loop container
Can I do the same without using Foreach loop file enumerator?
If I do the automation to pick file using foreach loop file enumerator i am facing errors like:
Error at Package1: The connection "{...
0
votes
0
answers
59
views
Database Engine Services and Integration Services failed during SQL Server 2022 installation
Please, what can i do concerning the issue?
0
votes
0
answers
71
views
SQL Server SSIS Data Flow Task: How to enable transactions between two machines while bypassing DTC, if one machine does not have DTC support?
A bit of a conundrum here; I'm trying to survey what are my best to worst-case options.
For a given SSIS package, I have a Data Flow Task which connects via OLE DB for both the Source and Destination ...
0
votes
0
answers
63
views
SSIS Npgsql Connection Authentication method not supported
After upgrading the source database, the previously working package does not execute and reports the following error. Does anyone know what and where to update in the SSIS server? I hope that it will ...
0
votes
1
answer
3k
views
The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine
I am using SQL 2019, SSISDB is installed. My customer creates an SSIS package using the import/export wizard to import an excell file, and that was successful only after I had to install the 32bit ...
0
votes
0
answers
28
views
Parameterized Connection Manager in SSISDB Job not using the set ODBC password
Regardless of the solution I find online I have been unable to get my SSMS Scheduled Job to successfully complete my ODBC-connected SSIS ETL Packages.
I have tried deploying the SSIS project/packages ...
1
vote
1
answer
59
views
Database name in oracle 12 connection parameters
I am currently working with MS SSIS to copy data from MSSQL to Oracle 12.
I cannot figure out how to make connection string to some test database.
I found sample connection string for oracle .net ...
0
votes
1
answer
71
views
Schedule a job in SQL Server between two days
How to schedule a job in SQL Server that starts on Wednesday afternoon and it should end on Thursday EOD and between this the SSIS package should execute for every one hour if any error occurs in ...
0
votes
0
answers
20
views
Download files from multiple FTP folders inside particular files in SSIS
I have an FTP server with a folder named CIV.2-130211067212254633.
Inside this folder, there are files with names like 066075254035232083135067074114053142221015071213.Upload.
...
0
votes
0
answers
15
views
SSIS - Retrieve variable value from controle flow (no using script task) to data flow
I'm facing an issue with a case where I need to retrieve variable value from controle fow to data flow.
The value of the variable in controle flow = 3 but in data flow = 0 as if the value was not ...
0
votes
0
answers
36
views
ignore_dup_key vs SSIS Lookup in a SQL Server Batch import
I have to do a Import of my live table (25M rows) to a new table with Identity column before I cutover.
I implemented two set of process. Batch import SP which will import records whose creation ...
0
votes
0
answers
281
views
Validate Excel File data source in SSIS package
I need to load a number of Excel files using SSIS package into a SQL Server table. I am able to successfully loop through and load my Excel files into my table I do not have any issue doing that, BUT ...
0
votes
1
answer
114
views
Installing SSIS, SSAS and SSRS on a separate Server
Please is there any benefit from installing SSIS, SSAS and SSRS on a separate server from the SQL Engine instance?
Apart from additional license implications, are there any other considerations for ...
2
votes
1
answer
97
views
Performant Loop or Alternatives
My current data set looks like following
[Table 1]
Key
Group
Amount
Sequence
1
1
50
1
2
1
60
2
3
1
70
3
4
1
90
4
5
2
120
1
I have a second table that records a "Rolling rank" of each ...
0
votes
1
answer
53
views
SSISDB rename for maintenance
We have an SSISDB database that has become too large and we wanted to do some maintenance on it. I've read that you shouldn't rename the SSISDB database as it would break links to existing packages, ...
0
votes
0
answers
147
views
SSIS Data Flow Task indicates it wrote x rows, and reported no errors, but none of the rows appear in destination
This package had worked in the past but, for whatever reason, hasn't been working as intended recently. The package has a script task that sets a file path variable to the file path for the most ...
0
votes
1
answer
469
views
Using SSIS Project Parameters in SQL Agent Jobs
I'm using SQL Server 2019 and attempting to set up project parameters for my SSIS projects. I've set up project-level parameters within the package itself and those parameters do appear in SSMS for me ...
0
votes
0
answers
104
views
SSIS Database Always on AG and Patching
I am after clarification, when we patch a SQL Server Always on AG environment that has the SSIS database in the AG we remove the SSIS database, patch failover, patch and add the SSIS DB back into the ...
0
votes
0
answers
400
views
MSSQL SSIS is disable but Scale Out Master service is still running. Can and how to disable this
I am trying to clean up all unnecessary services on our DB boxes, therefore have disabled most of the SQL related features like SSIS and reporting etc. However it appears a sql process for is still ...
0
votes
0
answers
312
views
Issues deploying SSRS related to permissions
I'm tryingto deploy a package (from a Visual studio 2017 ) and I'm receiving this error:
TITLE: SQL Server Integration Services
A .NET Framework error occurred during execution of user-defined
...
1
vote
1
answer
628
views
SSIS error when trying to run using Windows Authentication
I'm having issues running an SSIS package. There are several elements to this issue, and what I'm hoping for is the overall correct way to accomplish the goal.
This SSIS package imports from an Excel ...
3
votes
1
answer
5k
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://...
2
votes
2
answers
839
views
Security Considerations/Risks/Best Practices for Enabling CLR Integration (Assemblies) for SSIS Catalog
The major pre-requisite in utilizing SSIS Catalog is enabling CLR Integration. The instance in question is running SQL Server 2014. I'm looking for feedback and resources I can mine which specifically ...
1
vote
1
answer
339
views
Open Row insert- SQL
When we run a SSIS package to import data from excel , we get an initial error:
The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.
If the 64-bit driver is not installed, run the ...
0
votes
1
answer
515
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
284
views
Run SSIS Package but the package path will be decided runtime
I have to import 16 Excel files into 16 tables through agent. The agent will process the data further. I have created a SSIS package for each . I don't want to create 16 jobs to do same steps.
My idea ...