SSIS Error: System.IO.IOException: The process cannot access the file "xxxxx\project.ispac" because it is being used by another process.

The .ispac file is locked by the debug process, which is a normal function of debugging. However, if the debugger crashes (or if Visual Studio itself dies/crashes) then it doesn't let you execute your SSIS package in Visual Studio.

You will likely get the error:
System.IO.IOException: The process cannot access the file "xxxxx:\Project.ispac" because it is being used by another process.

So! To fix this apparent insanity, you have to kill the processes (SSIS Debug Host and/or SSIS Debug Host (32 bit)) and restart Visual Studio.

Sometimes, it's the little things

Visual Studio 2013 editor highlights the current line by changing the background color of the current line.

By default, it's this hideously impossible to see also-black that looks like crud on my LED screen. Also, the fact that I'm using the dark theme means that also-black doesn't help when the highlight value is a grey value of 80% black.

So I changed it from the default to Navy for Active, and Olive to Inactive.

It's found in the Visual Studio 2013 menu at

• Tools\Options\Environment\Fonts and Colors\

The ones to edit are

• Highlight Current Line (Active)
• Highlight Current Line (Inactive)

Change all the SSIS package's protection levels, all at once

So, I have a scenario where I'm working in SSIS 2012 on SQL Server 2012 with SQL Server Data Tools 2012 (SSDT), and in order to ensure all the packages are both coming to me and going to others with the same level of protection and level of editability, I need to change all of the protection levels of all the packages in my solution, all at once. Somehow, somewhere, one or more of them got reset to a non-default value - or maybe, we need to pull one back from some other source - either way, at anything above about two packages, this gets to be a real pain.

I found two options:

Before you do anything else:

1. First things first, commit everything to source control, close SSDT, and make a zip backup of the whole project folder. 
2. Verify the backup.

Then:

1. Set or Change the Protection Level of Packages
1. http://technet.microsoft.com/en-us/library/cc879310.aspx
2. Now, open a command prompt at the folder with the packages you want to modify.
3. Next, create either a command window, or a command file (AKA batch file) with this:
1. for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;strongpassword
4. What I wanted instead was to set them to DontSaveSensitive, plus, I have spaces in the file names (radical, I know), so I used this:
5. for %f in (*.dtsx) do dtutil.exe /file "%f" /encrypt file;"%f";0 /Q
6. The /Q doesn't prompt to overwrite the existing package
7. You can find more about the DTUtil syntax here
1. http://msdn.microsoft.com/en-us/library/ms162820.aspx
2. Open the .dtproj file using your favorite text editor, and do a find and replace on some strings like
1. me="ProtectionLevel">3

So, for me, it was 

me="ProtectionLevel">3

and replace with 

me="ProtectionLevel">0

last, reopen and build

What I wanted built-in, part 2

I am a responsible coder. Everywhere I’ve worked, if they haven’t had source control, I have championed the install of it. Even when it wasn’t possible, I have still campaigned to get it, because we all need insurance.

However.

This sense of responsibility? Not so much when in my test lab, but still, I do check in and check out to provide myself sanity. And prevent myself from stepping on myself. I just don’t do it as often as I would, since I’m not worried about other people getting in there.

(Well, I am, but that’s what firewalls are for.)

So, in trying to connect to Team Foundation Server, I was reminded that I’m going to need to install two MORE packages just to chat with my own TFS install from SSMS.

First up, I have:

Microsoft Visual Studio Team Explorer
http://www.microsoft.com/en-us/download/details.aspx?id=329


Then, I have to install:

Microsoft Visual Studio Team Foundation Server 2010 MSSCCI Provider 32-bit
http://visualstudiogallery.msdn.microsoft.com/bce06506-be38-47a1-9f29-d3937d3d88d6


Or, if I were on SQL 2012, I would need:

Microsoft Visual Studio Team Foundation Server 2012 MSSCCI Provider 32-bit
http://visualstudiogallery.msdn.microsoft.com/b5b5053e-af34-4fa3-9098-aaa3f3f007cd

And this is just so I can connect.

I want this built in – why isn’t this software on a web share as a part of the server install, so I don’t have to go back out to the internet and get it?

Free zip code information

Free zip code information I found online:

• http://federalgovernmentzipcodes.us/
• http://geonames.usgs.gov/domestic/download_data.htm

Ctrl+R doesn't work

I had the following conversation with my VM the other day:

Why can't I push Control+R (Ctrl+R) in SSMS 2012, and have my query results window go away?  

I'm on a virtual machine here, Mr. SSMS, and I really don't need to see, again, that I just accidentally selected getdate().

In order to get Management Studio to give me back my full editing window, I went in to settings and reset the keyboard layout.

Like this:

1. Click Tools >> Options
2. Click Environment >> Keyboard
3. Click the Reset button
4. Answer Yes to the warning
5. Click OK to close the window
6. Exit SSMS
7. Start SSMS again
8. Select getdate();
9. Ctrl+R now works!

MS SSIS Wiki

You know, it's sites like this that make me wish I had a page crawler, that would self-organize a website into an eBook automatically. That would be a rather efficient way, I think, to carry around part of the Internet's brain trust in an eReader...



http://social.technet.microsoft.com/wiki/contents/articles/776.sql-server-integration-services-ssis.aspx#SSIS_Wiki_Pages

New Engagement

I recently started a new engagement with PotomacWave Consulting, and couldn't be happier!

I'll be doing SSIS, SSRS, SSAS, and SQL Server 2005 developer / DBA stuff.

Really excited about this change!

Expression Editor

