Category: Business Intelligence

Categories
.NET Business Intelligence

adding custom SSIS transformation to visual studio toolbox fails

Just very recently I encountered an issue in deploying a custom SSIS component assembly where I cannot add a custom SSIS transformation to the Visual Studio toolbox.

It turns out to be a relative “no-brainer” error if only the clues were more straightforward. Basically after deploying the assembly I could not find my component listed in the “SSIS Data Flow Items” tab list. [more]

There are a number of articles available online on creating custom SSIS objects (control flow tasks, source, destination, data transformation etc).

Here are a few :

Extending SSIS Part 1 – Creating a Custom Data Flow Transformation Component

Developing a Custom Data Flow Component

Developing a Custom Transformation Component with Synchronous Outputs and searching say using keyword “adding custom SSIS transformation” should return more.

 

At Agilone, we’ve been developing SSIS components to complement our products and one of the issues I’ve faced recently is when I was deploying the custom SSIS components (the assembly) and it’s time to add it to the toolbox (Choose Items > SSIS data flow items tab (since the component was a data flow item)) I was unable to see the component (or any component on the assembly I just added to GAC and the PipelineComponents folders).

I tried these (in different orders and to no avail but for completeness including in the list):

1) close BIDS (Visual Studio Shell) or reset toolbox and close BIDS

2) uninstall the assembly in GAC and reinstall the assembly in GAC

3) make sure that assembly is on the correct folder (e.g. DTSPipelineComponent – here are more details on deployment and testing – Deploying and Testing Custom SSIS Components)

4) restart SSIS service

5) reopen the project and try to re-add the component in the toolbox again.

As mentioned it didn’t do the trick and took me sometime to figure it out.

It turns out that there was a problem with the assembly. I built the version referencing some SQL 2005 libraries (DLL), specific version = true and when I deployed it to that server (which only had SQL 2008 DLLs) it failed silently and simply didn’t appear on the “SSIS Data Flow Items” tab.

There are more complex ways to figure out why it doesn’t appear in the said tab but I would like to share the more straightforward one.

After the step where you clicked Choose Items (for toolbox) when the dialog window appears instead of changing to the “SSIS Data Flow Items” tab, stay on the .NET Components. And then click browse and browse for the assembly which contains your custom SSIS component

If the assembly chosen doesn’t contain an SSIS component then it will say so.

However if the assembly contains errors (e.g. missing referenced assemblies among others) then it will also show an error dialog saying so.

That’s it. I hope it helps and saves you some time (since I wasn’t able to find this information quickly – for a good “googler”)

And for the more “complex” method I believe you can you fuslogvw or similar assembly binding logger/monitoring tool to figure out any issues on assembly binding (missing references) such as the one I encountered. But I suggest doing he “browse” test first before exploring those debugging techniques.

And finally, of course as for any project check that you have the right assemblies referenced, whether you want specific version true/false and make sure that all dependencies are in place.

 

Categories
Business Intelligence Career Growth Data

SQL Business Intelligence Developer Needed (Manila, Philippines)

We are currently looking for a SQL BI Developer Professional to work with us on exciting, high-profile/scale projects. Feel free to contact me or visit http://www.lwsmedia.com/contact.htm. Looking forward to work with you. [more]

Company Profile:

Agilone LLC
Norwalk, CT USA, Los Gatos CA USA , Istanbul, Turkey  and Manila, Philippines

Companies who can effectively understand, process and take value from their data gain a sizable competitive advantage.  However, many fail to do so since the amount of data captured by organizations is growing more quickly than the capabilities of the tools to analyze it.  Agilone solves this problem with proprietary, SAAS-based analytical tools that help them make data-driven marketing decisions that drive superior results.
 
Agilone’s goal is to help clients develop and execute data-driven marketing strategies. We provide clients with advanced technology and analytical marketing services to help identify and execute opportunities hidden in their data.  Our approach is effective because we analyze more of their data than the competition and offer a customized solution.
 
Typical engagements are in areas of customer valuation, pricing, response modeling, segmentation, with follow-on implementation of technologies such as data warehousing, web-based application development, database management and business intelligence services.
 
We are headquartered in Norwalk, CT with a technology development and services center in Istanbul, Turkey.  Recently, we have opened our new office in Silicon Valley (Los Gatos) California.

We are a high growth, entrepreneurial company and are always looking for intelligent hardworking people to join our company.  Transfers between Istanbul office and US offices are possible and we do sponsor work visas and Greencards in US depending on tenure and performance of employees.
 
Job Description:
This position is for our Manila Office.   Due to the increase in our business volume we need additional SQL developers with the following background and skill set:
 
Position Responsibilities:
– Be part of the developer team from Analysis to design, programming, testing and deployment
– Design and administer project related databases
– Create technical documentation
– Perform unit test of the codes
– Author user manuals and installation guides
 
Education:
Minimum: BS, BA or equivalent, very good command of written and spoken English
 
