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