I needed a way to test the logic of my expressions outside of BIDS. A quick Google search later, and I ran across this. It's worked wonders for me so far, in that it forces me to think through my test before I take the expression back to my package(s).

http://expressioneditor.codeplex.com/

Loading Error

Having an SSIS package loading error today. Can't figure out why it would get corrupted. Odd.

I'll try to post some screenshots a little later.

Book List: Task 2.5 - SSIS

So, let's review:

You put in what essentially amounts to userid/password, along with some keywords, and out comes XML. SSIS's webservice component should be able to handle this no issue, right?

Turns out, it should be. Instead, what you actually get is a known issue with the error "the selected web method contains unsupported arguments"

Check out this URL to see what I mean:

http://www.bigresource.com/Tracker/Track-ms_sql-hskONpQF/

So, this bummed me out. I thought "There HAS to be a way to do this!"

In searching for an answer, I also came across the MSN WSDL file, to feed into the SSIS webservice task. This was useful to prove that it wasn't just Amazon's file. You can find it here:

http://soap.search.msn.com/webservices.asmx?wsdl

Book List: Task 2.5 - Introduction

Here's what I want to do:

• Take a string that I suspect is the title of a book, like "Huck Finn".
• Send this title to Amazon's Web Service, the Product Advertising API, to see if it's valid.
• Receive a response indicating the validity of this string as a title using number of matches to validate.

First, I want to do this manually. That, in and of itself, is not quick or easy. First, you have to sign up for an amazon account. That was simple, I already had one. My account was also already an associate, so that helped - I had poked around their site a bit already.

Then, you have to go to

https://aws.amazon.com

and get your amazon.com account registered for Product API access.

That done, you then go back to

https://aws-portal.amazon.com

Where you can now access your security credentials, under the heading of "Access Keys". From here, you grab your two string-based access ID's, and save them off someplace. You'll need them again in a minute.

Now, head to http://associates-amazon.s3.amazonaws.com/signed-requests/helper/index.html

This is a web page that will allow you submit requests manually. It's a syntax checker! Woohoo! A button I can push! :)

Now, I put in the information, and it spits back the various information about the URL. This is cool! Copy and paste the URL from the bottom-most text box labelled "Signed URL" into a spare browser window, and POOF! You have a fully formed amazon result right there! Success!

Most of this information is covered in one form or another in the official "Getting Started Guide":

http://s3.amazonaws.com/awsdocs/Associates/2010-06-01/prod-adv-api-gsg-2010-06-01.pdf

So, let's review:

You put in what essentially amounts to userid/password, along with some keywords, and out comes XML. SSIS's webservice component should be able to handle this no issue, right?

Book List: Task 2

The next task in this project was to import the text files into a SQL Server 2008 DB.

To do this, I created an SSIS package. I will cover the package creation in another post.

The next step actually bears the point of this series, as attempting the validation of the title/authors has become the crux of the whole thing (as data validation usually is).

The basic problem looks like this:

Column_1 Column_2
Mark Twain Huck Finn
Tom Sawyer Mark Twain
Call Of The Wild Jack London
Arthur Conan Doyle Sherlock Holmes

In the case of Mark Twain and Tom Sawyer, they can both be valid author names - there's no special characters, etc.

So, I went looking for a validation method. My first thought was to use Amazon. That should be pretty simple, right? Just grab an API, plug it in, and away we go!

As it turns out? Not so simple to use Amazon from SSIS.

Book List: Task 1

Getting the book list itself was pretty simple, really, even though the physical files were spread over several devices and/or formats.

In order to make my life easier, I decided that a ANSI/ASCII text file would present the fewest problems. The easy way to do this, of course, seemed to be to go into each device/format and email the files to one PC, and then "Save As..." to get them all to the same physical disk.

It was, suprisingly, just that simple.

Book List

I have a list of books that I want to import into a DB, so I can sort them, group them, and just generally use them as sample data. This doesn't seem like it should be that big of a deal.

Right?

Okay, well, perhaps the list is a little unscrubbed because I both pulled some entries from the Internets and hand entered some others into this list. Still, it shouldn't be that big of a deal, right?

Ha! You'd like to think so, wouldn't you?

So, the simple version of the project plan looks like this:

1. Take multiple files, filled with some combination of

• Author
• Title
• Other Spacing / Whitespace

1. Import said files into a SQL 2008 DB
2. Validate that authors are really authors, titles are really titles
3. Run a single select to indicate the distinct list of books in the set
4. BONUS: Add a column indicating ownership status
5. BONUS: Add a column indicating "have read" status

SSIS /dev/null

While working with SSIS 2008 recently, I needed some random data. While I was poking around on the same site, I found this:

http://www.sqlis.com/post/Trash-Destination-Adapter.aspx

From their webpage:
When developing a package it is very useful to have a destination adapter that does nothing but consume rows with no setup requirement. You often want run a package part way through development, or just add a path so you can set a Data Viewer. There are stock tasks that can be used, but with the Trash Destination all columns are treated as selected automatically (usage type of read-only), so the pipeline knows they are required. It is also obvious that this is for development or diagnostic purposes, and is clearly not a part of the functional design of the package.



So, basically it's a /dev/null for SSIS. Cool!

SSIS Sample Data

While working with SSIS 2008 recently, I needed some random data. I found this:

http://www.sqlis.com/post/Data-Generator-Source-Adapter.aspx

From their webpage:
It generates random integer (DT_I4) and string (DT_WSTR) data and places them in the pipeline. You specify how many columns of each you would like and for any string columns you pass a fixed length value. You then need to specify how many rows in total you require to be generated.