Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
917
questions
4
votes
1answer
43 views
Importing Chinese characters via SSIS
I'm trying to import file that contains Chinese characters into a table. File encoding is Big5 (Traditional).
Here is a sample file like one that I need to import: https://www.pastiebin.com/...
0
votes
0answers
19 views
SSIS Export Text in Email
We are receiving emails from this SSIS package and I need to edit the text that is in The EMAIL and not the data exports themselves. I have checked the stored procedures that produce the report code, ...
0
votes
1answer
18 views
Merge Join SSIS Throwing Error Despite Sort and Matching Meta
I'm attempting to Merge Join two data sources. I ensured the metadata matched and that the sort key was present in the metadata, but it is still giving me the error:
Both inputs of the ...
0
votes
0answers
8 views
Cannot create or use an SSIS package template in SSDT 2017
ENV:
Using VS 2017 Professional/SSDT 2017 on Win10 pro.
Our company does NOT allow developers to have Admin Rights on their laptops so the ONLY way to create and use a template is to save it in the ...
0
votes
0answers
35 views
Dimension attribute with one-to-many relationship
I have a request for an attribute in a dimension that has a one-to-many relationship with the lower level of the dimension.
Here is the case:
Dimension: Employee
Attribute: Immatriculation
I have ...
0
votes
2answers
63 views
SSIS Slow Data Transfer between Oracle and SQL Server 2016
I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS.
It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the ...
0
votes
0answers
24 views
Data Loss issue while migrating data from Source(Oracle) to Data Warehouse Staging Database(SQL Server)
Project Overview
Loading data from Oracle Database to Datawarehouse Staging Database(SQL Server). Using ETL process we are loading data from Oracle to SQL Server. Staging Database in SQL server is an ...
0
votes
1answer
21 views
SQL Server job command line
I have created couple of jobs on SQL SERVER out of SSIS packages , i can see command line for some of them start with /DTS and for some /SQL , even though all jobs creates in same version and perform ...
0
votes
1answer
51 views
Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it
I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When ...
0
votes
0answers
15 views
Not able to migrate DTS package from SQL Server 2008 to 2012. Getting errored out
Requirement: We need to migrate SSIS/DTS package from SQL Server 2008 to another machine having SQL Server 2012 on it.
Steps performed so far:
1. First I exported the package from Source server to ...
0
votes
0answers
15 views
SSIS Package runs fine with a few rows but not with a bunch
I have an SSIS package that runs a Script task as its final step; it calls an API and waits for a response. The API writes files to disk, so it's easy to see if it's working; if files are getting ...
0
votes
0answers
13 views
Executing Project-deployed SSIS package Works in SSA job but not via SSMS Object Explorer
I'm configuring a SSIS project in the SSISDB repository of a SQL 2016 instance. I have experience setting up credentials and SSA proxies to run packages with Windows authentication via SSA job steps. ...
0
votes
1answer
22 views
Prod environment uses a different SSIS version to Dev when running a legacy SSIS package
I have a dev environment with SQL Server 2008 R2 and SSIS (10.0) installed.
I have a number of SSIS packages built with SQL Server 2008 R2 BIDS and these have been deployed to a Prod server running ...
0
votes
0answers
33 views
SSIS not able to run package in SSMS
It's very simple package, running fine when executed via visual studio. From the report I get:
Visual Studio 2019 Community
SQL2019CTP3.1
In the connection manager I am using windows authentication. ...
0
votes
1answer
39 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 ...
3
votes
0answers
74 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 ...
0
votes
0answers
105 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
33 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
73 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
67 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
52 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
31 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 ...
1
vote
0answers
78 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
49 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
34 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
53 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
209 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
33 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
39 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
174 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
29 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
146 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
56 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
21 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
60 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
33 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
184 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
21 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
53 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
242 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
125 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
48 views
Import CSV data into SQL using bulk insert with text qualifier
I am looking for a solution to my task. I am importing 800 000 records in CSV format into SQL server. The data is comma separated and the text qualifier is double quotes. But some data in columns ...
0
votes
1answer
532 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
91 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
117 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
32 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
155 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
30 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
18 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 ...
