Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [ssis]

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

0
votes
0answers
10 views

Integration Services Catalog folder permissions changed

Question: Do any of the SQL Server systems tables in either SSISDB or MSDB contain information that would allow me to discovered which users are making changes to folder permissions in the Integration ...
1
vote
2answers
33 views

Reverse SQL Server Listener?

The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to. We also have SSIS on the secondary replica of the HA ...
0
votes
1answer
34 views

SQL Job Fails Logging In to Integration Services

I have a SQL Server 2012 where all databases are set up in instances. I created a DTS package that accesses a table in a database, let's say MyDB\MyInstance and CSV files in a network folder. ...
0
votes
1answer
85 views

TSQL or SSIS for Direct Insert Speed Performance

I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data. If TSQL Server and SSIS have the ...
1
vote
0answers
23 views

Availability Group and Transfer SQL Server Objects SSIS task

We have two db instances on Always On Availability Group. And I need to transfer data from selection of tables to the primary DB. I have created SQL Agent Job for SSIS Transfer SQL Server Objects ...
-1
votes
2answers
39 views

SSIS Balanced Data Distributor vs T-SQL Insert [closed]

Currently researching performance difference between SSIS Balanced Data Distributor and T-SQL Insert. We want to insert large amounts of data from one table into another; tables are in the same SQL ...
0
votes
2answers
47 views

Multiline UDF or Stored Procedure for SSIS Data Source

We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow. Would utilizing a stored procedure produce any faster results when streaming data from one place to ...
1
vote
1answer
21 views

Compare The lenght of two string variables inside an Expression task in SSIS

Hope you're doing well. I need to compare the Length of two string variables inside an expression task in SSIS so that if they have the same length the package will execute the next step and if not it ...
0
votes
1answer
36 views

How to Use OPENROWSET function within a Transactional SSIS Package

I have an SSIS Package and one of its components is "Execute SQL TasK" and I've written an OPNEROWSET query inside this task. When I configure the transaction option of the whole package from "...
2
votes
1answer
28 views

Find max date in multiple columns in SSIS

I have csv file source and it have 5 date columns . I need to create a new column which will have the latest date after comparing all the 5 columns . Expample : source ![cust id dt1 dt2 dt3 dt4 ...
0
votes
1answer
27 views

Temp tables not being created in SQL Server Azure [duplicate]

I have created a stored procedure that creates multiple temp tables for use in the sproc and then drops them at the end of the sproc. The problem I am encountering is I can execute the stored ...
0
votes
1answer
84 views

SQL Loop through Inserts and Update table

I have a table (tblStagingHeader) containing the below two columns and example data. It is a record of SQL table names that have been created as part of an SSIS\ETL load. StageTable Inserted --...
0
votes
1answer
48 views

Looking for solution to add double quotes in field which has commas in SSIS

I have a field called something like TYPE & the values are variant in it. I want the SSIS to export the result to CSV & while exporting, if it finds the , in column TYPE only that line/value ...
1
vote
1answer
102 views

SSIS column cannot convert between unicode and non-unicode string data types

I am transferring a table from SQL Server to PDA Server. This is my data flow: ADO NET Source ---> OLE DB Destination All of the columns in my source table are varchars and so when I created my ...
0
votes
1answer
48 views

Has anyone connected to Oracle from SSIS using Oracle Driver ( 12.1.0 ) from VS 2017 Data Tools SSIS

I have installed Oracle Driver ( 12.1.0 ) and I have VS 2017 Data Tools SSIS . The connection to Oracle Service from local machine via ODBC 64 bit on my machine is working. Connection to Oracle ...
0
votes
2answers
44 views

Running SSIS jobs sequentially in T-SQL

We are loading ETL data in two parts, first with an SSIS package and then stored procedure. In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 ...
1
vote
2answers
55 views

Running Powershell Code embedded in SSIS or TSQL, not in external file

Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved ...
0
votes
1answer
24 views

SSIS Excel to SQL Server - Need to add SSIS variables into the mix

I have an SSIS job that loads Excel files from a UNC path into a staging SQL table, then executes a stored procedure at the SQL server to load the data into a production SQL table. What I need to do ...
-1
votes
1answer
41 views

SSIS Error :Cannot roll back .No transaction or savepoint of that name was found

Hope you are Doing well. I have 1 SSIS Package as you can see below : In the "Insert" part of the Package which is an Execute sql task, I'm executing a Stored procedure . this is the code inside the ...
0
votes
1answer
25 views

Enable or disable a task based on parameter

I have an SSIS package called Main_Flow that executes several other packages in order. What I'd like to do is to somehow parametrise the enabling/disabling of packages in the main flow. This way I ...
0
votes
1answer
144 views

Install SSDT for VS 2017 - Missing Integration Services

I have downloaded and installed SSDT from Microsoft docs. The install was successful and I now have SSDT for VS 2017 installed - version 15.8.9 (as per screenshot) However, I cannot create an SSIS ...
0
votes
1answer
25 views

SSIS Installation on Dedicated Server

We are planning on installing SSIS 2016 on a new dedicated ETL server. The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server). ...
1
vote
1answer
61 views

