Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
0
votes
0answers
18 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
26 views
“Connection contains host name” - SSIS packages run locally but not on Azure
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
23 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
0answers
16 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
8 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
9 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
18 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
27 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
73 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
17 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
47 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
35 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
34 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
21 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
84 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
63 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
31 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
28 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
42 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
14 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
9 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
85 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
18 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
25 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
15 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
24 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
30 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
40 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
40 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
39 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
48 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
28 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 ...
3
votes
2answers
27 views
dtexec returns immediately without any messages
I'm trying to setup a command-line call to dtexec to validate packages, using the project model. I'm calling dtexec like this:
dtexec /val /proj my.ispac /pack my.dtsx /rep v
Though the real ...
0
votes
1answer
76 views
Oracle ODBC connection failure (using oracle's stock ODBC drivers)
I want to use an Oracle-ODBC connection in Visual Studio 2017/ SSIS as it's much faster than OLE DB during tests.
Problem:
I follow Oracle's steps to the letter.
Install instant client (v18, ...
2
votes
0answers
101 views
How do I do an ODBC connection to ORACLE from SSIS/ Visual studio?
Despite that OLE Db and ADO.NET are newer than ODBC potentially, I've heard that ODBC connections to Oracle are potentially faster within SSIS.
So --- I'm trying to set up a simple ODBC connection to ...
2
votes
1answer
38 views
SSIS best practice to allow easily swappable/ secure/ accessible Database Connections?
I come from Pentaho PDI.
There, I learned to have scripts load data to a [VariableDB].
That way, I can either initiate that VariableDB at runtime to "ProdDB" or even set the variable to different ...
2
votes
1answer
65 views
ETL, Production, Development — Should these be different servers? Instances?
You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly.
My predecessor left quite the mess regardless but I'm trying ...
1
vote
1answer
149 views
Find SSIS package which calls stored procedure
We are trying to catch errors into an Error Log Table, similar to below.
We have many Master SSIS packages, calling many stored procedures.
Following code will log into the Error Log Table. Is there ...
1
vote
1answer
54 views
Fail SSIS “Execute Process Task” if false is returned
I have an "Execute Process Task" that calls Powershell and checks if a folder is empty. I'm wanting the SSIS job to fail if $false is returned.
Here is the code
param ([string] $FolderLocation)
$...
0
votes
0answers
21 views
Move from SSIS to Azure
I have an ETL solution which we have been doing in conventional SSIS. The job of SSIS is just to create star schema from the application database to dimension and fact tables. There is very little ...
0
votes
0answers
34 views
Salesforce source SSIS Package error
Im extracting data from salesforce using COzyroc SSIS component. This package is scheduled as job in MSSQL server which runs on daily basis.
This job failes on alternate days. One day it is executed ...
1
vote
3answers
77 views
find table used in SSIS Package script [duplicate]
I have a number of SSIS Packages
I am trying to locate a table that is populated by one of the SSIS Package
is there an easy way to find in the project where database.dbo.tabledata has been used in ...
3
votes
1answer
90 views
Should SSIS packages and SQL database be on same server?
I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling.
So here's my question --- Should SSIS ...
0
votes
1answer
20 views
SSIS AND XLSM load time issue Visual Studio 2010
I am having the following issue of SSIS AND XLSM load time issue Visual Studio 2010
I want to speed this up
I am reading a .xlsm as my excel source
I have noticed its loading / reading every row of ...
4
votes
1answer
87 views
Reading 300% rows - Problematic execution plan
Whole queryplan and query: https://www.brentozar.com/pastetheplan/?id=BkgbANxN4
I am struggling with a query that uses a lot of time.
The obvious reason for this is wrong estimates of amount of rows....
-1
votes
1answer
139 views
SQL TO SSIS derived column month name and 1ST of month [closed]
I have the following as sql
SQL TO SSIS derived column month name and 1ST of month
DATENAME(MM,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) --month name
DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) --...
2
votes
1answer
59 views
MDW Data Collector Server Activity collection step 3 suddenly stopped working
For many weeks my data collections from my database instace have been uploading to my MDW server. Suddenly step 3 of the Server Activity Collector has stopped working giving the following error:
Job ...
0
votes
0answers
49 views
Integration Services Catalog folder permissions changed
Question: Do any of the SQL Server systems tables in either SSISDB or MSDB contain information that would allow me to discovered which users are making changes to folder permissions in the Integration ...
1
vote
2answers
42 views
Reverse SQL Server Listener?
The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.
We also have SSIS on the secondary replica of the HA ...
