Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
905
questions
0
votes
1answer
20 views
Execute SSIS procedure from stored procedure using non-privileged SQL login
I have a web app running on a different server that uses a SQL based login (not a windows based login) to connect to the SQL server. That app is calling a stored procedure that is located in a ...
2
votes
0answers
29 views
SSIS Package failing to access UNC path it has permissions to
Trying to run an SSIS package that tries to access \\fileserver\share\file.txt, but the SSIS job returns:
"File System Task:ERROR: File or directory "\\fileserver\share\file.txt" represented by ...
-1
votes
2answers
30 views
SQL Procedure error [on hold]
I am having an issue with an sql query used in job automation
The procedure inserts data from a source table(48 columns) to destination table(49 columns where the 49th/last column is NOT in ...
0
votes
0answers
44 views
how i can solve this problem in sql server management studio 2016 [closed]
Copying to [dbo].[Sheet1$]
Error Messages
Error 0xc020901c: Data Flow Task 1: There was an error with Source - Sheet1$.Outputs[Excel Source Output].Columns[AA_change] on Source - Sheet1$.Outputs[...
0
votes
0answers
51 views
SSIS Scale-out Manager don´t show status of any Workers
Why does my SSIS Scale-out manager on my master server not show the status of any workers?
My infrastructure is:
Master server M1 with Mssql engine (there is SSISDB) SQL2017 EE CU15, SSIS master ...
0
votes
1answer
34 views
upgrade MSDB only?
good morning.
we currently are running on SQL server 2008 r2 and going to Sql server 2016. we are told we cannot do an in place upgrade and will have to restore all SSIS and jobs manually (script, ...
0
votes
1answer
30 views
SSIS job — how to create stored procedure that repeats SSIS jobs?
Hey I realize I can probably create a modular parent package that repeats SSIS jobs in Visual Studio itself .... but I know you can execute SSIS packages using a stored procedure in SSMS -- 'exec ...
2
votes
1answer
46 views
SSIS packages running forever without completing. OLEDB Source query suspended
We have several packages that will intermittently run until they are killed.
When looking at the Activity Monitor we can see the source query for one of the data flows is in a suspended state with ...
2
votes
2answers
60 views
Where can SSISDB actually reside?
Trying to wrap my head around what's going on under the hood when integration services is installed, and how that relates to SQL instances on the same machine.
Currently we are aiming to have a full ...
1
vote
1answer
37 views
How to prevent concurrency issues in a ssis package?
Have a SQL Server 2017 Server.
Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag.
In the package there is a data flow that runs a stored proc (please see below)...
1
vote
0answers
12 views
SSIS won't connect to Oracle issue after ODAC driver un-install & re-install
I installed the ODTwithODAC1120320_32bit drivers and client and could successfully connect SSIS to an Oracle database.
I noticed that the installation had defaulted to the E: drive which is where the ...
0
votes
0answers
22 views
SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store
I'm trying to use SSIS (Visual Studio 2017) to insert data into an Azure SQL table with a column that's encrypted via Always Encrypted, and a column master key stored in Azure Key Vault, but I keep ...
0
votes
1answer
25 views
Can I upgrade SSIS 2008 packages directly to SSIS 2017 SSISDB?
I'm planning a mifgration of a SQL Server 2008 instance running the DB engine and SSIS.
The SSIS is deployed to the file system.
Am I able to copy those SSIS packages to a directory on my new ...
1
vote
0answers
29 views
After reboot SSIS cannot connect to MS Access dbs
We rebooted our sql server 2016 machine. SSIS jobs that have been running fine are now unable to connect to access dbs. it throws the error:
"XXXXX:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE ...
2
votes
1answer
37 views
How to automate the changes of parameters in ssis package run by a job?
I have a job that runs a package with specific parameters as you can see on the picture below:
I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27.
I need ...
0
votes
2answers
54 views
SSIS Create New Folders On The Fly When Writing To A Flat File Connection
I have an SSIS package that is meant to write to multiple files, which are stored like
\\server\MainFolder\DynamicField1\DynamicField2\File.csv
where the subfolders DynamicField1 and DynamicField2 ...
1
vote
1answer
24 views
Directory Verification with For Each Loop in SSIS
How can we check if a directory is valid or not inside for each loop . I am having multiple files at some location and i want to load that file but before this i
want to check if the directory is ...
0
votes
0answers
31 views
[SSISDB].[internal].[cleanup_server_retention_window] Foreign key Violation
I have setup a job to cleanup SSISDB with predefined storeprocedure "[SSISDB].[internal].[cleanup_server_retention_window]" but sometimes i get following error. After that i have to do manual cleanup ...
0
votes
0answers
94 views
SSIS packages run locally but not on Azure (possible host name issue?)
I am trying to make SSIS packages using Visual Studio 19 that import data from tables in a remote Azure Managed Instance SQL database to a Microsoft Azure SQL DB database, aiming to schedule these to ...
2
votes
0answers
25 views
Paralleled SSIS package flatlining at 20% CPU
I'm a DBA and my developers are wanting to aggregate data as quickly as possible. They have run the 3 main executions within SSIS to run in parallel but rather than make the CPU hurt for a shorter ...
0
votes
1answer
67 views
SSIS data flow gets stuck reading table with no errors
When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
0
votes
0answers
29 views
SSIS/SSDT packages no longer showing in Visual Studio after deploying
So earlier today I had deployed my project including SSIS packages to my SQL Server. They successfully showed up ( but were giving me a duplicate key error). So I decided to save the project and end ...
0
votes
0answers
15 views
SSDT solution missing packages after deploying project to SQL Server [duplicate]
Earlier today I deployed my project and SSIS packages to my SQL Server, the succesfully showed up in the SSIDB catalog. I encountered an error about a key that I had intended to fix when I got home to ...
2
votes
1answer
38 views
SSIS Merge Join: Delete does not work
I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere).
Merge Join:
Inserting and Updating works fine,...
1
vote
2answers
31 views
SSIS connection used based on passed value
I have an SSIS package that populates a reporting warehouse from a live and test environment.
Currently the package has each step duplicated as the source connection changes between live and test. ...
3
votes
0answers
133 views
Import Oracle unicode data with OLE DB Source and destination in SSIS 2015
My issue is identical to this post on mssqltips.com, however my issue is importing from Oracle, not Excel.
I get all the way to the bottom where I need to map the output columns from my data ...
1
vote
1answer
18 views
Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages?
I am building an Azure SQL DB database and to do this I will have to regularly (weekly-monthly) pull data from various other servers. I am designing SSIS packages to do this and whilst most of the ...
4
votes
1answer
49 views
SSIS: Move data between servers - will data travel through my workstation?
I have just installed the Microsoft SQL Server Data Tools for Visual Studio 2017 on my workstation and created a simple SSIS package (.dtsx) which basically copies data from one MSSQL server to ...
0
votes
0answers
133 views
Attunity Connectors for SSDT are not showing in VS 2017 (SSIS Toolbox)
I'm trying to add a connection manager using the new Attunity drivers I downloaded but I'm not seeing it as an option.
This Microsoft page does not list the latest SSDT version (15.x) as one of the ...
1
vote
0answers
88 views
SSIS 2017 KerberosSupport - Double Hop Issue
I am having an issue with what appears to be a kerberos double hop issue with SSIS 2017.
The setup consists of a backend database cluster servers (DBSERVER01\02) an Application\SSIS server (...
0
votes
0answers
26 views
Import CSV data into SQL using bulk insert with text qulalifier
I am looking for a solution to my task. I am importing 8 lakhs of records in CSV format into SQL server. The data is comma seperated and the text qualifier is double quotes. But some data in columns ...
0
votes
1answer
304 views
How to change “deployment target version” on SSIS 2019?
I am experiencing the following errors when using the "Transfer SQL Server Objects" task in SSIS on Visual Studio 2019:
An exception occurred in SMO
Value cannot be null. Parameter name: server
...
2
votes
1answer
73 views
Database Engine and SSISDB architecture advice
I'm seeking some architecture advice involving the database engine and Integration Services components of SQL Server 2017. We have an existing SQL Server environment which is due for an upgrade and ...
2
votes
0answers
88 views
22046 “Impersonation error” running SQL Server Agent job
(New to SSIS. None of the similar questions mention this particular impersonation error.)
I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. ...
0
votes
1answer
30 views
Populate monthly table from quaterly data
I have below excel file that I am loading to ssis. The problem is that the granualrity for the date dimension is by month. Is there a way to load the data so that for Jan, Feb & Mar the value is -...
0
votes
1answer
106 views
SSIS script task - Execute Dataflow task based on variable value
I have been trying to create a package which executes a couple of dataflow tasks based on the available value in one of the variables. How can I achieve this?
The sample scenario: Variable param may ...
0
votes
0answers
24 views
Execute SQL Task timeout setting is being ignored for Redshift ODBC Connection
When I run an SSIS SQL Task (VS 2017) against an ODBC Amazon Redshift (32 bit) connection, the timeout setting is not being respected and the Task/SQL continues to run endlessly.
0
votes
0answers
12 views
SSIS setup and loading bulk CSVs into SQL
I'm trying to setup a SSIS to load bulk CSV files into sql server, but i'm getting the following error. Can you please help with the resolution.
Warning: 0x80019002 at Package: SSIS Warning Code ...
3
votes
1answer
214 views
SSIS : Null value for date keeps failing
I have a sequence container and in that sequence container I have select that is passing through data into a foreach loop container. The problem arises with one of the columns that's a date datatype. ...
0
votes
0answers
18 views
SSIS DB issues when trying to run Package from SSISDB to getch ADO directoty users list
I am working on task that involves using Dev server to fetch ADO directory user information. I am connecting through SQL script i Script task and passing my User authentication. This is working fine ...
0
votes
0answers
20 views
What are SSIS ,SSRS and DB engine SQL server 2008R2 to 2012 IN PLACE UPGRADE steps and cautions? [duplicate]
I am trying to find, SSIS ,SSRS and DB engine SQL server 2008R2 to 2012R2 IN PLACE UPGRADE steps and cautions ?
Thanks,
DL
0
votes
1answer
30 views
SSISDB Start and Stop time for each Package
I am trying to query the SSISDB Project Catalog to review the Start and Stop time for each SSIS package.
Is there any native query to conduct this?
I am checking [internal].[executions], SSISDB....
0
votes
0answers
42 views
SSIS - How to parameterize the connection string for a DTSX Package at run time
I have a very simple dtsx package where I created a package parameter called pSourceFilePath and I set its connection string to a path on a local disk. I "parameterized" the connection string for the ...
0
votes
0answers
25 views
SQL Server Integration System (SSIS): looking for a way to iterate a list of Oracle databases
I am developing a package in Visual Studio in order to bring some info coming from different Oracle databases in a unique SQL Server database. Each Oracle database is a clone of the other one, the ...
1
vote
1answer
34 views
SSIS comparison load between new, updated and deleted records and populate a staging database
So far,
My control flow looks like:
Execute SQL Task
- This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be ...
0
votes
1answer
44 views
What do I need to develop an integration service to extract data from an Oracle DB to a SQL Server DB using Visual Studio 2015? [closed]
I have to develop an ETL to extract some data from an Oracle DB 11g to a SQL Server 2017 DB. I am pretty new at all this technology so I would like some advices. The strongest problem so far is to ...
2
votes
1answer
44 views
SSIS:How retrieve and insert an XML Column
I'm trying to Insert two columns from one table onto another. The first column is an int but the second column is an XML. I'm having issues inserting the xml column
In first SQL Task I'm doing a ...
0
votes
1answer
40 views
Find “Mystery processes” on a MS SQL Sever environment? (SSIS, databases, etc) [closed]
I inherited a collection of Instances/ MS SQL Databases/ SSIS scripts on a server. I am now the interim "accidental DBA."
I thought I was familiar with everything there, but apparently not. It's all ...
0
votes
2answers
101 views
Add missing column to SSIS, data not extracted to staging table
I've inherited an environment that extracts data from a Dynamics AX 2012 ERP to a data warehouse for reporting. The original extraction set up by consultants did not include the proper date column ...
3
votes
0answers
31 views
Migrating Access Saved Import Specifications to SSIS Package
We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
