Home - Jorge (El Che) Vasquez Blog

7/28/2008

While working on a project, I was asked to create and SSIS Package that would read a flat file (.txt) from a Network Drive onto a SharePoint Custom List. While most of the solution was straight forward, the one part that I was hoping to be the simplest, turned out to be a pain.

So, I created and SSIS Package, which would access a Network Drive and read a flat file. The SSIS Package worked great on my development environment, however, when I created a "SQL Job" and the only step in the Job was to execute the SSIS Package, I kept on getting the following error:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:23:06 PM
Error Code: 0xC001401E
   Source: Package Connection manager "Flat File Connection"
   Description: The file name "\\servername\folder\file.txt" specified in the connection was not valid.
End Error
Error Code: 0xC001401D
   Source: Package
   Description: Connection "Flat File Connection" failed validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

As I looked at the History of the SQL Job, it seemed that the Job was executing the SSIS Package by using different credentials : SERVERNAME\SYSTEM

After reading a few blog post, I encountered the following microsoft link, which listed a few options:

http://support.microsoft.com/kb/918760/

While informative, the above link did not provide instructions on resolving the issue, so here is a few steps that will help.

Under SQL, go to Security -> Credentials

Right click and select "New Credentials"
Provide credentials of a Domain User who has permissions to the Network Drive.

Under SQL, go to SQL Server Agent -> Proxies

Right click and select "New Proxy"
Under "Credentials", select the credentials that you created on step 1.
Under "Active to the following subsystems", check the checkbox that says "SQL Server Integration Services Package"

Under SQL, go to Jobs

View the properties of the Job, whose step is calling the SSIS Package.
Edit the Step that is calling the SSIS Package and under the "Run As" drop down, select the Proxy created under step 2.

That's it, run the Job once more and all is working as expected.

Posted at 2:40 PM by Jorge Vasquez | Category: Third Party Tools | Permalink | Email this Post | Comments (3)

5/29/2008

Under Armour Baltimore Marathon

As some of you may know, I always look for a challenge. Last year, I took a trip to the south of Argentina and performed a 12 hour glacier trek (http://www.chaltentravel.com/excursion.asp?exc_id=13&l=en).

So if you can walk for 12 hours, why not run a marathon. Hence, I decided to run the Baltimore Marathon(http://www.thebaltimoremarathon.com).

I've got 5 months to train and hope to make it all the way.

Posted at 9:36 AM by Jorge Vasquez | Category: Hobbies | Permalink | Email this Post | Comments (3)

5/14/2008

SharePoint and SQL Cubes

It has been an extremely busy couple of months, since I have been back from the Seattle SharePoint Conference. And man, it has been a while since I posted anything.

At the Seattle SharePoint Conference, there were many presentations on BI Solutions. These BI Solution presentations spanned across a wide variety of topics, however, they ended up on the same note. Creating solutions that would allow power users to easily report.

As I things started to settle down, I started to look onto the reporting. Within our organization, we have a timesheet system that we use and I log hour time. Hence, what better place to report.

First thing I had to do, is to understand the data model that the timesheet system used. It took a few (1-2) days and experimenting, but after few, I understood the model
Then, it was time to create a SQL Server Cube. I had heard before about cubes, but had never created a cube. Hence, I went through a Microsoft Analysis Services Tutorial, which helped to understand what a cube was and how to create one. Once again, it took me about 3-5 days to get a grasp of creating cubes
Then, since I am not a designer, I contacted our HR department and had them provide me with a template format for the report. It took 1 day to get what I needed (those HR folks always take their time) ;)
Then, I decided to use "Microsoft Excel 2007", since it provides you with the ability to connect to Cubes. Once the data connection is set, the Cube data will be displayed as a Pivot Table. It took about 1 day.
So let's report, or at least that's what I thought. Since (1) had a data model, (2) had a cube, (3) had a report format and (4) Excel 2007 as a presentation layer.

Here is where things got a bit more interesting:

As I started to do data mining, I realized that the data was not being refreshed. Within Microsoft Excel 2007, there was an option to refresh that data, however, nothing happened.

After more researching, I found out that in order to refresh the Cube data, I had to process the Cube.

Well, using "Microsoft SQL Server Management Studio", I connected to Analysis Services and started to look at the Properties of a Cube. I was hoping to see a setting, in which I could schedule the Cube to process on a daily basis. However, there was no such a setting.

After more researching, I was able to find out that these are the steps that you must take.
Create an SSIS Package:

Open the "SQL Server Business Intelligence Development Studio" and create a new "Integration Services Project".
Under the "Connection Manager" Section, perform a right click on select "New Analysis Services Connection...", which will allow you to connect to the location of the Cube.
From the "Control Flow Items", drag and drop a "Analysis Services Processing Task". Double click on the "Analysis Services Processing Task" and under the "Analysis Services" section, set the objects that you would like to process.
Make sure to "Save All" files locally.
Then, go to File -> Save Copy of [ProjectName] As ... and save a copy onto the SQL Server that holds Analysis Services. This step will allow you to deploy the SSIS package onto the appropriate server hosting analysis services.
You are done.

Schedule the SSIS Package:

Open "Microsoft SQL Server Management Studio" and connect to the "Database Engine"
Under the "SQL Server Agent", create a "New Job" and provide a Name and Description for the Job.
Under the "Steps" section, create a new step by clicking on the "New..." button. Provide a step name and under the "Type" drop down list, select "SQL Server Integration Service Package". Provide the SQL "Server" Name and click on the "Packages..." button and select the SSIS Package.
Under the "Schedule" section, create a new Job Schedule by clicking on the "New..." button.
You are done.

Run the schedule jobs a few times for testing. Moreover, I would recommend to add an email alert under the SSIS Package, to be alerted when the package was completed or failed.
Now, I am able to get the latest data without any issues.
Hope this helps ;)

