After patching the SQL Server to 15.0.4223 it started to work 99%. There is a string column that is inserted into a number column in the target database. While that worked before, this still produced corrupt data, but only when run as a job. However, that was easily fixed by converting the field to the correct type in the SSIS package. So the solution is, so it seems, that there was a bug introduced in the SQL Server somewhere between 2012 and 2019 that has been fixed in more recent version.

The packages are going to execute and consume resources on whichever server they live on, which in this case would be SQLSvr1. Also, SQL Agent Jobs just execute the steps inside them local to the server they're on (unless the step references a Linked Server, etc). So the Agent Jobs probably would fail on SQLSvr2 if you tried running them without deploying your SSIS packages to SQLSvr2. The SSIS component of SQL Server would need to be installed and enabled on SQLSvr2 as well.

You can use SQL Server Authentication / a SQL Login to execute SSIS packages, as long as that account has the appropriate permissions provisioned. This might be the simpler route to go for your application. You probably want to take a look at the docs on https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-start-execution-ssisdb-database?view=sql-server-ver15 , which is a stored procedure that allows you execute a particular SSIS package. Please see the following regarding the permissions needed: This stored procedure requires one of the following permissions: READ and MODIFY permissions on the instance of execution, READ and EXECUTE permissions on the project, and if applicable, READ permissions on the referenced environment Membership to the ssis_admin database role Membership to the sysadmin server role Only one of the aforementioned permissions is needed. The simplest choice but best compromise with security without over-provisioning would probably be to add the User of that SQL Login to the https://docs.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver15 role.

Option 1 and 2 are good options for ssisdb. About the dtsx in the msdb, those usually come from wizards like import/export features in ssms or maintenace plans. If you need them for sure it's quite complicated. here you can find a good guide: https://www.sqlshack.com/how-to-retrieve-information-about-ssis-packages-stored-in-msdb-database/

My preference is to install SSIS on a separate server from my main database instance. Here's why. SSIS is, for the most part, a standalone application. As such, it also has its own resource needs, and consumes CPU and memory, as needed. There are going to be times when SSIS spikes CPU and memory, and limits SQL Server from getting the CPU and memory resources it would like. Similarly, SQL Server can spike CPU and memory, and limit SSIS from getting the CPU and memory it would like. It's much more difficult to balance the CPU and memory needs between two applications on one server, than it is to isolate them to their own VM, and scale them appropriately. As such, to do so you would need to account for sizing your servers to support both and also set your max server memory settings in SQL Server, to ensure enough memory is available for SSIS. It's simply a head-ache I prefer to avoid, if at all possible. Instead, size your SQL Server to support your SQL Server workload. For example, if under peak load, your SQL Server requires 8 CPUs and 64 GB of RAM, build a SQL VM with that as your baseline. Now, if SSIS needs 2 CPUs and 16 GB of RAM, those needs are a net addition to the 8 CPUs and 64 GB of RAM you've already decided you need for SQL Server. Should you choose to run these two on the same server, you're looking at 10 CPUs and 78 GB for RAM. Typically, either way you slice, you need the same number or resources to run the two services. The question comes down to whether you want to try to manage them on the same server. With Virtualization these days, it's almost a none issue. Simply slice up the CPU and memory resources you want for each service, and give each its own VM to run on. This will make performance issues much easier to troubleshoot down the road. All too many times I have seen scenarios where a DBA struggles to figure out if it is SSIS or SQL that is the problem when performance issues arise on a single server. Take for example CPU spike to 100% while an SSIS package runs. Is this related to bad queries pulling data out of your OLTP database, or instead inefficiencies within SSIS package and overall ETL process? With the two services running on separate servers, you can easily see which server has its resources spike the most during your SSIS process, and tackle the component head on. You also prevent one service from impacting the other, since they no long share a server. With that said, you can also refere to the https://docs.microsoft.com/en-us/sql/integration-services/scale-out/scale-out-support-for-high-availability?view=sql-server-ver15 on MS Docs to help make SSIS highly available when not installed along side SQL Server on the same cluster.

