Friday, May 8, 2015

Powershell copyhere Behavior Differs between Integrated Development Environment and Integration Services Catalog

Quick Description:  in Powershell, shell.application.copyhere works in a script called by SSIS when executed from SQL Server Data Tools.  It does not work when executed from the SSIS catalog when deployed to SSIS/SQL Server.

Do not try to use shell.application copyhere as a way of unzipping a file in a powershell script called by SSIS.  It will work in the IDE, it will work executing from the Integration services catalog, and it will fail spectacularly as a sqlAgent job.  Apparently, copyhere only works when called as a script for regular files, and only works for zip files when it feels like it (I'd argue that executing from the SSIS catalog isn't exactly interactive, but apparently, I'd be wrong).

Thursday, May 7, 2015

SSIS Attunity Oracle Source SQL Command from Expression Difficulties

Short Description:  When attempting to use a variable/expression to populate the SQLCommand, the expressions property is not showing up when looking at source properties.

The post title is a mouthful, but the upshot is that I ran into the unfortunate situation in which, when attempting to use a variable as the SQL Command for an Attunity Oracle source, the expressions menu wasn't showing up in the Oracle Source properties.

I'm not sure if this is always the case.  It certainly seems that the expectation is that the expressions property will show up under 'Misc Properties' per a number of posts like this one If you're not specifically having trouble setting a variable as the SQL Command because the expression properties are not available, you may want to start with this post, it will answer the basic questions around setting the query from an expression.

Solution:  If you're stumped because you're used to setting variable expressions as the query for data sources, but you can't find where to enter the expression, then we have/had the same problem.  Right clicking the Oracle Source and combing through the properties comes up blank for misc properties and the expressions property in particular.

In this case, to get to the expressions property of the source,

  • Right click the working surface of the data flow task 
  • Click on the ... icon in the Expressions property of the data flow task.
  • The Property Expressions editor will come up
  • Click the drop-down arrow in the Property drop down
  • Select the [Oracle Source].[SQLCommand] property, and enter your variable.
I know...this would be easier to follow with screenshots.  I hope to pull some together when I have a chance.

Friday, April 24, 2015

SSIS Flat File Text Showing Up in Quotes Although Column is Set to Qualified

Short Description:  An SSIS package using a flat file datasource with columns set as TextQualified = true come through showing the text qualifier (values are in quotes or some other qualifier) in the destination (database, another flatfile, etc).

Problem:  This is just a case of a two part setting being in two oddly different places.  A column is set to TextQualified under Connection Manager Editor, Advanced, Column Name, TextQualified (true).  The Text Qualifier itself is set under The file connection's properties.  Text pulled from a flatfile will still show up in quotes (or surrounded by any other qualifier) even though TextQualified=true has been set.

Solution:  In addition to enabling the TextQualified setting at a column level.  The qualifier has to be specified at the connection properties level.

To set the Text Qualifier:

  • In SQL Server Data Tools.
  • Right click the Connection Manager and select Properties
  • In the Properties pane on the right hand side, under Misc
  • Enter your Text Qualifier (e.g. ") in the TextQualifier property.

Wednesday, April 22, 2015

SSIS Code Page Error Moving Flat File to OLE DB destination

Short Description:  When creating an SSIS dataflow moving data from a flat file directly to an OLE DB destination, the OLE DB destination throws a code page inconsistency error.

Problem:  An OLE DB destination that is fed by a Flat File Source which is in turn fed by a Flat File connection throws an error "Error 2 Validation error. Stage Sent Data: Stage Sent Data: The column "MyColumn" cannot be processed because more than one code page (65001 and 1252) are specified for it."  SQL server is by default Code Page 1252, and Unicode is 65001 for a text file. if you don't put the output columns through a Data Conversion transform before they get to the OLE DB destination, they will fail with this error.

Work Around:  I say work around because changing the Code page of the flat file connection does not actually result in the Code page staying saved, but it does result in the OLE DB destination accepting the input.

To Work Around:

  • Open the Flat File Connection, and change the Code Page from '65001 (UTF-8)' to '1252 (ANSI - Latin I)'
  • Click OK to save
  • Open the OLE DB Destination which is showing the error.
  • Close the OLE DB Destination which is showing the error.
  • The OLE DB Destination should now, now longer show the error.
  • For kicks, look at the Flat File Connection, and see that the Code Page is back to '65001 (UTF-8)'

Wednesday, February 4, 2015

Amazon SDK Samples Target Framework, Reference Mismatch

Quick Description:  After installing the Amazon SDK for .NET, a Visual Studio (in this case SSIS Script editor for 2012 (VS2010)) solution from one of the samples doesn't recognize the reference for AWSSDK.  In an SSIS script task, this is also an issue building a new solution using the SDK.  All usage of the namespace in the project's code generate 'type or namespace name 'Amazon' could not be found' errors.

Problem:  In this case, it was caused by a mismatch between the reference dll version and the project target framework version.  VS 2010 can't target Framework 4.5, so it should default to 3.5.  However, I'm just not really sure which dll the (AWSSDKAndSamples_2.3.18.0) are targeting by default, if you're using VS 2010 (script editor) because I tested on both a 3.5 machine and a 4.5 machine, and although both came up targeting 3.5, the .dll was mismatched on both.  I brought the S3 sample up targeting both 3.5 and 4.x and in both cases, had to remove the existing reference and add the correct one for the target version.

For Standard VS 2010.  To solve the problem in standard VS 2010, make sure both the Reference and the target Framework are the same version.  Logically, this should already be the case with the sample.

To check your Target framework, right click your project, and under Target framework, select either 3.5 or 4.x. (3.5 is the only option that is available for VS2010 and AWS SDK.

To add a new reference to the right framework (adding the reference appears to be the clincher, switching between target framework versions didn't help me).  Right click references, select Add Reference and browse for AWSSDK.dll.  If you chose a default install the .dll is in C:\Program Files (x86)\AWS SDK for .NET\bin\Net35 or Net45.

For a Script Task in SSIS (technically VS2010), unfortunately, I think you might be out of luck.  I was unable to get it to work in the script task.  If you are magic and got it to work, I would *love* to know how.  My workaround is probably not acceptable for most people, but will get you where you need to go.  I made sure 4.5 was installed on the SSIS box, and then wrote a console app in VS2013 to be launched by an SSIS Execute Process task.  It's not ideal.  The other option, of course, is to upgrade to SQL/SSIS 2014 which can target 4.5.

Monday, January 6, 2014

How to install a wildcard certificate on Dell DRAC

This does not seem to be well documented, but I have tested this procedure on RAC/DRAC/iDRAC 6 and 7 with Internet Explorer (versions 6,8,11), Firefox (version 26 Windows and Linux), Chrome (version 32 Windows and Linux).  The process used an Entrust wildcard certificate.

  1. Obtain a wild card certificate, intermediate certificates (if any) and private key from a Certificate Authority (CA).
  2. Change the default key size on the DRAC using racadm -i config -g cfgRacSecurity -o cfgRacSecCsrKeySize 2048
  3. Upload your private key using racadm -i sslkeyupload -t 1 -f private.key
  4. Create a text file (yourfile.crt) that includes, in this order, your wildcard.crt, a Carriage Return Line Feed (CRLF) and your intermediate.crt.  Remove any other Carriage Returns (CR) except for the CRLF between certificates.  If you are performing this on Windows, you will probably need use something other than Notepad to succeed.
  5. Upload the text file you created using racadm -i sslcertupload -t 1 -f yourfile.crt

Firefox generally would not succeed without the intermediate certificate authorities, while Internet Explorer (and Chrome on Windows) would work without issue.

Monday, September 9, 2013

Oracle PL/SQL search for repeated substring

Short Description:  Query to find instances of a substring immediately repeated in a single string.

Description:  the query below finds a substring repeated immediately after the first.  My current example is searching username data for records for which the first and last name are the same.

trim(SUBSTR(USERNAME, INSTR(USERNAME, ' ', 1, 1)+1))= trim(substr(USERNAME, 1, instr(USERNAME,' ')))