SSIS Cannot cast division to float DT_R8 using Derived Column

This short blog shows you why you cannot cast the division or int float DT_R8 using Derived Column.

I have a column ttMove is Integer and the value is 465. So, 465/60 should be 7.75.

I was putting the Expression (DT_R8)(ttMove / 60).

You need to remove the parenthesis () in order to get 7.75. So the right Expression should be (DT_R8)ttMove / 60 .

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100’.

This shows you how to resolve the error “Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100’.” in SSIS. This message actually does not make any sense to anyone.

I was looping through my Excel Worksheet in SSIS and I got the following message.

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{D1A469BC-F371-4D15-832F-BACB50C162E9}’ failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Again, the message did not make any sense to anyone. So I tried to set the TargetServerVersion property for SSIS Project. Somehow, it was working fine.

Do not just follow me and set SQL Server 2014. You must know the version of SQL Server that you are using.

SSIS Connection property has not been initialized

This short blog showing you one of the possibilities to resolve the error message “Connection property has not been initialized” when using the Script Component in SSIS package.

In SSIS package, the Script Component is added, and the Connection Manager is added like below.

In the Script Component C# code you have the following.

public override  void AcquireConnections(object Transaction)

{

   ConMgr = this.Connections.ConnectionQc;

   Targetconnection = this.Connections.ConnectionQc.AcquireConnection(null);

   SqlConn = Targetconnection as SqlConnection;

}

When you run then you get the error “Connection property has not been initialized”.

What is wrong here is that your Package Connection Manager could be using

Native OLDE DB\SQL Server Native Client 11.0

Change the Connection Manager Provider to

.Net Providers\SqlClient Data Provider

This will resolve your problem if you are having the same scenario as I do.

What is Windows 10 Creators Update

I was looking for Windows 10 Creator edition as  mentioned in Microsoft Build 2017.

Hence, I was finding a copy of Windows 10 Creators in  MSDN subscription but unfortunately I could not get this edition.

I was read this link, https://blogs.windows.com/windowsexperience/2017/04/11/whats-new-in-the-windows-10-creators-update/,  it has written “We are excited to announce that today, the Windows 10 Creators
Update has started rolling out to Windows 10 customers around the world as a  free update.” And again it didn’t say where to get a copy of Windows 10  Creators edition.

Then I came to another website “Windows 10 Creators  Update review: Microsoft adds fun to its flagship OS” and again when to get this  Windows 10 Creators Update.

Finally, I get this link https://support.microsoft.com/en-us/help/4014916/windows-10-choose-your-privacy-settings-for-windows-10-creators-update resolve my question.

Windows 10 Creators Update (also known as Windows 10, version 1703). You can find Windows 10, version 1703 in  MSDN.

Enable and Disable Proxy in IE using command

This short blog show you how to Enable and Disable Proxy in IE using command

Despite Chrome and Edge claim the best browser but in Windows environment there yet need to rely on IE to set the proxy.

It is tedious to open the IE setting to set enable or disable the proxy while you are changing network connection in some of the company.

The easier way I found is to use command.

To Enable

reg add “HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings” /v ProxyEnable /t REG_DWORD /d 0 /f

To Disable

reg add “HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings” /v ProxyEnable /t REG_DWORD /d 0 /f

Reference:

https://superuser.com/questions/419696/in-windows-7-how-to-change-proxy-settings-from-command-line

SSIS Loop Excel Workbook

This blog is to show you how to loop through an Excel Workbook using SSIS.

To loop through Excel in SSIS you need some tips and  tricks.

The expectations of this article are:

1. You have installed SQL Business Intelligence Development Studio (BIDS) in your Visual Studio 2015.

2. You know ha blank how to create a blank SSIS project.

To start with.

1. Create a blank SSIS project.

2. Drag a Data Flow Task from SSIS Toolbox to the  page.

 

3. Drag Excel Source to the  page.

The Excel Source Editor opened.

4. Click new to connect to the Excel file.

5. Select the Excel file and click  OK.

6. Choose Table or View in Data  access mode.

7. Choose the Worksheet in Name of the Excel  sheet.

8. Click on Columns to check the columns.

10. Click OK to complete the Data Source.

11. Drag the OLE DB Destination to Data Flow page.

 

12. Double click on the OLD DB Destination to configure  the Database connection.

13. Click New to create a new connection.

14. I have created a MS SQL Server northwind connection  so I am going to use that.

15. I duplicated the Products table to become Products1 table in northwind database. I choose Products1 table here.

16. Click the Mappings to see the columns mappings between Excel and Database table.

17. Ignore the ProducId because it is generated by the database.

18. Click OK to complete the Link Excel Source and OLD DB Destination using the
green arrow. 

19. Click on “SSIS  (menu item)” in Microsoft Visual  Studio.

20. Click on “Variables (menu  item)”.

21  Name the variable SheetName and select String as Data type.

22. Click the Control Flow tab.

23. DragForeach Loop Container intoControl Flow page.

24. Drag the Data Flow Task into Foreach Loop Container.

25. Click on “Collection (tree item)” in Foreach Loop Editor.

26. Click on “Enumerator ()” in Foreach Loop Editor.

27 Choose Foreach ADO.NET Schema Rowset Enumerator for the Enumerator..

28. “Connection: (combo box)” in Foreach Loop Editor.

29. “New Connection… (button)” in Configure ADO.NET Connection Manager.

30. Expand .Net Providers for OleDb.

31. Choose Microsoft Office 12.0 Access Database Engine OLE DB Provider.

32. Click on All.

This is very important.

33. Type Excel 12.0; HDR=Yes; in Extended Properties. Click Test Connection. Click OK.

34. Click on Variable Mappings choose User:SheetName that you have created earlier. Type 2 for Index. Click OK.

35. Click on Excel Source.

36. In the Properties, Access Mode choose OpenRowset from Variable.

37. Choose User:SheetName for OpenRowsetVariable  property.

You are good to run now.

The sample Excel Workbook you can download here: https://1drv.ms/f/s!AuZYHSwsevw1gm56VrA27ILqQ4GA

SSAS cannot find database after deploy

This short blog shows you where to find the SSAS database that you deployed.

After you deployed the SSAS cube in Visual Studio and you started SQL Server Management Studio then choose Analysis Services. You did not get to see the database you deployed.

The reason is, only the Administrator can see the SSAS database, I am not sure why is Microsoft doing that. It seems this is only way I can resolve this for now. This is SQL Server 2012, the newest version might have change the behavior, I am yet to try test.