Posted at 7:49 AM by Jorge Vasquez | Category: SharePoint 2007 | Permalink | Email this Post | Comments (3)

3/26/2008

SharePoint Conference 2008 - Seattle

Seattle is one of those cities that I wanted to visit for the longest time, since I grew up during the "Grundge" era. In addition, I am a SharePoint kind of guy and the combination of both made it a great trip. Seattle reminded me a lot to Denver, Colorado, however, a bit bigger and colder.

At the conference, I learned about a few topics which caught my interest. The Silverlight presentation on General Mills, displayed some great capabilities of Silverlight. In addition, there were plenty of session in regards to Business Intelligence which opened my eyes to the possibilities. There is a lot technology left to learn.

The city of Seattle has a lot to offer, the Space Needle, the Public Market, Ferries, Arts & Crafts and many other places of entertainment. One thing that I enjoyed the most, was my introduction to Sushi (specially the Salmon Sushi at Umi).

Will post picture soon.

Posted at 11:28 AM by Jorge Vasquez | Category: SharePoint 2007 | Permalink | Email this Post | Comments (2)

1/11/2008

Traveling Around Argentina and Chile

For the past few weeks, I have been traveling around two of the most beautiful and dearest countries to me; Argentina (Buenos Aires, El Chaltern) and Chile (Santiago, Vina del Mar). Both countries have great food, great places to visit, but most of all; great people.

Along the way I have met some wonderful individuals, many of which I will never forget and would like to thank for listening and providing a helping hand. Along the way I have seen people wanting to connect to one another, wanting to share their experiences. Along the way I have seen individuals in unthinkable situations that many of us would considered impossible, yet they continue on. Along the way I have seen the resilience of the human spirit, the good in all people, and the beauty of this magnificent world that we live in. Along the way I have been able to reflect on my life and have seen myself grow as a husband, father, friend and individual.

As I get back to business, I can only hope that many of the experiences do not fade away, but become a reminder of how fortunate we are to be where we are. To treat all individuals that same way we would like to be treated and to always tell your loved ones how you feel about them.

I would like to thank my wife and kids for helping me achieve my goals and for their endless support, with out them I would be an aimless soul in this world. ;)

Posted at 4:21 PM by Jorge Vasquez | Category: Travels | Permalink | Email this Post | Comments (3)

9/14/2007

How to remove {generate-id()} from a Customized Form

ISSUE:
I am customizing the EditForm.aspx file and have added an HTML Table Tag. Moreover, when I set the "ID" attribute for the HTML TABLE Tag and save the page, SharePoint keeps on appending the following {generate-id()} . How can I get rid of this, since I am not able to call HTML by ID due to this stuff that SharePoint appends.

SOLUTION:
SharePoint appends the following: {generate-id()} onto HTML element within the body of the form, whose "ID" attribute has been set. Moreover when the HTML element is rendered onto the page, its ID attribute would contain a 6 character string appended.

<input type="text" id="tbLuckyNumberID0EAAA" value="23">

You must create a dummy parameter
Append the dummy parameter to the ID attribute

EXAMPLE:

In the below example, I have set the ID attribute on an HTML TD Tag and an HTML INPUT Tag
Once you save the file, you will notice that SharePoint appends its evil code
Hence, you must create a dummy paramter
And finally, you must append to the ID attribute you own evil code
Moreover when the HTML element is rendered onto the page, its ID attribute has rendered as per your own code.

<input type="text" id="tbLuckyNumber" value="23">
Let me know if it helps. ;)

Posted at 2:12 PM by Jorge Vasquez | Category: SharePoint 2007 | Permalink | Email this Post | Comments (4)

9/10/2007

Using Javascript to Manipulate a List Form Field

This is a great post by the SharePoint Designer Team.

Using Javascript to Manipulate a List Form Field
http://blogs.msdn.com/sharepointdesigner/archive/2007/06/13/using-javascript-to-manipulate-a-list-form-field.aspx

