We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [ssis]

SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.

Filter by
Sorted by
Tagged with
2
votes
0answers
25 views

Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC

I am facing a strange issue on SQL Server 2017 Enterprise edition running in AWS on EC2. When we are on the SQL server and we import an SSIS package using the locally installed SSMS connected to ...
2
votes
0answers
8 views

SSIS package deployment. Changing protection level takes a very long time

We are in the process of migrating our servers to a new data center. Currently we are on SQL Server 2014 and SSIS packages deploy in seconds. The new data center has several SQL server 2016 installs ...
0
votes
0answers
31 views

SQL Server replication or Integration Services [on hold]

Our team develop a web application Enterprise Resource Planning (ERP) and we install it in all the stores that we have. Which store has its own application and database to prevent the lack of ...
-1
votes
1answer
21 views

SSIS package not working [on hold]

Recently we moved few databases from SQL Server 2008 to 2012. Couple of SSIS package on SQL Server 2008 were dependent on it. So to update the packages so that they point to new DB Server, we ...
0
votes
0answers
25 views

Need to move ssis package from SQL server 2008 to 2012

We have to move ssis package from SQL server 2008 to 2012 on another server. Package on source is stored in msdb. What is the best way to move the packages to 2012?
1
vote
0answers
26 views

Create/Edit SSIS Derived Column Task From Text/Code for Large ETL

I have to insert a large UTF-8 encoded flat file to a 1252 encoded sql server. In order to do so, I am using SSIS to type cast each column. Is there a way, other than copy pasting through all 400 ...
0
votes
0answers
9 views

Authentication failure in parent child SSIS packages

I'm troubleshooting an issue for our developers. A web app using windows authentication calls a stored procedure that executes an SSIS package. The SSIS package uses an "Execute Package Task," to ...
1
vote
0answers
14 views

Revisit SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store

I am having the same issue as SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store even with SSIS built in VS 2019 with the ODBC Driver 17 for SQL ...
0
votes
0answers
27 views

SSIS: To run the package outside of SQL Server Data Tools you must install Integration services or higher

I try to launch a dtsx package from Cmd using "dtexec". The process begins and no error encounted. But at the end I have the error message SSIS: To run the package outside of SQL Server Data ...
1
vote
1answer
42 views

SQL Agent job for SSIS execution fails with “An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed.”

A SQL Agent job with a SQL Server Integration Services Package step fails with the following message: Executed as user: DOMAIN\SQLAGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility ...
0
votes
1answer
106 views

SQL Server: Description: The version of Destination - ***.csv is not compatible with this version of the DataFlow

I have downloaded SQL Server 2016 evaluation onto a Windows 10 laptop. I have imported some data into a new DB and new table. So far so good. I have exported the data to a CSV file and save this as ...
0
votes
0answers
31 views

How do I handle deadlocking on a Stored Procedure when running an SSIS package?

I am concurrently running (for example 25) the same SSIS package that retrieves data from a 3rd party platform. This data is then inserted into our Production database. In order to increase ...
0
votes
2answers
769 views

How can I resolve the error “Could not load file or assembly 'Microsoft.DataWarehouse.AS'” in SSIS?

I launched good old Visual Studio 2017 today to create a new Integration Services project/package. When attempting to add a new target destination (OLE DB connection), upon selecting the drop down ...
0
votes
0answers
53 views

Using Transactions To Rollback SSIS package

I'm at a loss with an SSIS package I inherited. It contains: 1 Script Task 3 Execute SQL tasks 5 Data flow tasks (each contains a number of merges, lookups, data inserts and other transformations) ...
0
votes
1answer
47 views

SQL Server Integration Services (SSIS) Package Failing with no error

I have some SSIS packages that are executed as steps in a Server Agent Job. The Steps often fail with no error and I have had to add retries. Now the jobs complete but I get the warning exclamation ...
4
votes
1answer
93 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
24 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
36 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
17 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
38 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
197 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
27 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
69 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
26 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
19 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
19 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
34 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
49 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
61 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
154 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
147 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
37 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
47 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
145 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
82 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
74 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
74 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
162 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
154 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
39 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
88 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
498 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
69 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
61 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
383 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
30 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
243 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
76 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
28 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 ...