Knowledge Sharing

Temporary tables are the temp space available in the database to store data for further modifications. It is extensively used in the TSQL programming which is very convenient for the programmers. When it comes to SSIS we need to deal it with differently as the temporary tables life time is for only one session. SSIS throws error if the dataflow task used TSQL with temporary table. For e.g create one data flow task with below query which uses temporary table #ContactAddress to store data from person.Contact table of Adventureworks. #ContactAddress is the source table for ContactAddress in which data comes from the SSIS package. give the below query in one SSIS task and load the data into the table. create table #ContactAddress ( contactId int, firstname nvarchar(100), ) insert into #ContactAddress select ContactID,FirstName from Person.Contact ERROR:   SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80040E14. An OLEDB record is available

In this post, I would like to explain how we can migrate data from existing data lake gen1 to gen2. Azure data lake gen1 is optimized for big data analytics but storage is costlier than the gen2. Before Gen2, only option to store the archival data was in blob storage because of this low pricing. Gen2 is built on top of blob storage and hence the cost of storing in Gen2 is very cheap. gen2 is the combination of blob storage and ADLS. Microsoft combined blob storage and ADLS gen 1 into one platform to come up with ADLS gen2. Please see the video below to understand the migration details.