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

learn more… | top users | synonyms

0
votes
1answer
58 views

SSIS Package - “Subquery returned more than 1 value” - Inserting one record into a table

SSIS on SQL Server 2008 R2 I have inherited an SSIS package that is failing when importing a spreadsheet. The part of the failure is in a data flow that consists of :- * an Excel Source Data Flow ...
0
votes
1answer
169 views

Communication Link Failure in SQL

I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there ...
2
votes
1answer
442 views

SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job. Now it fails intermittently with the ...
3
votes
2answers
140 views

What could cause “lagging” in SSIS execute SQL task?

Recently I noticed that the part of my ETL process loading the data into the staging area sometimes takes longer and sometimes shorter. With the following query (executed in the integration services ...
0
votes
1answer
57 views

Change database used for SQL Server Integration Services logging

The SQL Server Best Practices Analyzer noted that my Integration Services' logging table is in a system database. It recommends that I move this to a non-system database. (See here) How do I make ...
0
votes
1answer
32 views

SQL Server Merge mandate

Does the target table need to be identical to source table schematically? Specifically: 1) do they need to have the same number of columns and do the column types must be the same?, 2) do the column ...
9
votes
1answer
689 views

Problem creating a transaction in an SSIS package

I'm working on a package that needs to use a transaction but I'm currently getting the following error: SSIS package "CATS-Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS....
0
votes
0answers
20 views

SQL Server 2012 process (Stored procedure in SSIS Package via SQL Agent Job) stays running where it normally ends within 10 minutes

[] SPID 70 : SSISDB.catalog.start_execution;1 SPID 113 : EXECUTE ETL.uspLoadDataStoreData'dbo', 'Project', 0
0
votes
0answers
38 views

commonly used diagram for ETL

A the title indicates. Am i wondering what is the most commonly used diagram to explain an ETL process? In my case the process is rather linear, would a flowchart suffice? or is a Use-case also ...
1
vote
0answers
31 views

SSIS package version GUID does not stay stable between runs

We have a large quantity of SSIS packages created in visual studio 2008. Currently running on hundreds of servers. We are logging the details of each package before it runs. One of the things we are ...
1
vote
1answer
130 views

T-SQL Check another table rows if match delete them

I am creating a script in SSIS where I want table A to check if rows exist in table B. If they exist, they must be deleted. Table A is an archive table named Customer_archive Table B is a table that ...
0
votes
1answer
77 views

Do you need Table Lock when you have multiple OLE DB connections inserting to the same table?

SSIS is not really my forte. I noticed you have a table lock option on OLE DB destinations where the entire table becomes locked in what I assume is during the transaction of inserting data. What ...
1
vote
0answers
172 views

SQL Server Job Cannot Run SSIS Package Error 0xC00160AE Cluster Environment

I have a 2 machine SQL Cluster and a separate SSIS/SSRS Server. I'm running SQL Server 2014 and Integration Services 12. I'm getting Error 0xC00160AE when trying to run a job from my SQL cluster that ...
1
vote
0answers
29 views

SSIS DTSPackageType behaviour

I created a new SSIS package within Visual Studio 2013 with SSDT BI Tools (12.04.2430.0) installed bu when looking into this package I can see that the DTSPackageType is set to DTSDesigner100. Based ...
1
vote
2answers
59 views

SQL Server 2014 data integration between servers

Given two MS SQL servers, each with a different database, how does one create an SSIS package such that one can run a complex query that fills a temporary table and then transfer that data as a bulk ...
0
votes
1answer
121 views

Extract data from Oracle DBF files

I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files). I'd like to extract the information using SSIS or Pentaho's Kettle ...
0
votes
0answers
139 views

How to import data from Sqlite into SQL Server 2012

I need to import data from sqlite into SQL Server so we can include it in our data warehouse. Based on previous system volume. Id expect there to be 50-100,000 rows after a couple of years. For ...
2
votes
0answers
164 views

SQL Agent - PSexec remote commands causing SSIS packages to hang

First post here, so please be kind! I have a bit of a problem with a set of SQL Agent jobs (SQL Server 2014, running on Windows server 2012 R2) that I am trying to setup. Each job is a multiple step ...
1
vote
0answers
48 views

SSIS package with changes to Excel file

I have an SSIS package that loads up an Excel file on daily bases; from time to time the file changes. It can have additional columns in the Excel worksheets. How can I know if the Excel file has ...
0
votes
1answer
22 views

Whole numbers from sql column using SSIS

