Saturday, December 31, 2016

Synchronous and Asynchronous transformation



Synchronous transformation is quicker than asynchronous.
But merge join is faster than lookup sometimes, while merge join is asynchronous.
"A asynchronous component requires all the rows from the upstream component
before it begins processing the rows, to do some modification on the data,
and ususally generates a differant number of rows.


In a way, they act as both destination and source, thus they generate
different LineageID for the output columns. Since the mappings in the
downstream is done using the LineageID you need to restore these mappings."



Blocking, Non Blocking and Partial blocking


Non-Blocking
Audit
Character Map
Conditional Split
Copy Column
Data Conversion
Derived Column
Import Column
Lookup
Multicast
Percentage sampling
Row count
Row sampling
Script component


Partially Blocking
Data mining
Merge
Merge Join
Pivot/Unpivot
Term Extraction
Term Lookup
Union All

Blocking
Aggregate
Fuzzy Grouping
Fuzzy Lookup
Sort


Protection Level in SSIS

Protection Level

Error: 

Error: 0xC0202009 at Package, Connection manager "runeet2k8.sa": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'sa'.".

Solution: 

For providing protection to package , use Protection level as 'Dont savesensitive', which will clear the password from our connection manager,
which will cause our package to fail for login details of connection manager,
for managing this , we can use configuration file, and set password value while executing package through configuration file.

Use EncriptAllWithPassword, when need to give password to open a package

Delay validation


SSIS validates package to ensure that they will execute properly.Sql server  validation occurs both as package is opened(design time validation) and just before the package executes(run time validation).

Design time validation can be performed by seting work offline option.
Run time validation can be stopped by setting delay validation property as true for the package, which will validate during run time.

Delay validation of a single component in Data flow can be set by setting property ValidateExternalMetaData property to false.

Problem- Data Driven subscription-


Not able to create subscriptions for SSRS reports as it give the following error "Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid"






Solution-

This error is due to the Windows authentication used for Data source Connection. SSRS requires a Login to connect to the data source to process the report when subscription will be occurring at its scheduled time. 
For this, it is required to store the credential in to Report Server or deploy the Data source with SQL Server Authentication. Use Custom Data Source and save the credential.





















Friday, December 30, 2016

How to connect to MySQL database from SSIS ?

Solution-

1.       Either using ODBC driver
2.       Using .Net SQL client Connector

Using ODBC Driver-

·         Install newer version of ODBC Driver for MySQL 
·         ‘Mysql-connector-odbc-5.2.6-win32’ for Windows with 32 bit.
·         ‘Mysql-connector-odbc-5.3.1-beta-winx64’ for Windows with 64 bit.

Using Dot Net SQL client Connector to MySQL

·         Install latest version of .Net connector for MySQL
·         ‘mysql-connector-net-6.8.3’

Using ODBC Driver---

After installation of ODBC driver, go to Systems and Security->Administrative Tools- > ODBC Data Sources
















In SSIS-


Using Dot Net SQL client Connector to MySQL ----In SSIS-