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

2
votes
2answers
97 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 ...
3
votes
1answer
229 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
83 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 ...
0
votes
1answer
142 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
281 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
22 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
267 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
511 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
20 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
130 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
160 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
313 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
28 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, ...
1
vote
0answers
33 views

Repeated login issue SQL Server 2008 R2 (SSIS)

I'm trying to figure out an issue I'm currently having with an SQL Server connection - there has been a few issues with timeouts on a SSIS package, where it has failed a login attempt - however as far ...
1
vote
0answers
21 views

How do I save my preferences after moving components around in the SSIS Toolbox?

I'm working with SSIS 2012 (SQL Server Data tools), which runs in the VS 2010 Shell. This is my first time working on a project that required us to install some 3rd party SSIS components. ...
0
votes
0answers
13 views

Downgrading SQL Server 2008 R2 from Enterprise edition to Standard editions [duplicate]

Can anyone tell me when we downgrade from SQL Server 2008 R2 Enterprise to Standard edition in a production environments. What part of SQL Server is needed to install and run a SSIS package? I need ...
1
vote
1answer
180 views

SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported. My query is a select on table Y verifying if not exists in table X and the result is written to table X. ...
3
votes
1answer
2k views

How can I resolve SSIS Excel Connection Manager Error 0xC0209303?

I have created an SSIS package that imports an excel file into a SQL Server table. The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where ...
2
votes
1answer
48 views

SSIS: What services to restart so Folder Permissions Propogate to username executing pacakge?

I created an SSIS package that needs access to a folder on a shared drive. The package runs successfully on my local machine but I get an access denied error when I run it on a different server ...
0
votes
0answers
2k views

Oracle:Test connection failed because of an error in initializing provider. ORA-12170: TNS:Connect timeout occurred

We have Migrated SQL server 2008R2 to SQL server 2012 from Windows 2008R2 onto Windows 2012R2. Everything went well , except for the Oracle database connection: We have a SSIS package created on SQL ...
4
votes
1answer
229 views

Star join query optimization - Change partitioning, use columnstore?

I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which ...
1
vote
0answers
147 views

Using SSIS vs writing your own ETL [closed]

I have two data sources. Data from Questionaires laying in a datatable in the data hosters database (queriable via REST in JSON) Data about clients from salesforce (also queriable via REST in JSON) ...
2
votes
1answer
907 views

Script component source won't load when click “Edit Script…”

I'm working on an SSIS package in Visual Studio 2013. For any script component throughout the package, if I double-click on it and then click the "Edit Script..." button, Visual Studio 2012 opens but ...
5
votes
1answer
2k views

Assiging stored procedure results to SSIS variable

I am trying to get the value from a stored procedure into an SSIS variable, and then testing to see if two SSIS tasks can work if I added an expression. So for an example, I am trying to use this ...
0
votes
1answer
55 views

Question regarding flow control of SQL based jobs in SSIS

Question: I have pertaining to SQL and SSIS : How do I make an SSIS package work according to a logical flow that I mapped out for it? Say there are six steps, I have the logic for how they should ...
1
vote
1answer
217 views

SSIS Run Excel Macro Error

I have an SSIS package that populates an Excel file. The package is run from a SQL Server Agent job. It all runs fine, but now I have added a Macro to the Excel file. This macro causes the Package to ...
1
vote
2answers
344 views

SSIS package with log provider configured using expression - creates multiple log files, fails if I change original target folder

I have created a small SSIS solution to illustrate my problem. Unfortunately it looks like I can't attach it here, so I will try to explain. I can send the solution to people individually. SSIS SQL ...
5
votes
2answers
274 views

Copy data from multiple databases to a central database for reporting and analysis

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting ...
2
votes
0answers
152 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
1
vote
1answer
58 views

dtsinstall.exe does not deploy packages

I created SSIS project solution in VS2008. I created 5 packages and deployed them to SSIS Storage succesfully. I executed 4 packages successfully. I leave SQL server few days alone (holiday!). I ...
2
votes
2answers
123 views

SSIS compatibility

I'm trying to use SSIS for preparing data warehouse and for making business objects for report generator. The problem is that i have to support multiple sql server versions - sql server 2008r2 and ...
1
vote
0answers
52 views

incremental update in HBase using sqoop

i am working on a POC where i need to implement same like SSIS for sql to hadoop. out DW are in SQL Server and we want to create a HBase at central location where all the data from diff DW will be ...
0
votes
2answers
210 views

