Stack Exchange Network

Stack Exchange network consists of 175 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
17 views

Find “Mystery processes” on a MS SQL Sever environment? (SSIS, databases, etc)

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 ...
0
votes
2answers
19 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 ...
3
votes
0answers
76 views

SQL Server job will not run

I am trying to get a job to auto run that syncs a csv file into a datatable. I have tried everything I have found online. Here is my set up. I have a a login credential with myself as the identity, ...
1
vote
0answers
10 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 ...
0
votes
0answers
28 views

Data From ADP Needs To Be Ingested By SSIS, Data Has Variable Columns And Multi-line Headers [on hold]

Our company's payroll admin is exporting data for us from the payroll software, ADP, which I am then tasked with inserting into SQL Server through SSIS. The problem I am running into is that the ...
0
votes
1answer
36 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, ...
2
votes
0answers
30 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 ...
2
votes
1answer
28 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 ...
2
votes
1answer
37 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 ...
2
votes
1answer
30 views

Find SSIS package which calls stored procedure

We are trying to catch errors into an Error Log Table, similar to below. We have many Master SSIS packages, calling many stored procedures. Following code will log into the Error Log Table. Is there ...
1
vote
1answer
27 views

Fail SSIS “Execute Process Task” if false is returned

I have an "Execute Process Task" that calls Powershell and checks if a folder is empty. I'm wanting the SSIS job to fail if $false is returned. Here is the code param ([string] $FolderLocation) $...
0
votes
0answers
19 views

Move from SSIS to Azure

I have an ETL solution which we have been doing in conventional SSIS. The job of SSIS is just to create star schema from the application database to dimension and fact tables. There is very little ...
0
votes
0answers
24 views

Salesforce source SSIS Package error

Im extracting data from salesforce using COzyroc SSIS component. This package is scheduled as job in MSSQL server which runs on daily basis. This job failes on alternate days. One day it is executed ...
1
vote
3answers
45 views

find table used in SSIS Package script [duplicate]

I have a number of SSIS Packages I am trying to locate a table that is populated by one of the SSIS Package is there an easy way to find in the project where database.dbo.tabledata has been used in ...
3
votes
1answer
55 views

Should SSIS packages and SQL database be on same server?

I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling. So here's my question --- Should SSIS ...
0
votes
1answer
15 views

SSIS AND XLSM load time issue Visual Studio 2010

I am having the following issue of SSIS AND XLSM load time issue Visual Studio 2010 I want to speed this up I am reading a .xlsm as my excel source I have noticed its loading / reading every row of ...
3
votes
1answer
79 views

Reading 300% rows - Problematic execution plan

Whole queryplan and query: https://www.brentozar.com/pastetheplan/?id=BkgbANxN4 I am struggling with a query that uses a lot of time. The obvious reason for this is wrong estimates of amount of rows....
-1
votes
1answer
71 views

SQL TO SSIS derived column month name and 1ST of month [closed]

I have the following as sql SQL TO SSIS derived column month name and 1ST of month DATENAME(MM,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) --month name DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) --...
2
votes
1answer
39 views

MDW Data Collector Server Activity collection step 3 suddenly stopped working

For many weeks my data collections from my database instace have been uploading to my MDW server. Suddenly step 3 of the Server Activity Collector has stopped working giving the following error: Job ...
0
votes
0answers
26 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
40 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
38 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
114 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
24 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
50 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
52 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
28 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
75 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 "...
3
votes
1answer
37 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
28 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
165 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
96 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
436 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 ...
1
vote
1answer
156 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
67 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
101 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
28 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 ...
0
votes
1answer
94 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
82 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
393 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
36 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
94 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
368 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
242 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
83 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
50 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
370 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
693 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. ...