SSIS Expression to Get File Name From Full Path

RIGHT( @[User::FullPath] , FINDSTRING(REVERSE( @[User::FullPath] ) , "\\", 1) - 1)

Here is what it does:

• Reverse the full path so the last backslash (\) becomes the first one.
• Get the position of the first backslash in the reversed string and this way we would know the length of the file name.
• Use the RIGHT() function get the file name since we know its length.

Error – Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

I was using office interop assemblies to do some customization on an excel report the process generates, I was able to do everything I wanted with the excel file on development machine and it worked just fine, but when I deployed to production I started getting the error “Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).” When I run the process through a SQL job, I used SSIS 2012 with the project deployment model but this applies to any process using the office interop assemblies.

Although the process was running fine when I login with the same user executing the job and execute it from visual studio I was still getting the error when it runs through a scheduled job, I had to spend a good time researching the issue and found the answer over few forums so I though to combine everything in one place.

Here is what I did to fix the issue, hopefully this works for you as well:

• Install Interop Assemblies: make sure either Microsoft office installed  with .NET Programmability Support feature selected or install the assemblies directly from microsoft website.
• Assemblies Version: check the version of the assemblies on development and production machines, the assemblies will be in the GAC, in widows 7 this folder is %windir%\assembly. of course the versions should match.
• Desktop Folder: the service uses the desktop folder under systemprofile so you will need to create this folder if not there, here is the location of the folder:
  • For 64 bit applications : C:\Windows\SysWOW64\config\systemprofile\Desktop
  • For 32 bit applications : C:\Windows\System32\config\systemprofile\Desktop
• DCOM Running User and Permissions: you need to find the excel application entry in the component services console and set the security and identity for it:
  1. First if the user running the service is a domain user make sure it’s added as a user on the server machine.
  2. In the run window type dcomcnfg.
  3. Expand Component Services –> Computers –> My Computer –> DCOM Config.
  4. look for Microsoft Excel Application or CLSID {00024500-0000-0000-C000-000000000046}, if you can’t find it then it’s possible that you are running 32 bit office over 64 bit machine, look at the end of the post for steps to find it.
  5. right click the item and select properties, then select the Security tab.
  6. select Customize for Launch and Activation Permissions and Access Permission and add the user running the service then give it full access.
  7. go to the Identity tab and select This User option and add the user running the service then type it’s password.
  8. click Ok then restart your service.

this step should do it for you, if not you can try the next one.

• COM Security: you need to set the permission explicitly for the user running the process, here is how to do it:
  1. In the run window type dcomcnfg.
  2. expand Component services –> Computers  then right click on My computer and select Properties.
  3. Select COM security tab and in the Launch and Activation permissions panel click Edit Defaults.

  

• add the user executing the service and give it Local Launch and Local Activation permissions.
• Quit Application: this might not be so much relative but make sure you release the instance of Excel.Application object in your code using the Quit() method, this way your process will not lock the file.

*If you couldn’t find the excel application in the DCOM config then try to find it in the 32 bit console as following:

• in the run window type mmc -32.
• go to file and select Add/Remove Snap in…
• Select component services and click Add.
• click OK then continue from point number 2 above.

I might have been very generous with the permissions here but I found out it has to do with the domain security policy and your server setup, so after you get this to work I suggest you go back and narrow down the permissions to the minimum that keeps you application running.

SSIS – Importing an excel file with over 255 columns

One of the several limitations comes with the excel driver is the number of columns you can import, you will find that excel only reads the first 255 columns of the file and ignores the rest, this is often not a big issue as most of the files you work with will have less than 255 columns but when it has more then it’s a real issue as there doesn’t seem to be an easy workaround for it.

The best way I found to get around this is to use two data source components, each one reads a different set of columns then use a merge join transformation to combine the data together again, this approach works based on the fact that excel will read both sets of data in the same order they are in, which I found true after trying this on different files, here is how it works:

1. Add an excel sources to the data flow, set the data access mode to SQL command and set the command to “Select * from [Sheet1$A:IT]“, this tells the driver to return the data for all columns between A and IT which will be the first 255 columns, I’m assuming here the sheet name is Sheet1.
   
   
2. add another excel source, configure it the same way you did the first one, you can reuse the same connection manager but make the SQL command  “Select * from [Sheet1$IU:MA]”, my last column was MA here so you will need to replace MA with the last column in your sheet.
3. now we need to add an identity column to each data set so we can merge them together in the next step, we will add a script task and add the identity column as an output column then inside the script assign an incremental values to it, again the data order will not change between the 2 sources, so the first row in the excel file will take the value 1 as identity on each side.
   
4. in order to use the merge join we need to sort both of it’s inputs, so we add a sort task on each side and set it to order the data based on the identity column we added earlier in the script task.
   
5. now add the merge join and join both outputs.

this way you will have all the columns in the data flow then you can continue processing you data as normal.