Questions tagged [ssis]
SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.
944
questions
0
votes
0answers
14 views
SSIS Script transform package won't compile when deployed to dev [Visual Studio 2017]
I have visual studio 2017.
I have a script transform that I can run locally just fine, however when I deploy to the Integration Services Catalog on the dev server and run it, it fails with the ...
0
votes
1answer
11 views
Replace “ ” character derived column SSIS
So I am getting CSV Files and for some reason they are coming as "123-45-9873".
Now I trim the "-" out but the column is an SSN column so I am left with "12345987 taking the 3 out and putting the " ...
4
votes
1answer
81 views
+50
SSIS Package Not Being Deployed
I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project ...
-1
votes
2answers
70 views
T-SQL Query performance
We are using SSIS package to execute the following statement.
SQL statement
delete from ABC
OUTPUT DELETED.[ABC_Id], 5
into XYZ (OId, OCode)
where ([ABC_Id] = ?...
0
votes
0answers
35 views
SQL Server Integration Services Catalog - Package Errors
Just getting started with the catalog. Have a package that runs fine in Visual Studio. Fails every time in SSMS (after project is deployed to the SSISDB).
Here are the errors I get each time:
Even ...
2
votes
1answer
18 views
Azure SSIS Deploying in SQL Azure
Does deploying SSIS really required an azure data lake house when deploying on Azure database?. I am currently working on an SSIS project that its database is currently deployed on Azure and I am ...
1
vote
0answers
15 views
Remote Connection to SSIS
I have just created a new SQL Server with SQL Server 2017 (14.0.1000) and SSIS (14.0). I am able to remote connect with the admin user on that server whom I did all of the setup/configuration with, ...
1
vote
1answer
82 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
33 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
42 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
39 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 ...
1
vote
0answers
39 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
47 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
...
3
votes
0answers
104 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
66 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
92 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
35 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
13 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
22 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
51 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
162 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
581 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
169 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
266 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
247 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
178 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
45 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
555 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
31 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
157 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
80 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
21 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
24 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
45 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
86 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
163 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
364 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
179 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
46 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
100 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
282 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
98 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
98 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)...
