This article provides information about configuring MS SQL Server Repository connection
By default MS SQL Server repository connection is using 32 bit Microsoft OLE DB Provider for SQL Server, it is possible to use different 32 bit OLE DB Provider by switching to OLE DB Repository connection type.
Click OptionsClick PlusEnter connection nameSet type to MS SQL ServerSelect/Enter server nameEnter username and passwordSelect database nameMake sure that connection actually works
Note: When user name and password are blank windows autentification is used
Click OptionsClick PlusEnter connection nameSet type to Ole DBSelect build connectionSelect relevant Ole DB provider and enter necessary parametersMake sure that connection actually works
Notes:Using Sql Server user name and password is always better than using windows authentication.
Consider the following scenario.
Administrator Jonh installs the software everything is working fine.
Developer Peter logs in, nothing is working, because the developer has no access to the repository database.
Peter gets proper access and he can do his job.
Over weekend another administrator want to check execution status he logs in, the same problem happens again.
Quite often our customers are not able to load data into SQL Server due to "Function sequence error"
The problem can be easily addressed by installing the latest version of ODBC Drivers for MS SQL Server
Getting the latest version of SQL Server ODBC Drivers
Both Visual Importer ETL and Advanced ETL Processor are 32-bit applications so they use 32-bit ODBC drivers
Our software uses the highest driver installed for example If both SQL server 2005 and 2008 are installed it will use SQL Server 2008 Driver.
To Check Version of SQL Server ODBC Driver do the following
Wiki article with the links to the latest version of drivers.
Great news, all our ETL products support TLS 1.2 now.
That does not mean that the software is actually using it.
To enable it you have to install a lot of patches from Microsoft. they must be installed on both the client and the server.
Then you have to enable TLS 1.2 and disable the rest of the protocols (on the server and client)
We recommend using IISCripto for it
And the last step is to reboot the computer
All our ETL tools have no limitations, which means you can load your data today and solve all the data quality problems right now.
Load data in Microsoft SQL server in just a few clicks. Plus there is no need to create any scripts or learn command line tools just point and click.
There several ways to load data into Microsoft SQL Server Database
This article describes loading data via BCP.
BCP is the fastest way of loading data into SQL Server
Check constraintsEnsure that any constraints on the destination table are checked during the bulk copy operation. By default, constraints are ignored. Keep identitySpecify that there are values in the data file for an identity column.
Keep NULLS Specify that any columns containing a null value should be retained as null values, even if a default value was specified for that column in the destination table. Batch sizeSpecify the number of rows in a batch. The default is the entire data file. The following values for the Batch sizeproperty have these effects:If you set Batch size to zero, the data is loaded in a single batch. The first row that fails will cause the entire load to be cancelled, and the step fails.If you set Batch size to one, the data is loaded one row at a time. Each row that fails is counted as a one-row failure. Previously loaded rows are committed. If you set Batch size to a value greater than one, the data is loaded one batch at a time. Any row that fails in a batch fails that entire batch; loading stops and the step fails. Rows in previously loaded batches are either committed or if the step has joined the package transaction, provisionally retained in the transaction, subject to later commitment or roll-back.
Old version:1640 records per second:
New version:4091 records per second:
Note: Only If your table has TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX), XML, IMAGE or BINARY(MAX) fields.
+ Up to 2 times faster data extraction from SQL Server + Up to 2 times faster data extraction from ODBC sources + Up to 40 percent faster loading data into SQL Server + Up to 40 percent faster loading data into ODBC + Up to 10 percent faster QVX files creation + Up to 10 percent faster loading data into PostgreSQL - Various bugs fixes and improvements
Copyright © 2022 ETL-tools.com. All Rights Reserved.