Below is the sample SQL. From Source1 tb, I need only whole numbers of Value column into ValueNEW of Destination1 tb as shown in the output pic. --Source1 IF EXISTS (SELECT * FROM sys.objects ...
13
votes
1answer
1k views

Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job ...
1
vote
0answers
34 views

SSIS Guarantee that all data will be moved to target when running data flows in parallel

My package has the following two tasks which run in parallel. DF - Move source data to target loads a copy of all source data to target via OLEDB Dest - Target and also loads a copy of source PK ...
0
votes
0answers
41 views

Primairy Key constraint error in SSIS with delete

So I have an SSIS package that deletes a bunch of data then inserts the new data. Even though the delete completes without error I am still getting a primary key constraint violation. When I run the ...
0
votes
1answer
79 views

How to Publish Checked-In changes only in Team Foundation Server 2013 to SQL Server?

I would like to ask you how to publish checked-in changes only in Team Foundation Server 2013 to SQL Server? It's a new installation of TFS 2013 - installed by me. Currently when I create new object ...
0
votes
1answer
44 views

Better parallelization than a sequence container in SSIS 2008?

We currently have a process that has to process hundreds of thousands of rows an hour. For valid reasons, it has to call a stored procedure for each row. We're using a SSIS package (SQL Server 2008) ...
1
vote
2answers
43 views

Should I convert timezones when moving data to a report server/data warehouse

I'm writing an SSIS Package to take data from a production DB to a reports server/data warehouse. The other databases on my reports server are all in Mountain Time but this DB is in UTC should I ...
1
vote
1answer
66 views

SSIS Merge component performing a UNION instead of merging

I have a package to combine data from multiple databases. I'm trying to bring together two data sets so they can be written to a single table. There's a common SSIS_ID (DT_I4) used to match rows and ...
7
votes
4answers
6k views

SQL Server 2014 - how to query SSISDB to find out the errors in the packages?

I have seen this question SSIS 2012 - How to Query Currently Running Packages in T-SQL? It gives me the following script: SELECT E.execution_id , E.folder_name , E.project_name , E....
2
votes
1answer
205 views

Running scheduled SSIS pkg with SQL Server 2012 Web Edition

I have a client with SQL Server 2012 Web Edition. I believe it has SQL Server Agent, but I see on the following Microsoft page that many integration services are not supported: https://msdn.microsoft....
1
vote
1answer
85 views

SSIS Derived Column Conditional Time Modification

I'm using SSIS to transfer data from a MySQL database to a SQL Server one. There's one point that I'm currently stuck on, however. MySQL TIME datatype supports values greater than or equal to 24:00:00,...
6
votes
1answer
664 views

SSIS - Fixed width flat file mapping tool/technique

I am wondering if there is a tool (or a special technique) that anyone has used to map fixed width flat files in SSIS Flat file connection managers? Usually I just use the Flat File Connection ...
1
vote
1answer
299 views

SSIS Execute Sql Task not executed on server

I have a package that has an Execute Sql Task. When I run it in SSDT it works fine, the query is executed. When I deploy it on the server and run the package, the query is not executed and there is ...
0
votes
1answer
33 views

Change SSIS package location (REPOST from SO) [closed]

My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to ...
0
votes
0answers
38 views

Change SSIS package location

My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to ...
0
votes
2answers
237 views

problems when importing csv files into SQL Server using SSIS when the columns are not consistent from quarter to quarter

I am using SSIS to upload a bunch of csv tables into SQL Server Express. The csv files are quarterly data files. They are largely consistent but every couple of years a column may be added or an ...
0
votes
0answers
86 views

Selected Parameter values is not passing to another tab click in SSRS view report

I built an SSRS Report in which the final report is populated on a tabular formatted report with the combination of many sub reports. The report takes 3 input parameters values as date,fund code,...
0
votes
1answer
25 views

SSIS - Update Specific Cell in CSV

I have a pre-defined csv template. The header row begins on row 5. Prior to this I have a date value in cell B2. How do I update this cell value in SSIS?
2
votes
2answers
163 views

Get rows inserted and updated dynamically in SSIS

I need to get the rows inserted and updated in each table through my SSIS package. Is there any way to do it except using the row count transformation component before each insert or update component....
3
votes
1answer
470 views

SSIS Control Flow: Precedence constraints not working as expected

The control flow of my SSIS package is not behaving as expected. What I want to achieve: Test if CLR is enabled on SQL Server using SELECT value FROM sys.configurations WHERE name = 'clr ...
2
votes
1answer
108 views

Proper permissions for ETL user

I'm working on changing the permissions on our ETL to incorporate best practices. This is our ETL in a nutshell: Load 100+ tables from 5 different SQL Servers into stage in DW Call procedures on ...
1
vote
1answer
240 views

“Unspecified Error” when executing SSIS Package

I am executing an "Initial Load" SSIS package, which truncates all ETL tables first, then re-populates them by pulling data from a remote server. I get the dreaded "Unspecified Error" at a different ...
4
votes
2answers
327 views

Loop through tables or copy paste

I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA ...
1
vote
0answers
23 views

Handling changing data in the Fact table

We have an employee & benefits data warehouse which is producing incorrect information in the fact table. In File A (Feb), the employee has a leaving date of 27th Feb 2015. In File B (Mar), the ...
13
votes
2answers
291 views

How to implement a Set based algorithm/UDF

I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns. The algorithm is implemented in VBA and does a bunch of math using values from some columns to ...
0
votes
2answers
949 views

Data Flow Transform Not Automatically Mapping Columns

I'm in the midst of creating a set of SSIS 2014 packages (using Visual Studio 2013) importing data from 50 tables. Since most of the tables are being filtered on the same column and I'm importing all ...
0
votes
0answers
21 views

SSIS - different versions of the project

I am designing the ETL process. I created a package that extracts the data from the source using stored procedures. The SQL query looks like this: EXEC BI.ProcedureName The procedure returns data ...
2
votes
2answers
184 views

Installing SQL Server Integration Services on Server Core

I have Windows Server 2008 R2 Core on virtual machine and want to install SQL Server Integration Services. On the D:/ drive SQL Server 2014 Enterprise .iso is mount. I am reading a book where it is ...
0
votes
0answers
244 views

SSIS detect & manipulate all user variables ( set all empty )

I am developing a rather large ETL ( 39 distinct Flat File or Excel Connection Managers in current build & about as many to go ) through SSIS ( Visual Studio and MSSQL Server versions both 2008-R2 ...
3
votes
3answers
394 views

Update Table from SP in SSIS

I know there are similar questions to this one, but I could not find one that answered my question. Inside my current SSIS package... I have a dataflow task which... Takes output from a stored ...
0
votes
0answers
38 views

Using TVF as Data Source in SSIS

I have an OLE DB Source Editor. I want to use a Table Valued Function as a data source. I select 'SQL Command' and enter: SELECT * FROM [dbo].[Test] ('UserA','2015/07/31') This works find. However, ...