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
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.
...
