Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts

Wednesday, March 19, 2014

Configure SQL user to Write to the Event log

The case scenario is : I'm using a SSIS Script task to zip a large number of files and I want to log the 7zip errors to the Event log using System.Diagnostics. Here is the procedure I use to allow the SQL user to write to the event log.

You will need wevtutil normally found in %system32%, if not available you will have to use the registry editor.
1. configure credential for the user
2. determine permissions for the application log : wevtutil gl application > D:\temp\out.txt
3. open the file out.txt :
           name: application    
           enabled: true    
           type: Admin    
           owningPublisher:    
           isolation: Application    

            channelAccess: O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)
   
            logging:
  logFileName: %SystemRoot%\System32\Winevt\Logs\application.evtx
  retention: false
  autoBackup: false
  maxSize: 20971520
  publishing:
  fileMax: 1

4.          Using the Registry Editor (see original post  from Beth Massi)

You can allow any authenticated user write access to your log or you can allow the specific user SID write access. Open up the registry editor to your event log CustomSD key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Eventlog\MyLogName\CustomSD

To allow all authenticated users write access add the following security permission:
(A;;0x0002;;;AU)
To allow a specific user write access you would specify their SID:
(A;;0x0002;;;SID-OF-USER-ACCOUNT) 
5.       Modify the registry using wevtutil 
 wevtutil sl Application /ca:<updated channel line>

 Example to give read/write access to authenticated users will will use the 0x3 value:
 wevtutil sl Application /ca: O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)(A;;0x3;;;AU)

Monday, January 27, 2014

SSIS Parameter cannot be derived from sub-select queries


If you’re trying to use a parameter in a sub query using the SSIS SQL task you may encounter this error : image

Edit the SSIS Execute SQL Task and check the ByPassPrepare option
Currently it is set to false, and we generate the error above.


Set the Set the ByPassPrepare option to True:
  image
The task will now execute successfully !!

Thursday, October 18, 2012

SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED

just published "another ssis package" on one of our servers runnig Windows 2008R2.
Go the following error :


Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.  End Error  Error: 2012-10-18 15:43:10.76     Code: 0xC00291EC     Source: create xls file for  Execute SQL Task     Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  15:43:08  Finished: 15:43:10  Elapsed:  2.309 seconds.  The package execution failed.  The step failed.

 the fact is using 64bits you cannot use Microsoft.JET.OLEDB.4.0.
To work around this open the job step properties ans choose the execution option tab.

then check the following option below left of the properties window.


here is its , now you can you Jet Oledb for excel without issue !

Friday, July 20, 2012

Ace oledb cannot be found with oledb connection (ssis)

Currently on my workstation I have Office 2010 64 bits, and Ace oledb driver 64 bits. Working in BIDS I could not find the Ace driver available to create a new Oledb Connection.

Since BIDS is 32 bits I had to :
  1. Remove Ace driver 64 bits
  2. Remove 64 bits Office Products
  3. Reinstall Ace 32 bits
  4. Reinstall Office 32 bits (!)
  5. In Bids, choose Project Properties and  in "Debugging"  set Run64bitsRuntime to "False".
Hope this helps :)

Monday, July 16, 2012

stop ssis package when a For Each loop enumerator is empty

Problem : You want to stop a package when a For Each Loop Enumerator is empty.

Solution : Add a OnWarning event to the for each loop checking for right Error code.

Steps :

1. Add an OnWarning Event handler to the For Each Loop Enumerator:

2. Add A Script component to the Event handler that can read the following system variables:



3. Add the following code to test if the For each loop is empty:


Thursday, July 12, 2012

SSIS Error : Failed to decrypt protected XML node DTS:Password" with error 0x80070002

error Message :
 Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error 

 I've had the same error and just solved it.
As well as changing the protection level of the packages to DontSaveSensitive, you also need to make sure that anywhere within the package that points to a network drive i.e. all connection managers, and within any execute package containers, all the the file paths do not start off with the letter of the drive, instead use the server name.

i.e. K:\foldername\inputfile.xls

becomes: -

\\ServerName\foldername\inputfile.xls