Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
937
questions
1
vote
1answer
29 views
Trouble Connecting to SSIS in SQL Server 2017 (14.0.1)
Setting up a new server with SQL Server 2017 (14.0.1000) and I just installed SSIS (14.0). As of right now I am unable to connect to SSIS from SSMS and receiving the following error message:
...
0
votes
0answers
29 views
SSIS OData Package Fails on SQL Server 2017
Awhile back, I created several SSIS packages, in SSDT 2015, that were deployed to a SQL Server 2016 SSIS instance. They were scheduled with agent jobs and run using basic auth.
Currently, in both ...
0
votes
1answer
35 views
error in SSIS - [SQL query execution task] Error: The binding name of the results must be zero for the complete result set and the XML results [closed]
I have an Execute SQL task that returns "Full result set". Despite that, the task fails. Please how can I fix it ?
[SQL query execution task] Error: The binding name of the results must be zero for ...
0
votes
1answer
36 views
Error while deploying package to catalog
I am getting below error while deploying package to SSIS catalog on the last step, I am sysadmin and have access to the below views. Can someone help please. Let me know if you need more info. Any ...
0
votes
0answers
36 views
SSIS to import tables from Access DB to SQL
im trying to design an SSIS package to import 80 tables from Access DB into SQL DB, I have done it through the export and import wizard then saved the SSIS package but i didnt like the way the package ...
2
votes
1answer
34 views
How to decrypt dtsx package?
I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so:
Use SSISDB
if object_id('tempdb..#package') is not null begin drop table #package end
...
0
votes
0answers
46 views
Why is my SSIS package not getting updated?
I have a Integration Services project within SSDT that has multiple packages. When I right click a single package and choose deploy, SSDT says the operation completed successfully.
However, when I ...
2
votes
1answer
51 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
1answer
66 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 ...
-1
votes
1answer
25 views
SSIS package not working [closed]
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
29 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?
2
votes
0answers
34 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
11 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
18 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
39 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
109 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
398 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
32 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
1k 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
120 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
211 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
194 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
25 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
131 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
40 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
40 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
444 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
22 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
130 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
65 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
20 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
23 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
44 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
73 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
135 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 ...
4
votes
0answers
308 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
171 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
45 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
82 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
240 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
94 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
91 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
104 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
214 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
339 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
44 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
129 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
854 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
115 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 ...