SSIS File System Task (Copy File Operation) OverWriteDestination functionality

I was unable to find any documentation on the SSIS File System Task component OverWriteDestination property functionality for a Copy File Operation. I understand what it is used for but how does it ...
0
votes
1answer
504 views

The application is not installed - Visual Studio 2013

I need to deploy packages using visual studio 2013 but I am seen this message, the application is not installed, as you can see on the picture below. what do I need to install?
0
votes
0answers
16 views

Update source table from SSIS after Inserting in Destination [duplicate]

I am using SSIS 2012 from visual studio to extract all email information from source tables as follows: Data flow to take data from Notifications table in App1 Store that in SQL Destination. ...
4
votes
2answers
432 views

SSIS Package Error when called from SQL Agent Job

We have SSIS running on its own server under account 'ourserviceaccount'. We then have SQL Agent running on a separate server under the same account. I have set up a job that calls an SSIS package, ...
8
votes
4answers
3k views

Is it possible to develop a SSIS with a SQL Server Evaluation Version

We are thinking about buying an SQL Standard Server for implementing an ETL via SSIS. Since it is very expensive for us I would like to test developing SSIS Packages on a free version. Since the ...
3
votes
1answer
185 views

SSIS Repeated Header In CSV File

I am writing to a csv file in a Foreach loop. For each loop the code is writing the header so it appears repeatedly throughout the file. How do I ensure the header only appears once? I have tried ...
1
vote
0answers
178 views

Is there any way to set the default data source for the SQL Server Import and Export Wizard?

When I launch the SQL Server Import and Export Wizard for SQL Server 2014, the selection in the Data source drop-down defaults to .Net Framework Data Provider for ODBC. This happens regardless of ...
2
votes
2answers
506 views

Why would a job call a batch file with dtexec.exe instead of just calling the SSIS package directly?

I've inherited a SQL 2012 server with a number of jobs that call SSIS packages. Instead of the more obvious solution (calling them directly from a "SSIS Package" job step), these jobs use a CmdExec ...
2
votes
1answer
37 views

Demographics Dimension Table - Finding the Key!

I have a dimension table which captures demographics on Employees. The schema is similar to below: CREATE TABLE [dbo].[DimDemographics] ( [DemographicsKey] [int] NOT NULL, [AgeBand] ...
0
votes
0answers
104 views

Could not load file or Assembliy “SecureBlackBox.dll”

Task- To Send csv files to Secure FTP. Problem: Script Task using Third Party DLL for Secure FTP mainly "Eldos" is not able to load dll ,when deployed on integration server. Resolution Usually i ...
2
votes
1answer
244 views

SSIS Update (Replace) record instead of append based on Primary Key

I created an SSIS Pacakge that imports a monthly extract claims file. I have a simple Data Flow task, flat file to OLEDB Destination with a Derived column component in between used to generate a ...
1
vote
0answers
103 views

SSIS Error - Login Failed for user 'sa' [closed]

I am running an SSIS package. However, I get the below error... The ExistsOnSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'sa'.). The SQL statement issued ...
1
vote
1answer
125 views

When does SSIS SQL Server Configuration write to the configuration table?

I am using SQL Server configuration in an SSIS pacakge. Other than when you create/edit the SQL Server configuration entry in the SSIS Package Configurations, are there other times that SSIS writes to ...
2
votes
1answer
60 views

Introduce or not introduce SSIS to an ETL project [closed]

Given is an ETL process that works fine using traditional SQL. The process is well optimized making use of ALTER TABLE permissions and performing partition switching, index creation, partition ...
1
vote
1answer
59 views

SSIS Custom Task with a default Task UI

I followed the tutorial here to build a very simple task and was able to get that task to work. Here are my current tools: Sql Server 2012 Visual Studio 2013 (project is compiled in .net 4.0) Sql ...
5
votes
2answers
2k views

sql server max memory includes SSIS?

I have installed 2 instances of sql server plus SSIS on the following server. Note the amount of RAM is nearly 384 GB And this is the max and min memory settings that I have applied to my 2 ...
1
vote
1answer
150 views

How to restrict access to SQL Server user in some period of time

I have two SQL Server users 'A' and 'B'. I use user 'A' for ETL and 'B' for reporting. My requirements is to restrict user 'B' access while user 'A' performing ETL (Extraction, Transforming and ...
0
votes
3answers
454 views

SSIS - Best way to insert large number of rows

So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a ...