Posted at 12:06 PM by Jorge Vasquez | Category: MOSS | Permalink | Email this Post | Comments (3)

9/5/2007

Denver, Colorado, USA 09012007

I traveled to Denver, Colorado for the wedding of one of my friends (Ali). Even though he is my boss, I consider him more of a friend than my boss. I am happy for Ali and Kat and wish them the best in their new chapter of their lives.

The wedding was held in the heart of Denver, Colorado, which gave me and my family a chance to explore the city and learn a bit more about the Midwest.

Since I traveled with my family, I was looking for activities that I could do with my wife, daughter (11) and son (6) around the city. After looking through tons of websites and brochures, we decided to take the kids to the "Denver Art Museum" which contained a lot of activities for Kids along the museum and it had a very cool architectural design which we all enjoyed. We also visited local sites, such as the US Mint, Colorado Capitol and while on the city there was a "Taste Of Colorado" Food and Music event. The city life is great for families since there are even more activities, such as the "Children Museum" and the "Denver Aquarium" and many parks to visit. In addition, there were plenty of bars and places for singles and couples to enjoy.

Outside the city, we decided to travel to the "Rocky Mountain National Park" (about 1.5-2 hrs from Denver). Driving towards the mountain was one of the most beautiful sights. Along the way, we could easily see plenty of wild life in its nature. In addition, there were plenty of shops selling all sort of things, such us Indian artifacts (I convinced my wife that we must get a Navajo Bow and Arrow Set :)), clothing, food, etc.. Make sure to purchase drinks, since you will be thirsty most of the time, especially when hiking a trail at the National Park. The information center at the Park had great trails to hike for families and adults. Hiking was a great experience for the whole family, since it helped us appreciate how people back in the day, used to live (museum history and trail experience reinforced how hard it could have been. Kids really appreciated). We have been to the "Shenandoah National Park", since it is close to where we live, however, the "Rocky Mountain National Park" is a much better experience for families and couples.

Will post pictures as soon as possible.

Posted at 9:37 AM by Jorge Vasquez | Category: Travels | Permalink | Email this Post | Comments (44)

8/20/2007

Deploying and Supporting Enterprise Search (Technical White Paper)

This is a great technical white paper, even for developers, since it provides an overview on Enterprise Search. If you are looking for code, this is not necessarily what you would like, however, if you are looking for a features and architecture, this is a good start.

http://www.microsoft.com/technet/itshowcase/content/deployingsearchtwp.mspx

Posted at 11:44 AM by Jorge Vasquez | Category: MOSS | Permalink | Email this Post | Comments (3)

7/30/2007

SharePoint Performance on Large Lists

The Microsoft SharePoint Products and Technologies Team Blog posted a very good white paper that talks about SharePoit performance of a very large lists. Greate read.

White Paper:
Working with Large Lists in Office SharePoint Server 2007"

Excerpt:

There is documented guidance for Microsoft^® Office SharePoint^® Server 2007 regarding the maximum size of lists and list containers. For typical customer scenarios in which the standard Office SharePoint Server 2007 browser-based user interface is used, the recommendation is that a single list should not have more than 2,000 items per list container. A container in this case means the root of the list, as well as any folders in the list — a folder is a container because other list items are stored within it. A folder can contain items from the list as well as other folders, and each subfolder can contain more of each, and so on. For example, that means that you could have a list with 1,990 items in the root of the site, 10 folders that each contain 2,000 items, and so on. The maximum number of items supported in a list with recursive folders is 5 million items.

In Office SharePoint Server 2007, virtually all end-user data is stored in a list. A document library, for example, is just a specialized list. The same is true for calendars, contacts, and other interfaces; they are all just customized versions of the basic SharePoint list, also referred to as an SPList. The individual items in the list are referred to as list items generally, or an SPListItem in an SPListItemCollection in the Office SharePoint Server 2007 object model. The findings in this article are equally important across all of the ways in which you store and work with data in a Office SharePoint Server 2007 site.

There are some scenarios in which you want to take advantage of the features of Office SharePoint Server 2007, but need to exceed the limit of 2,000 items per container. If you write your own interface for managing and retrieving the data, it’s quite possible that you can go past this limit without an adverse impact on farm performance. You may be able to manage larger lists to some extent by using views within Office SharePoint Server 2007 that are filtered such that there are never more than 2,000 items returned. Filtered views provide better performance than just trying to view one large flat list, but are not as efficient as breaking down the list into different containers if you are using the predefined browser-based Office SharePoint Server 2007 interface.

If you develop your own interface, there are several different ways to retrieve list data, each with different performance characteristics. Some data access methods perform very well, but are only useful in a limited number of scenarios. Finally, there are also performance tradeoffs that need to be made with other data maintenance tasks in addition to data retrieval.

Posted at 3:18 PM by Jorge Vasquez | Category: MOSS | Permalink | Email this Post | Comments (0)

RSS Feed

1 - 10

Jorge (El Che) Vasquez Blog