SSIS Data Source: Embedded Queries, Stored Procedures, or Functions

We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this. When writing queries from the OLTP Data Source, it it better to utilize? 1) Inline Embedded queries 2)...
1
vote
1answer
86 views

SSIS: Passing variable from child package to parent package

I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order. The first package to be called from Main_Flow is ...
0
votes
1answer
92 views

Export all SSIS packages from SSISDB

We have quite a few SSIS packages on SQL Server 2012 deployed to SSISDB, you can right-click on a project and select 'Export' saved as an *ispac file, however, is there a script to Export All Projects ...
1
vote
1answer
61 views

Why am I getting an error about truncation when running an SSIS job?

hoping someone can help me out. I have no idea what's going on with this job. It runs every week night and always comes back with the same errors. There are about ten files that are written. We pull ...
1
vote
1answer
58 views

Last character in column shift to next column

I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the ...
1
vote
1answer
27 views

Unable to find “Local” window while debugging SSIS package

I'm trying to debug my package in SSIS. I need to see the value of my variables while debugging, but the problem is that I cannot find the "Local" window. The only window I have is the breakpoint ...
0
votes
2answers
1k views

SSIS vs T-SQL Join Performance, Difference Databases on Same server

We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases. I suspect for small ...
-1
votes
1answer
201 views

Foreach Loop Enumerator with multiple File Names

I have a directory like C:\NewExcelFile in which I have lots of different Excel files with different names (but with the same extension .xlsx). I have a "foreach loop enumerator" in my package which I ...
4
votes
3answers
302 views

SSIS data flow to update source table rows after copying to destination

I have a simple data flow that copies a subset of data from a source table on an internal database to a table on web-facing database. If there is a problem, the error is outputted to an errors table. ...
0
votes
1answer
26 views

Precedence constraint does not run in SSIS package

Hope you are doing well. I have a Precedence constraint that does not work . Putting it into more clear way , the package is executed successfully up until the components in which I have the ...
1
vote
1answer
41 views

SQL Server SSIS quandary

I have an SSIS job that generates a list of servers from a table, then executes a stored procedure located in an administrative database on each server. When I try to add one specific server to the ...
0
votes
1answer
98 views

Facing Error in “Execute Sql task” component (The type of the value (DBNull) being assigned to variable)

Hope you are doing well I have a table as you can see below : I have a variable "ExcelPath" in "SSIS" in which I keep "FilePath" column . Then I have "Execute SQL task" and the query I wrote in ...
0
votes
1answer
41 views

Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)

I'm struggling to find out how we can store the conditions inside a conditional split transformation when we run a package. I need a snapshot of the expressions (the whole expressions, not only ...
1
vote
1answer
153 views

