Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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
0 votes
0 answers
48 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, ...
user avatar
  • 323
0 votes
2 answers
3k 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 ...
user avatar
0 votes
0 answers
44 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 ...
user avatar
1 vote
3 answers
3k 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 ...
user avatar
0 votes
1 answer
25 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 ...
user avatar
  • 1
1 vote
1 answer
1k 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 ...
user avatar
  • 213
0 votes
1 answer
124 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 ...
user avatar
0 votes
0 answers
361 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. ...
user avatar
  • 3,619
0 votes
1 answer
716 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 ...
user avatar
4 votes
0 answers
2k 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 ...
user avatar
  • 41
0 votes
1 answer
149 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, ...
user avatar
0 votes
1 answer
361 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 ...
user avatar
  • 1,493
2 votes
1 answer
1k 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 ...
user avatar
2 votes
2 answers
190 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 ...
user avatar
1 vote
1 answer
342 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)...
user avatar
  • 33
1 vote
0 answers
356 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 ...
user avatar
1 vote
0 answers
539 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 ...
user avatar
1 vote
1 answer
3k 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 ...
user avatar
  • 362
2 votes
1 answer
659 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 ...
user avatar
0 votes
2 answers
5k 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 ...
user avatar
  • 713
1 vote
1 answer
480 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 ...
user avatar
0 votes
0 answers
1k 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 ...
user avatar
  • 177
2 votes
0 answers
116 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 ...
user avatar
1 vote
1 answer
909 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 ...
user avatar
  • 21
3 votes
1 answer
235 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,...
user avatar
  • 283
1 vote
2 answers
74 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. ...
user avatar
  • 123
3 votes
0 answers
570 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 ...
user avatar
  • 1,051
1 vote
1 answer
146 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 ...
user avatar
  • 177
5 votes
1 answer
704 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 ...
user avatar
0 votes
0 answers
824 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 ...
user avatar
  • 1,051
1 vote
0 answers
542 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 (...
user avatar
  • 11
0 votes
0 answers
3k 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 ...
user avatar
1 vote
1 answer
5k 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 ...
user avatar
  • 177
3 votes
1 answer
637 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 ...
user avatar
4 votes
1 answer
1k 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. ...
user avatar
0 votes
1 answer
44 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 -...
user avatar
0 votes
1 answer
651 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 ...
user avatar
3 votes
1 answer
3k 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. ...
user avatar
  • 67
0 votes
1 answer
527 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....
user avatar
2 votes
1 answer
290 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 ...
user avatar
  • 21
0 votes
1 answer
150 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 ...
user avatar
  • 3
2 votes
1 answer
817 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 ...
user avatar
  • 67
0 votes
1 answer
52 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 ...
user avatar
  • 1,493
0 votes
2 answers
1k 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 ...
user avatar
  • 261
3 votes
0 answers
43 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 ...
user avatar
  • 188
3 votes
2 answers
285 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 ...
user avatar
0 votes
1 answer
810 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, ...
user avatar
  • 1,493
2 votes
0 answers
1k 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 ...
user avatar
  • 1,493
2 votes
1 answer
841 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 ...
user avatar
  • 1,493
2 votes
1 answer
770 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 ...
user avatar
  • 1,493

1 2 3
4
5
21