Set FTP password in SSIS

Written by Taylor Gerring on November 24th, 2008

Undoubtedly, you’re reading this because you’ve discovered that SQL Server Integration Services (as of SSIS 2008) will not allow you to set the password of an FTP connection through expressions. Fortunately, there is an easy workaround, that requires a simple Script Task. While not as simple as native expression support, it’s darn close. I’ve included C# code, but you may need to adapt to VB.Net if that’s your preferred flavor.

  1. Ensure a string variable is setup with the password. For this demo, I’m using the name, “FTPPassword”
  2. Add a Script Task to your package
  3. Edit the task
  4. On the Script page, click the elipsis for ReadOnlyVariables and check the box for User::FTPPassword.
  5. Click the “Edit Script…” button
  6. Change your entry point (Main, by default) to look like the below code. Save, close, and hit OK.
1
2
3
4
5
6
7
8
public void Main()
{
	ConnectionManager FTPConn;
	FTPConn = Dts.Connections["FTPServer"];
	FTPConn.Properties["ServerPassword"].SetValue(FTPConn, Dts.Variables["FTPPassword"].Value);
 
	Dts.TaskResult = (int)ScriptResults.Success;
}

A couple notes:

  • Ensure you update lines 4-5 to reflect the actual connection name. My example uses the name FTPServer.
  • Just to reiterate, my password is stored in the variable name FTPPassword. If yours is different make this change on line 5.

That’s it. Make sure you’ve got this task being executed before your actual FTP task and everything should work fine. Cheers!

Share/Save/Bookmark

Handling Embedded Text Qualifiers

Written by Taylor Gerring on November 12th, 2008

Note: This post assumes you’re working with SQL Server 2008. This can likely be adapted to SQL Server 2005, but the code is written in C#, which 2005 does not support.

It seems that SSIS can’t handle embedded text qualifiers when importing from a flat file. What is an embedded text qualifier? Let’s say you have a CSV file with a few fields. To thwart problems with commas inside your text fields causing confusion with your column delimiters, you implement a text qualifiers - typically double quotes. It makes a row look something like this:

"12036","Company Name, Inc.","555-555-1234","3.14159"

That’s fine, but what happens when the field also contains quotes, such as this:

"52665","Best "Kept" Secret Storage Facility","555-555-9876","2.71828"

Now, depending on how the parser interprets this line, it could see the double-quote before  “Kept” and expect a field terminator (a comma in our case). Well, it so happens, that for whatever reason, Integration Services exhibits this type of behavior and will fail on this type of line. It’s a well-documented bug and oft-requested fix, but even as of SQL Server 2008, the issue is still present (Note: This apparently should be a supported configuration according to RFC 4180). Ideally, you can simply import a different format - using a different text qualifier is probably the easiest change (ever tried the thorn?). If this is not feasible, a variety of other solutions have been suggested, the most flexible being, writing your own Script Transformation to custom-parse the rows.

The setup is simple: configure the Flat File Source to import the whole record as a single field. Pipe that output to your script component, and then connect that to the destination database or rest of your process. Here’s what my Data Flow Task looks like:

Data Task Overview

Data Flow Task Overview

And here are the settings I used for my Flat File Connection:

  • General
    • Text qualifier: <none>
    • Header row delimiter: {CR}{LF}
  • Columns
    • Row delimiter: {CR}{LF}
    • Column delimiter: [blank]
  • Advanced
    • Name your column. My example user the unimaginative name, “line”.
    • DataType: string [DT_STR]
    • OutputColumnWidth: 4000

And the steps to get started:

  1. Go ahead and add a Flat File Source to your Data Flow Task and configure it to use this Flat File Connection
  2. Now, place a Script Component on your Data Flow and select “Transformation” when prompted
  3. Drag the green output arrow from Flat File Source to Script Component
  4. Edit the Script Component and switch over to the “Inputs and Outputs” page
  5. Rename the output to “Match Rows”, or anything else of your choosing.
  6. Set ExclusionGroup = 1
  7. Add the correct output columns, with necessary names and types. This is very important, yet annoyingly mundane. Add an extra column for the line number with type of “four-byte signed integer [DT_I4]“. I called mine MatchLineNum.
  8. Add another output and call it “Error Rows” or anything else of your choosing.
  9. Set ExclusionGroup = 1
  10. Add only two columns, ErrorLine (string [DT_STR] 8000) and ErrorLineNum (four-byte signed integer [DT_I4]).