I think the error is related to the ODBC configuration on your server or computer. I'm wondering if you are using an SSIS Catalog to execute your package, or are you using DTExec on a server to execute your package? Would be a good idea if you can share more details about the server? I'm assuming you are using an SSIS Catalog. I would recommend reviewing the following settings: Review if your MySql Server has the same ODBC Driver Version as the SQL Server where SSIS resides. Review if the DSN Connection matches the Connection name in your SSIS Package. Go to ODBC Connection manager in the SQL Server where the SSIS Catalog resides, and make sure the ODBC Driver version matches the version in your SSIS Package. Go to Visual Studio, open your project, and check your SSIS Project properties --> Debugging -- Run64BitRunTime is False. Because you explained you are using odbc32, then your package project will be Run64BitRunTime "false" as I described above. If you made this change, then rebuild your project and deploy it again.

I have something similar running in our environment. Here is how we did it. You only have to worry about what you have sent from your SQL Server database. Create an SSIS package to load data into MYSQL, BUT you are going to be using a filter from a table that you create. The primary key is the last record sent. The table should look like this. Tablename | Key Customer 101 Accounts 521 At the last step you need to insert into this table the max Primary key ID for the table you sent. Then the next time the time the package run just filter the data being sent by the table. Example Select * FROM Customer where PK_ID > 101. Just ensure your package has the correct error handling. Hopefully this makes sense.

It's a little unclear what you're trying to accomplish, or the "why" you're trying to accomplish this. "The reason is to be able to have different teams responsible for updates and maintenance of other 3rd party applications on the SSIS server." - This is vague, by 3rd party applications, do you mean database applications, SSIS packages, or actual non-database software running on the server? "...we would like to separate the SSIS service from the actual database engine" - SSIS is a feature that coexists to the main SQL Server instance and engine, but is already separated as it's own running service. If you wanted to just use the SSIS service on a separate server, you'd have to install a second SQL Server instance (which means paying additional licensing for it as well). SSIS makes use of it's own database https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15 for storing a multitude of things including parameters and runtime history. So the main service for the SQL instance which is inclusive of the engine has to exist for SSIS packages to be able to execute and function properly. If you want division of responsibilities and access use proper security provisioning such that your SSIS team has access to only the functions they need with SSIS on your database server.

It looks like the owner of the SQL Agent job doesn't have access to the personal folder of user Aluka. If you use more public folder, it will have more chances to work.