Required Skills and Experience:
1. Excellent communication skills both written and verbal
2. Competent in T-SQL, working knowledge of MS SQL 2005 and MS SQL 2008.
3. Experience in Dimensional Databases and OLAP Cubes.
4. Desire to learn new platforms and environments as the projects leads.
5. Strong analytical and problem solving skills with attention to detail.
6. Self-motivated – comfortable working in a fast paced environment with limited direction.
7. Ability to multi-task and work on for several different projects.
8. Holding a Microsoft certification is a plus.
 
Process:
We will conduct 2 tests before an interview.  The first test would be a general aptitude test, after you pass this test you will be given a more specific T-SQL test.  After these tests you will be invited for an interview with the Director of Business Intelligence and the Principal of the company.

Categories
Business Intelligence

DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Fuzzy Lookup” (60) failed with error code 0xC0202009 while processing input “Fuzzy Lookup Input” (61)

Encountered this error in one of recent tasks involving SSIS Fuzzy Grouping and Lookup.

[Fuzzy Grouping Inner Data Flow : SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component “Fuzzy Lookup” (60) failed with error code 0xC0202009 while processing input “Fuzzy Lookup Input” (61). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[Fuzzy Grouping Inner Data Flow : SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “OLE DB Source” (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

[Fuzzy Grouping [800]] Error: A Fuzzy Grouping transformation pipeline error occurred and returned error code 0x8000FFFF: “An unexpected error occurred.”.

This is the only relevant error message or code that I got and didn’t seem quite helpful. [more]

And it appears intermittently at first, until I noticed that the error occurs at a certain number of records and although not exactly repeatable, very close to that value. around 4M rows.

While troubleshooting, tried increasing available memory (released some memory locked for another application) and the error occurred when the number of input records increased.

Tested further and the behavior seemed consistent. And it seemed that the size of the input is proportional to the memory (RAM) used. Calculated estimated size of ever row I had to be at 1KB. Tried processing 10M rows and freed up at least 10GB of RAM and it worked fine.

I can try to work-around so that I will process smaller sets/batches so i push 10M in one pass but just needed to figure out what was causing it and although not very clear from the error message I think increasing free memory did the trick for me.

Sharing in case someone else runs into a similar error. Hope this helps

Categories
.NET Business Intelligence Web

Safety settings on this machine prohibit accessing a data source on another domain

Encountered this message many times with sites using PivotTable component of OWC11 (office web components) “Safety settings on this machine prohibit accessing a data source on another domain”

This is a browser restriction (at least with IE). Before resolving this, make sure you are accessing a trusted site.

Resolution:

1. Add site to the zone (e.g. it is recommended that you only allow your trusted sites to be exempted from this restriction). So add the site (if you trust it) to your Trusted Zone. Internet Options > Security

2. Modify Security Level of that Zone (e.g. Trusted Zone). Custom Level button. A pop-up dialog should appear. Go the Miscellaneous Settings section. Then you should be a setting for “Access data sources across domains”. Recommended to set it to Prompt. If that option is selected you will be prompted when a component requires this permission.

Hope that helps.

Categories
Business Intelligence Data

24 hours of high quality sql server training for free

The Professional Association for SQL Server or popularly known aka PASS, one, if not the most popular and biggest group of SQL Server professional in world will be having 24 hours of PASS – a series of online web meetings with the best speakers in the industry [more]

Join us online on September 2, 2009* for 24 hours of free, nonstop SQL Server training

Join us for 24 Hours of PASS, a FREE community event featuring some
of the top SQL Server and Business Intelligence (BI) speakers in the
industry. Over the course of one 24-hour period—directly from your
computer—our experts will:

  • Show you how to take better advantage of the SQL Server capabilities already at your fingertips
  • Give you an in-depth look at the hottest SQL Server and BI topics,
    including SQLCLR performance, relational database design, performance
    tuning and troubleshooting, SQL Server 2008 security, embedding
    Reporting Services in your apps, SSIS tips and tricks, working with
    spatial data, text mining, data warehousing foundations, and much
    more… 
  • Take your questions directly and answer them live!

Check out all the great SQL Server sessions you can attend.

24 Hours of PASS is a series of live one-hour webcasts broadcast
over a 24-hour period that focus on a host of SQL Server topics. The 24
one-hour presentations will begin at 00:00 GMT (UTC) on September 2,
2009.*

You can view all details from the event's official site : http://24hours.sqlpass.org/

If only I could attend all of them in 24 hrs I would 🙂 Hopefully they would be able to make recordings this available. I will be trying to attend the Business Intelligence related ones so if we do make it, see you there. "virtually"

Categories
Business Intelligence

MCITP SQL 2008 Business Intelligence Developer exam 71-452 results out

Just a quick note for those who have taken the MCITP SQL 2008 Business Intelligence Developer exam 71-452 that the results are out [more] (at least for mine)

You should check in your prometric profile if you haven't got an email yet.

And this my first MCITP certification. Yay for me!

A little unexpected since admittedly I only have experience with SSIS and SSRS and not the kind of work I do on a regular basis. Nevertheless I always had a fascination on Business Intelligence

Hopefully will come in handy somehow and I will be building up more on this areas and hopefully be able to share a thing or two soon.