Now, a quick explanation on what all this was for: Because we are only importing the whole row, we want to configure the Script Component to output the individual columns. We did this part when we completed steps 5-7. Next, we configured an additional output for rows that we are not able to successfully parse. This is why we defined the Error Rows output in steps 8-10. Be aware, that if you’re supremely confident that you can parse every row, you could optionally skip the creation of the Error Rows output, but I would advise against it.

Now, we will create the actual script to parse the rows manually.

  1. Switch back to the Script page and click the “Edit Script…” button. This will open up a script editor that looks like a stripped-down Visual Studio.
  2. Take note of the code below, and I will explain it below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/
 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
 
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    //Declare LineNum to keep track of the number of rows
    int LineNum;
 
    public override void PreExecute()
    {
        base.PreExecute();
 
        //Initalize LineNum
        LineNum = 0;
    }
 
    public override void PostExecute()
    {
        base.PostExecute();
    }
 
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        //Increment LineNum  to keep track of the number of rows
        LineNum += 1;
 
        //split the input column. This will be highly dependent on the format and will probably need adjustment
        string[] columns = System.Text.RegularExpressions.Regex.Split(Row.line, "(?<=\"),(?=\")");
        //If the number of elements is not expected, we assume there was a problem
        if (columns.Length != 4)
        {
            Row.ErrorLine = Row.line;
            Row.ErrorLineNum = LineNum;
            Row.DirectRowToErrorRows();
        }
        //Everything looks good, let's move forward
        else
        {
            Row.MatchLineNum = LineNum;
            Row.ID = StripQualifier(columns[0], "\"");
            Row.Company = StripQualifier(columns[1], "\"");
            Row.PhoneNumber = StripQualifier(columns[2], "\"");
            Row.FavoriteIrrationalNumber = StripQualifier(columns[3], "\"");
            Row.DirectRowToMatchRows();
        }
 
    }
 
    public static string StripQualifier(string InputString, string Qualifier)
    {
        //This is a helper function only to remove surrounding text qualifiers
 
        string OutputString;
 
        if(
            InputString.Substring(0, Qualifier.Length) == Qualifier
            && InputString.Substring(InputString.Length - Qualifier.Length, Qualifier.Length) == Qualifier
          )
            OutputString = InputString.Substring(Qualifier.Length, InputString.Length - (2 * Qualifier.Length));
        else
            OutputString = InputString;
 
        return OutputString;
    }
 
}
  • Line 14: Declare LineNum for counting our position in the file.
  • Line 21: Initialize LineNum to 0. In the above script, we increment before parsing, so the first record will have a LineNum of 1. If you happen to have a header row and would prefer it to be 2 to coincide with the actual lines in your flat file, feel free to change the initalization to -1.
  • Line 31: Increment LineNum for keeping track of our position in the file
  • Line 35: This is the real nugget to the whole post. It utilizes regular expressions to correctly parse out the row, even with an embedded text qualifier. This is a  fairly simplistic implementation. If you need to tweak the parsing routine to your situation do it here!
  • Line 37: In my example I am expecting 4 fields, so I check that my string array contains four entries. If not, I assume it’s just a bad row and will inspect later.
  • Line 39-40: Set the appropriate output columns.
  • Line 41: Force the rows to the “Error Rows” output. Remember that extra output we created? It’s specifically for this situation, so we can redirect the “bad” rows to a different location.
  • Line 44: If we have 4 elements in our array, assume the best! You probably want to perform additional sanity checks here, including wrapping the whole thing in a try-catch block, but for our example, I’m keeping it simple.
  • Line 47-50: Set the appropriate output columns
  • Line 51: Redirect output to “Match Rows”
  • Line 56-71: This is simply a helper method to strip the text qualifiers off the text. Discussing it is outside the scope of this post, but I’ve tested it and it works fine.

Be sure and build the script (Ctrl+Shift+B or use the Build menu) before closing out. If not, you will receive a validation error. After a successful build, close and click “OK” on the Script Transformation Editor dialog. You can now connect the outputs from your Script Component to any destination, be it an OLE DB Destination or another flat file. The important thing to note is that you connect the Error Rows output to analyze any potential problems. You have access to both the record number and the original line read in. This should provide you with sufficient information to start analyzing the problem, and hopefully fix the parsing routine in the Script Component to deal with it.