How to read data from multiple Excel files with SQL Server Integration Services(New Senario)

Hope you're doing well. I have a scenario here. As you can see I have a table with two main columns : What I need is to read each "FilePath" which is "Active" right now and then read the Excel files ...
1
vote
1answer
55 views

SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds

Ultimately, what I am trying to do is create an SSIS package in the QA environment for another team which truncates the destination tables and inserts from the source tables. However, on one table, ...
0
votes
0answers
55 views

Cannot open the datefile when running a scheduled ssis package. SQL

So I have researched this problem thoroughly, or so I think. I want to schedule an upload of a CSV to our database in SQL Server. I have created the SSIS package using tasks --> import data. When I ...
0
votes
1answer
238 views

Cannot deploy SSIS packages after upgrade to SQL Server 2016 SP2 Build 5201.2

We recently upgraded from SQL 2016 SP1 build 4466 to SP2 build 5201 (CU2 GDR) on an AG. On one server an analyst was deploying SSIS using Visual Studio Enterprise 2015 (version 14.0.25431.01 Update 3 ...
1
vote
2answers
220 views

SSIS package deployed to SQL Server hangs when running a Powershell script

We have a SSIS package that tidies up a few files and folders, and want to automate it to run every night via the SQL Server Agent. When running the package via Visual Studio (2015 and 2017) the ...
0
votes
2answers
621 views

Connection Manager in SSIS 2017 Package NOT Saving the Password

We're using Visual Studio 15.7.2 and using SSIS on SQL 2017 (14.0.3029.16). We built a connection manager to a 3rd-party, vendor managed SQL Server 2008 R2 (10.50.6529.0). Our only allowed ...
1
vote
1answer
457 views

Upgrading SSIS Packages to 2017

The purpose of this question is to document my answer to what seemed to be a strange issue when changing the target SQL Server version of an SSIS package created in some older versions of SSIS prior ...
1
vote
1answer
411 views

Problems Exporting Long Columns to Flat File Using SSIS

We have an SSIS package that is going to generate files to be consumed by Google Big Query. The files will be gzipped .tsv files. One of the requirements is that the file be UTF-8. We have set this ...
0
votes
0answers
64 views

SSIS deployment manifest - requires Integration Services to be installed - it is!

We have a SQL Server 2016 Standard cluster set up with 2 nodes with Integration Services v13.0 installed on both nodes independently, i.e. Integration Services is not clustered. When I try to install ...
1
vote
2answers
50 views

Saving SSIS package from server to local computer

Right now I'm able to upload an SSIS package from my local computer to SQL Server with my C# program: Microsoft.SqlServer.Dts.Runtime.Application app; app = new Microsoft.SqlServer.Dts.Runtime....
0
votes
1answer
365 views

SSIS VS17 Unsupported: This version of visual studio is unable to open the following projects dtproj

I get this error when trying to open VS17 With SSIS Project written on VS15:
0
votes
1answer
110 views

SSIS Transfer Login Task Error

I have created an SSIS Transfer Logins Task. I have selected the SourceConnection and DestinationConnection and both valid and active. I have selected certain Windows group accounts to transfer ...
0
votes
4answers
113 views

SSIS deployment failed - working with files

I've developed a simple SSIS package that will open a csv file, select a number of column, add two derived columns and then copy the results to a file. This works within visual studio (even if it ...
1
vote
3answers
68 views

Can someone guide me how to compare tables with SSIS

I have two tables in two different databases in two different servers. Both tables have an orderid column, and a given value for orderid references the same entity in both tables. I want to check ...
2
votes
1answer
91 views

How does SSIS Balanced Data Distributor decide to split records?

I have been using the balanced data distributor control in SSIS, but am coming across an issue in which my parallel destinations (Salesforce bulk api via TaskFactory) are getting in lock conflicts. I ...