SSIS Variable values can only be modified in the OnPreExecute and the OnPostExecute phase of pipeline execution. During execution, they are static. You can verify this behavior by adding a column to the dataflow with a Derived Column Task and use add your variable @[User::lastName] after the Script Task. You'll see value never changes between the first and final rows. Therefore, you have a logical error with this snippet (Name == @[User::lastName] && Company == @[User::lastCompany]) So, what do you do? The biggest challenge of course is you have a 330M row file. If we only care about sequential duplicates, then a script task as you designed is mostly correct except trying to assign to the SSIS Variables. Instead, you'll need to add column(s) to your data flow so your subsequent Conditional Split can make a logical choice. For normal work, I'd be fine with adding a lastName and lastCompany to the data buffer. However, given that you're going to be working in large sets of data, you need to fight for every scrap of memory you can get. I would instead perform the comparison logic in your Script Task and simply emit a boolean indicating whether this row is a duplicate of the preceding row. I would have a Script Task, acting as a transformation sitting after your File source. It would take as readonly input columns Company and Name and it would add a new column to the output called IsDuplicate using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { // member variables to keep track of previous row data private string lastName; private string lastCompany; public override void PreExecute() { base.PreExecute(); // initialize our variables this.lastCompany = ""; this.lastName = ""; } public override void Input0_ProcessInputRow(Input0Buffer Row) { // Encapsulate the logic from the Derived Column comparison here // // Are the two elements the same? // Do we have to worry about nulls here? Assuming not // Also assuming a case sensitive match is desired if (Row.Company == this.lastCompany && Row.Name == this.lastName) { Row.IsDuplicate = true; } else { // strictly speaking, this is not required as the default value for a bit if false but I favor explicit behaviour Row.IsDuplicate = false; } // Update the member variables to the previous row's data this.lastName = Row.Name; this.lastCompany = Row.Company; } } Now you can simplify your Conditional Split to the condition of our column IsDuplicate. I route any rows matching that to an output called "Sequential Duplicate" and my default path is called "First or non-duplicate rows". Again, I try to be as obvious with my intention so I can get my brain back into the game when I have to maintain it. That results in my data flow looking like

Welcome aboard the good ship SSIS. I'll be your captain this evening... Not everyone's first SSIS package is going to be as interesting as yours so some of the things I'm going to cover are techniques I only worry about when things are in the 20% of the 80/20 rule. SSIS package timings When you run a package in Visual Studio/SSDT, it runs under the context of the debugger. That's how we get the pretty UI, progress displays and the ability to set breakpoints. That's not free and I think an old rule of thumb was it could cause up to a 20% drag on processing. The only true way to get processing time is to run it in a controlled environment outside of Visual Studio. But that gets you into a licensing issue. Developing SSIS packages is free. But you cannot run an SSIS package outside of SSDT without a licensed installation of SQL Server on your machine. Developer edition is stupid cheap. Standard and Enterprise editions, not so much. If you where to Ctrl-F5, it would run the SSIS package outside of the debugger and be some degree faster but in your case, it'd likely error out specifying that "to run {task name} outside of Visual Studio, you must install the component". What that really means is you need to the SQL Server Integration Services Service installed. Once you can run an SSIS package outside of the context of the debugger, then you can get a better set of performance metrics. The easiest place to do this is the SSISDB in SQL Server. There are pretty reports that let you get down to component level timing as long as the package is run in the Performance logging mode (or you're on a 2017?+ and can define your own custom logging level) There are older ways of doing this, via the Logging but the current UI obscures it away. If the next sections don't provide illumination, ping me and I can flesh out the old ways. Quick and dirty performance thoughts Based on what you've described, it sounds like your Script Component is going to be the source of your poor performance. I don't know if you've gotten through all 330M rows in your source file. If the package runs in a reasonable amount of time, then I'd say keep using the whole file as your testbed. Personally, I'd take the first N lines of it to speed up your time to failure. All the components you described are synchronous components. 1 row in = 1 row out. Those are your fastest dataflow components as there are no memory copies happening and the activities can get parallelized. Well, maybe it's all synchronous - a script component by default acts in a synchronous fashion but you can change the settings to make it asynchronous. The quick way to tell whether it's the OLE DB Destination(s) is to delete them from your package. Version control is going to be your friend here - make a dumb testing change, roll back to good state. Try the next change and repeat If normal execution is 10 minutes, skipping sending the rows to the destination database and whatever actions it must take to save the data are not a factor in the timing. I've pushed large datasets into SQL Server, a well tuned destination (both SQL Server and the SSIS componentry) can make it sing. A poorly tuned destination is gonna cry with your data volume. My first run of your package would be Flat File Source -> Conditional Split => Row Count Good; Row Count Bad. Run that and see what happens. This is going to help you identify a theoretical best performance profile. 330M rows takes 1 minute per million to just read off disk, then you're looking at what, just shy of 6 hours? Where's that source file located? On your local disk or on a server share somewhere? You might get better performance copying from a network share to a local drive as a precursor step and then reading from disk. And then you get to factor in your workstation, hopefully, has slower disks than the actual server this will run on. The actual timings are less important that just knowing what the best your can expect from your package is compared to the subsequent things we do. Add your error destination back in and run it. How did that affect performance. Remove the error destination and add your script back in. Actually, what does "updates the package variables for duplicate detection". If the source data has a literal \ followed by a N, you can skip your Script task and use a Derived Column transformation to do the same thing. Assume you have CustomerName column ([CustomerName]=="\\N" ? NULL(DT_WSTR, 100)) : [CustomerName] You can either define a new column CustomerName_Clean or replace the existing. I usually favor adding a new column because if I have to troubleshoot, I like to see what I started with alongside what I ended up with. Destination OLE DB Destination still shows the assorted options so I'm not sure where you're seeing only the one. This is a place where you will need to change the default values. That Maximum Insert Commit Size - the default is that it will do an all or nothing commit of that 330M rows and that's gonna cause pain for the database. Pick a number, say 100k and start with that. Work with your DBA to see what is happening from their end as you start loading your target table. They may want to proactively grow their log before it starts ingesting your feed. If you have non-clustered indexes on that target table, remove them before you load. Growth of 100% or more and a single NCI, you're usually better off dropping and recreating them versus paying to keep the base table/heap and an additional index in sync during the data loads. If you have multiple NCIs on a table, the rule of thumb was 10% growth was the tipping point (SQLCAT articles no longer in existence or I'd reference them here) Data Types Double click the connector between your Flat File Source and your first component, the Conditional Split, and look at the Metadata tab. Do any of those columns report a TEXT named data type DT_TEXT, DT_NTEXT? If so, that's gonna be part of your headache. Large object types can get paged to disk when they pass through a data flow so now you're paying for surprise reads and writes, on your C drive unless you specified otherwise, along with the source reads and writes on wherever the database lives.

First, you seem to be missing a few zeros in the numbers you mentioned before you should start seeing problems (IMO) Second, I've only seen Kafka as part of a data loading solution for ingesting data from multiple IoT devices. In these instances, Kafka was used to solve the IoT problem. ACID Compliant databases have problems ingesting a bunch of single row inserts from multiple clients. This is due to the fact that COMMIT won't return until data is safely written to disk. Insert into live_data value ( ..... ); commit; The solution is to cache the request to save data then bulk load it into the DB. This is where Kafka comes into play. (We're talking scales upto 1M IoT readings per second) If you have problems loading 2GB of data per day, you need to investigating why. The key to performance is Bulk Loading of data and not using slow-by-slow (row by row) methods. I have found that database code (PL/SQL; T-SQL) works faster than ETL tools (eg Informatica) but the ETL Tools are easier to maintain over the long term.

After more experimentation, I found that while I was allowed to edit the SSIS OLE DB Destination component and change the code page, it wouldn't stick. i.e. I could click Save after the changes, open the editor again, and none of my edits had been saved. Searching instead for getting edits to stick, I stumbled across this: https://dba.stackexchange.com/questions/286478/how-do-i-fix-the-code-page-in-ssis-lookup-transformation-to-be-65001 And the answer was in there. On the Connection portion of the component editing, set the AlwaysUseDefaultCodePage setting to true, and change the code page to 65001 there. Then the explicit utf8 code page flows down to the column mappings. Now the package is running.

Is it possible to change it so that Insert has higher priority then Select so that Insert are not blocked If you enable the database option https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server then the SELECT will not require locks or block the INSERT. Also you could run the SELECT under SNAPSHOT transaction isolation level.

It's not a server role, it's one of the roles of the msdb database:SQLAgentUserRole - minimum rights from these threeSQLAgentReaderRoleSQLAgentOperatorRole - maximum rights of threeSee references to documentation: http://msdn.microsoft.com/en-us/library/ms188283.aspx http://msdn.microsoft.com/en-us/library/ms187901.aspx Free translation of response https://stackoverflow.com/a/6212630/5752652 from the participant https://stackoverflow.com/users/27535/gbn ♪

The mistake. Starting with SQL 2016, SSIS is available through the main connection to SQL Server, in the village of SSMS facilities, the branch Integration Service Catalog♪ Service connection Integration Services SSMS is left for interoperability with old SSIS versions where there is no SSIS catalogue - for example SQL 2008.Mantenance Plan can be migrated in the following ways: Through the unloading and subsequent loading of SSIS leaks dtutil♪ http://billfellows.blogspot.com/2011/11/ssis-package-extract-from-msdb.html and the selection of options for different versions of SQL. Insufficient - SSIS must be installed, which is not always possible.By copying the lines chosen by the request SELECT name FROM msdb.dbo.sysssispackages WHERE packagetype = 6with the subsequent opening of mantenance plans in SSMS - the connection parameters and references to databases are not correct.

It doesn't work at SSIS. Options for SSIS: OLEDB Source parameterized request for typeselect * from pays_ext where pay_id > ? limit 50;and compare variables Variable zero request. Structural variable SQL_Query and in terms for her"select * from pays_ext where pay_id > "+(DT_WSTR, 10)[User::Variable]+" limit 50;"Then in OELDB Source, indicate that you want data from the SQL request to the variable and indicate as a variable that was previously created SQL_Query♪

I solved the problem. First, he changed the user DSN to the system. https://www.codeproject.com/Articles/14401/How-to-Schedule-and-Run-a-SSIS-package-DTS-Job-in Does any connection manager in the package use ODBC DSN? If any ODBC DSN is used, please make sure the DSN type is System DSN rather than User DSN. The user DSN may not be accessible when the user that create the DSN logs off.Then created a new proxy and gave him other rights. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cbf9942c-7a1d-4c20-84d5-9326e1ae0933/ssis-package-only-runs-when-user-is-logged?forum=sqlintegrationservices Use master CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword' Use msdb Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential' Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy' Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'

The problem was solved by replacing OLE DB with ADO NET.

The variable you are using in the mapping is not the same as you are leaving the Results Set. By the way, when you use a variable that is mapped, you don't need to put her name, just put a question mark on the query (?) that it will use the variables mapped based on their mapping name (0,1,2,3).