Note, that performing this extra Script Component severely affected the processing rate versus SSIS natively supporting the import. Again, the best solution would be to correct the source, if possible.

Share/Save/Bookmark

Query Active Directory from SQL Server (again)

Written by Taylor Gerring on November 4th, 2008

Unlike my last post on the issue, instead of simply copping out and pointing to another blog for instructions on how to query Active Directory from SQL Server, I’ll provide some useful tips on getting things all working.

Firstly, make sure CLR is enabled:

USE master
SP_CONFIGURE 'clr enabled', 1
RECONFIGURE WITH override

Next, extract and open up the .sln file included in MSADHelper2.rar and build the project (Ctrl+Shift+B). Drill down to the MSADHelper2\bin\Release directory and copy the resulting MSADHelper2.dll file to a location of your choice - I dropped it right in C:\Program Files\Microsoft SQL Server\. Note the location, you’ll need it soon.

Now, copy the script from Igor’s post and place it in a new query window in SSMS, but DON’T RUN IT YET! For the installation section, I recommend that you run each statement by itself, to catch problems early. I’ll step through each statement with you.

  • Lines 1-4: Set connection settings
  • Line 14: Update the path to point to the location of MSADHelper2.dll and run to create one key
    • If you receive an error here, especially about memory or unable to load/register, restart the  SQL Server service and start over
  • Line 16: Run to create another key
    • The path may need to be updated if a .NET 2.0 version change occurs. If you get an error about not locating the file, verify the path. Likely, the framework version you have installed is different from 2.0.50727
  • Line 22: Create a login based off the first key
  • Line 24: Create another login based off the second key
  • Line 28: Tell SQL Server it’s okay to run unsafe assemblies from the first login
    • Why UNSAFE? Because the assembly accesses a resource that SQL Server cannot make guarantees about
  • Line 30: Tell SQL Server it’s okay to run unsafe assemblies from the second login
  • Line 39: Update to reflect the database where you want the CLR UDFs to reside, and run it
  • Line 42-44: Run this, but keep in mind the note about .NET versions made in Line 16
  • Line 46-48: Update the to the same provided in Line 14
  • Line 59-116: These simply define the UDFs, If you want to change the schema or naming convention, this is the time to do it.
    • The reason why I recommend running these lines as a batching instead of individually, is that if everything else above this worked, these should also work without a problem
  • Line 121: Run just to test everything out… That’s it, you’re done

A couple caveats:

  • Figuring out the correct provider path is always a problem for me. Be sure to check with your local AD admin and straighten that out - more on this in the next bullet point
  • Locating the correct OU or property
    • You can try using these functions to locate the correct OU or property, but to be completely honest, you’re much better off installing the Windows Server 2003 Service Pack 1 Administration Tools Pack. With this, you can open the MMC snap-in for Active Directory Users and Groups (located inside the Start Menu, Administrative Tools folder).
      1. Once installed, open up that ADUG snap-in and drill down to a user (any user you want).
      2. Bring up their properties (double-click), select the “Member Of” tab, and you can see the correct DC and OU settings.
      3. For example, Active Directory Folder, “DOMAIN.COM/Users/Awesome” would translate to something like this:
        LDAP://OU=Awesome,OU=Users,DC=DOMAIN,DC=COM
  • Once you have the correct provider information, try out Line 125-127. If all is well, you should get a Status = Passed.
  • Memory errors
    • I am dealing with this issue on one specific server, receiving messages like, “Could not load file or assembly ‘System.DirectoryServices”. I haven’t figured out a solution yet, but will be sure to update this post when I do.
  • Speed
    • Not sure what the bottleneck is, but I would not recommend trying to perform lookups live. You’re much better off caching a copy in a local table and refreshing it nightly. Just create the destination table and
      TRUNCATE dbo.ADUsers
      INSERT INTO dbo.ADUsers EXEC dbo.usp_GetUserList
  • Properties
    • There is often quite a bit of information stored in Active Directory. Be sure to grab important things like sAMAccountName and displayName and remove things like memberOf, depending on your specific needs.

That’s all for now. Got any comments or questions? I’d be happy to assist, just drop a comment in below.

Share/Save/Bookmark

Using Powershell to generate build scripts

