Stack Exchange Network

Stack Exchange network consists of 174 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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.

2
votes
1answer
21 views

Handling variable fields in a CSV file with JSON?

Im trying to figure out the engineering to get a CSV file into PostgreSQL. Requirements: The CSV has X number of predetermined required fields The CSV file has Y number of dynamic fields Example: ...
2
votes
1answer
39 views

Is it bad practice to use a data-warehouse for providing reorganized non-BI data?

The Primary goal is the case-optimized data representation. The idea is to gather data from several databases, save the data in a data-warehouse and restructure/connect the data in data marts(similar ...
0
votes
1answer
61 views

SQL Bulk import vs SSIS in Simplicity

We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week. All ...
2
votes
1answer
137 views

Is it impossible to force MariaDB users to SSL?

First of all, my english is really bad, so sorry for my english mistakes. I have a MariaDB 10.2.15. I installed SSL, and its working fine, but I cant force use SSL to users. my.cnf: [mysqld] ssl ssl-...
2
votes
0answers
29 views

SSIS - dynamically merging a group of files

I'm trying to create an import package for our new mssql database and I'm having to merge a group of 20 files together to be fed into a table on the database. However not all of those 20 files will be ...
0
votes
1answer
42 views

Multiple Agent Jobs Running same SSIS Package

I have the following doubt, is there a possibility to have faild a Job when are running simultaneously as the following example: 4 SQL Agent Jobs running the same SSIS in different schedules, I've ...
0
votes
0answers
23 views

Partial Database Migration: Dealing with CRUD Queries

Overview Notes: We cannot create a linked server object, because of the security policies. We are using MSSQL (T-SQL/Microsoft), and its SSIS tool. (I can add version(s), if they are needed) ...
1
vote
2answers
153 views

Character Conversion Issue Importing From CSV File

After doing a load of CSV files, there are various words that are "written" to the database incorrectly. Some examples: Diã¡ria should be Diária Crã©dito should be Crédito Ligaã§ãµes should be ...
2
votes
1answer
594 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS

I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from visual studio or from SQL Server Management Studio, it ran ...
1
vote
0answers
29 views

SSISDB too large

I have client whose SSISDB is too large (71GB). I have tried to set RETENTION_WINDOW to 50 from 365 and ran the maintenance job, but it has made little difference. Can someone please suggest an ...
0
votes
0answers
26 views

Data warehousing (ETL): Scheduled data migration from MySQL to PostgreSQL

I've been assigned task of data warehousing for Reporting and data analysis. Let me first explain what we are going to do. Step 1. Replicate production server MySQL database. Step 2. Scheduled ETL: ...
0
votes
1answer
94 views

Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2

I'm working on the production of a Data Warehouse with SSIS, and today we create the first dimension with SCD of type 2. The fact table is linked in the DIM_CIPESSOA dimension through the idcipessoa ...
0
votes
0answers
55 views

Confusion in Informix when selecting dates with JOIN

I am trying to run a query in Informix, doing a join in a table of logs where we save the record of insertion of the rows. Notice that the query below brings two results, with different insertion ...
4
votes
1answer
65 views

Single User Mode and Replication

If I put a replicated database in single user mode will the Log Agent count as the single user? ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE I'm trying to block users from ...
1
vote
1answer
90 views

Copying large amounts of data to another target tables

I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution ...
0
votes
0answers
64 views

SQL Server Shared Memory Provider Timeout

Recently our ETL started failing with Error: Shared Memory Provider Timeout, Our DTSX package is running on Integration Services Catalog and it has three main phases: Extract Phase DataWarehouse ...
0
votes
1answer
182 views

Data Warehouse: Staging Area, DWH databases and ETL tool on one server? [closed]

I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the ...
1
vote
2answers
58 views

Approaches for handling nvarchar sources to data warehouse

Situation is this...our data warehouse doesn't need nvarchar and is built using all non-unicode datatypes. But all new source systems are all coming in with unicode (utf8 Oracle or nvarchar in ...
-3
votes
1answer
174 views

I need to generate a GUID,help [closed]

I need help with an SSIS package, I need to generate a GUID code every time I register a row of data, can someone explain how it is done? In the SISS package, the data is extracted from an excel file,...
0
votes
1answer
59 views

Restoring MSSQL back-ups to Oracle 12c

I currently have an Oracle 12c DW that has several data sources of varying types (MSSQL, Oracle, MS Access, PostgreSQL, etc.) being moved into it as part of our existing ETL process. We have FME (...
2
votes
1answer
28 views

How can I SELECT an 'instance' in PostgreSQL?

I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code: CREATE TRIGGER cdc_table ON db.table AS ...
1
vote
0answers
59 views

Data transformation

so, I'm facing this issue in storing my file in my database. I have an excel file that I need to load in database. The file looks like this: The issue is that I need to store all this in columns: ...
1
vote
0answers
184 views

UPSERT or INCREMENTAL update of table but with DELETE

I have a following problem. We have two systems, an OLTP system and our Data Warehouse, both on PostgreSQL. I need to sync one table from OLTP system with DWH, so they're identical. I am using ...
0
votes
2answers
319 views

SQL SSIS Closing Files already in use

Sometimes when running SSIS packages on flat files, I receive error: "The process cannot access the file because it is being used by another process". Another person already has file opened. Is ...
1
vote
3answers
63 views

SQL SSIS Internal ETL at Company

Business Departments at our Finance company want IT to import various spreadsheets (companyid, productid, balance, transactions) into the databases. Lets say, the SQL physical server harddrive ...
2
votes
1answer
23 views

Existing data to new table, how to set up the PK & Identity

I am moving an Access app to SQL Server. I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field. The field is currently set as IDENTITY(1,1). I ...
1
vote
4answers
193 views

Does ELT data end up in an RDBMS?

I think I am confusing things. In ETL, data conversion starts at Database A and ends up in Database B, both are relational databases. Maybe I have a 20-year-old system. I use ETL to get the data ...
0
votes
2answers
39 views

Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application? [closed]

My small experience with ELT and reading makes it look like it is mostly for development. Because the data is not cleaned up like in ETL that makes me think the new application will need to adapt its ...
0
votes
1answer
34 views

Handling delayed key info in real time data warehouse

Our data warehouse is currently loaded with a traditional daily ETL batch job, but we're looking to soon implement a few stars in real-time (fed by streaming Kafka messages, FWIW). So only a couple ...
1
vote
2answers
45 views

How Does SSIS Manage Connection Traffic?

I recently started using SSIS in my internship and I am still learning. We have a dedicated SSIS server that executes all of the ETL jobs and does not hold any other databases. We have many SQL ...
0
votes
0answers
77 views

How to extract and store errors from Pentaho PDI

I'm using Pentaho PDI 'Spoon' version 7.1 to build an ETL package. The existing version of the package (built in SSIS) uses the built in variable @[System::ErrorDescription] to store an error ...
0
votes
1answer
50 views

SSIS - Iterative Data Flow Task with Schema Differences

I am new to SSIS and am trying to change a current process that moves data from one server to another using a linked server to use an SSIS package instead. Currently, I have am getting a list of the ...
0
votes
0answers
42 views

Import data into Apache Spark

I have been trying to find GUI tool or ETL tool to move data files like say large CSV and Excel files into Apache Spark RDD (and/or HDFS of Hadoop). NOW I just want to say that I have been importing ...
2
votes
0answers
100 views

SSIS multiple parallel ETL: Task Scheduler or Service Broker

We have one SQL Server 2008 on which we run several ETL packages in parallel. This setup has always suffered stability issues. Since I have taken over I am trying to find a way to increase stability ...
1
vote
0answers
7 views

SSDT no avaible input column error

i am learning SSDT from this link https://msdn.microsoft.com/en-us/library/jj819740(v=sql.120).aspx on point 8 i stuck before that i install DQS successfully and also connected to Data Quality ...
0
votes
1answer
208 views

Huge Size (still) with Differential Backup

I am new to differential backup so please bear with me. I have a database which the full backup size is ~55GB. I perform ETL nightly on this database. The ETL process involves UPDATE, DELETE, and ...
1
vote
0answers
19 views

ETL Job Issue - Works for One Server and Not for other

I have an ETL job which connects to 2 Servers. Recently it is acting strange with ETL running successfully for 1 server and not for 2 server. This is with one particular code which has link up to ...
3
votes
2answers
96 views

Optimize SSIS package for fewer queries

I need to associate customers' order with their "level" (Silver, Gold, etc.) when they placed the order: CRM server::CRM db::CRM table ---------- CustomerID PreviousLevel NewLevel ...
0
votes
1answer
46 views

MS SQL servers in different countries linked for ETL via SP

The title could be a bit complicated, but the question is very simple... We developed an application which uses a proprietary database (call it DB_ONE). Then, there's another application with its own ...
2
votes
2answers
39 views

Replicating data to a head office with limitations

Here's my situation. We have cash registers that send transactions to the store server. With every transaction received, the store server updates tables... at the same time other management ...
5
votes
1answer
2k views

SQL Server Data Tools for VS 2015 - Anyone using for SSIS development to multiple versions?

According to this link, https://www.mssqltips.com/sqlservertip/4253/backwards-compatibility-in-sql-server-data-tools-for-integration-services/ the newest version of SQL Server Data Tools - "SSDT for ...
0
votes
1answer
58 views

Linked Server only for Select Statements?

In linked server concept can we run only select queries like below ? SELECT * FROM OPENQUERY([SERVER_NAME], 'SELECT * FROM DATABASE_NAME..TABLENAME') I have a job which runs through Linked server. ...
1
vote
1answer
470 views

ETL Issue failing with Timeout error

We have a ETL job which is running each day at 02:00 AM. Until last week the Job ran successfully with no issues. The ETL runs the extract by connecting through the Linked server to the PROD server. ...
1
vote
1answer
524 views

How can I prevent Pentaho DI/Spoon from deadlocking?

I have a transformation with four steps: Read a pipe-delimited text (a text file input step) Write to a staging table, with truncation (import table) Run a stored procedure against the table ...
1
vote
1answer
285 views

How can I control how Pentaho DI formats integers coming from a text file?

I have a simple pipe-delimited text file with integers recorded as a sequence of numbers: 012345|00678 |various|text|fields There are some leading zeros, and some trailing white space, but no ...
1
vote
1answer
53 views

Performance counters of a ETL Database enviroment. What to look for?

I am monitoring a SQL Server 2008 box with Windows Server 2008. There is lot of inserts, deletes and updates in long duration transactions during from 3 to 4 hours a day. My question is, if the new ...
0
votes
0answers
49 views

SQL Server Datawarehouse Phase running infinite

So many years we use SSIS (Microsoft Integration Services) running etl operations to provide data. All these past years, the etl process was running as expected. Time to time as the data becomes ...
1
vote
1answer
47 views

Difference between DEFINE_CHAIN_EVENT_STEP and DEFINE_CHAIN_STEP procedure

As per oracle help:* DEFINE_CHAIN_EVENT_STEP Procedure: This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, ...
0
votes
1answer
175 views

Different Schemas or Different Tables for Synonym Switching

We are designing our Synonym Switching ETL process and have questions pertaining to the best way to differentiate the two tables used in this process. Option #1: Schema Names: Schema_name....
1
vote
1answer
661 views

Can you use ODBC to extract data directly from SAP HANA S/4 1610?

Trying to get a handle on what's possible and what's not with SAP Hana S/4 1610. My understanding is that one can access the underlying SAP HANA DB via ODBC simply by utilzing the SAP HANA Client ...