Written by Taylor Gerring on October 31st, 2008

I have a project where each object creation script is stored in a separate file. Between the tables, views, linked servers, stored procedures, functions, jobs and schemas, this results in roughly 400 objects and therefore 400 files. Trying to run these separately for a new installation simply is not feasible; I needed a quick, automated solution to combine all files into a single build script, while at the same time easily control the order of creation so dependent objects can be created in the correct order.

The solution I settled on was to simply store the list of files in the correct order inside a plain text file. I can then use a batch or shell script to compile a single, larger file. Instead of relying on an archaic DOS batch file, I decided to teach myself a bit of Powershell. It has a considerable amount of push from Microsoft to be bundled starting with their 2008 series of server products (SQL Server 2008, Windows Server 2008 R2). It’s also a free download supported on Windows XP forward. With projects like PASH, you might even say Powershell is a cross-platform solution.

After very little Googling and trial and error, I was able to whip up a small, but effective build script that uses my even-simpler build list. Below is the result of this (save the code to a BuildScript.ps1 file):

1
2
3
4
5
6
7
8
9
10
$BuildList = "BuildList.txt"
$OutputFile = "BuildResult.sql"
 
if (Test-Path $OutputFile) {
	Remove-Item $OutputFile
}
 
foreach ($file in Get-Content $BuildList) {
	Get-Content $file >> $OutputFile
}

This is a very simple script, but let’s pick it apart:

  • Lines 1-2 simply declare variables and set their values according to your input and output filenames. $BuildList should point to a file that contains the paths of files separated by line. Each line (that lists a file) can have a subdirectory, or point anywhere really.
  • Lines 4-6 checks if your output file exists, and if so, delete it.
  • Finally, 8-10 loops through the contents of your build list, storing each iteration in $file. Within the loop, we read the entire contents of the file and append it to your destination build file.

If this is your first time using Powershell, take a quick read of the Microsoft Technet article, Windows PowerShell: Securing the Shell. This will give you a necessary primer on how to get scripts running. Due to intentional security considerations, it’s not as easy as double-clicking a file.

Share/Save/Bookmark

Testing Object Permissions with Impersonation

Written by Taylor Gerring on October 28th, 2008

As either a DBA or DBD, undoubtedly, you’ve needed to create a user account with very specific permissions. Especially when that account is based on Windows authentication, it can be particularly difficult to test and verify the correct permissions were assigned. However, if you have the access - say, sysadmin - you can simply impersonate the account yourself and verify everything is set correctly. Here’s how:

--display the current user we're accessing as
SELECT suser_name(), original_login() --DOMAIN\YourAccount, DOMAIN\YourAccount
--here's the actual impersonation. this can just as easily be a normal SQL account
EXECUTE AS USER = 'DOMAIN\AnotherAccount'
--display the current user we're accessing as. note that SUSER_NAME() is the account specified above
SELECT suser_name(), original_login() --DOMAIN\AnotherAccount, DOMAIN\YourAccount
/*
run test cases here to verify correct settings
*/
--this steps us back out to the normal context
revert
--verify that we've reverted back to ourselves
SELECT suser_name(), original_login() --DOMAIN\YourAccount, DOMAIN\YourAccount

SUSER_NAME() returns the login identification name of the user. ORIGINAL_LOGIN() shows the login of the user actualyl connected to SQL Server. REVERT repeals the context back a level. Note, that if you EXECUTE AS multiple times, you’ll have to issue a REVERT for each level. That’s because when you EXECUTE AS within an impersonation, you’re actually doing it as the impersonated account (doesn’t that seem obvious?). Of course, you’ll only be able to EXECUTE AS if that impersonated account has the correct access.

What happens if you try to impersonate an account in a database where they don’t have access?

Msg 916, Level 14, State 1, Line 1
The server principal "DOMAIN\AnotherAccount" is not able to access the database "TestingDB" under the current security context.

A quick note on security groups: If you’re testing access against a security group, you must impersonate a user within that group. If you try and EXECUTE AS USER = ‘DOMAIN\GroupName’, you will receive the following error:

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "DOMAIN\GroupName" does not exist, this type of principal cannot be impersonated, or you do not have permission.

EXECUTE AS and REVERT are great tools for troubleshooting permissions errors whether they be from a user receiving an error or application login that needs to be locked down tight.

Share/Save/Bookmark