Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
Friday, March 02, 2007
SSIS Exception : Interface not registered (Exception from HRESULT: 0x80040155)
I've got some handover documents to write, and as I'm working from home on Monday I thought I'd set the laptop up to enable me to do this. One of the things I need to document is the SSIS export I wrote last year. I started up the laptop, and tried to open up the project and got a "Interface not registered (Exception from HRESULT: 0x80040155)" message. Not helpful. A quick search on google pointed me at a seemingly unrelated post on a TechNet forum, but at the very bottom is the solution from James McAuliffe. The solution, for me, was:
Navigate to the following folders:
C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\common\
C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\vsa
In each of those folders run this command to re-register everything in there.:
for %i in (*.dll) do RegSvr32 -s %i
The project opens now, but leaves me with a "Creating an instance of the COM component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from the IClassFactory failed due to the following error: c001f011." Bah!
Navigate to the following folders:
C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\common\
C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\vsa
In each of those folders run this command to re-register everything in there.:
for %i in (*.dll) do RegSvr32 -s %i
The project opens now, but leaves me with a "Creating an instance of the COM component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from the IClassFactory failed due to the following error: c001f011." Bah!
Labels: SSIS
// posted by Jane @ 12:33 PM 
Comments:
Tuesday, September 19, 2006
SSIS Variables and Deadlock
I've been having problems with deadlock on SSIS variables. The main issue is that I have a script item, which has a readwrite variable of strErrorDescription. In the case of a problem this gets populated. I have an onError event tied to this script item. That event uses strErrorDescription to populate the reason for the error. The onError event triggers before the script item finishes properly, and so before the variable gets released. After a bit of headscratching I found this post on the MSDN forums which describes the problem, and a workaround. I've added Dts.Variables.Unlock() to the code, just after the variable is written to, and this seems to do the trick.
Labels: Development, SSIS
// posted by Jane @ 12:29 PM
Comments:
SSIS - Can't debug script code in event handlers
I was struggling to find a way to debug event handler scripts in SSIS, and thought I was missing something obvious (as did Jamie). Thanks to Daniel Read (again) I now know that I'm not the only one:
b) Cannot debug script tasks in event handlers. This appears to be a bug. I can't get the debugger to stop on a line of code in a script task in an event handler. Strangely, if I set a break point in a script task in an event handler, a VBA code window will pop up for the first script task in the package (even though the breakpoint is not set in that task), but there will never be a break in the task where I set my breakpoint
b) Cannot debug script tasks in event handlers. This appears to be a bug. I can't get the debugger to stop on a line of code in a script task in an event handler. Strangely, if I set a break point in a script task in an event handler, a VBA code window will pop up for the first script task in the package (even though the breakpoint is not set in that task), but there will never be a break in the task where I set my breakpoint
Labels: Development, SSIS
// posted by Jane @ 11:17 AM
Comments:
Tuesday, August 29, 2006
SSIS and DTExec Red Herring
When initially deploying my SSIS package to my pre-production environment I got a bit hooked up on the following warning message which was appearing despite calling my package via DTExec with /conf = C:\Configuration.dtsConfig
Warning: 2006-08-21 10:58:19.96
Code: 0x80012014
Source: export process
Description: The configuration file "D:\Configuration.dtsConfig" cannot be found. Check the directory and file name.
End Warning
Warning: 2006-08-21 10:58:19.96
Code: 0x80012059
Source: export process
Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.
End Warning
I managed to reproduce this on my development machine by renaming D:\Configuration.dtsConfig to .dtsConfigx and running the package. It seems that SSIS checks for the configuration in the location it was originally created before processing the /conf override. Once I'd sorted this out I could concentrate on the real errors :-)
Warning: 2006-08-21 10:58:19.96
Code: 0x80012014
Source: export process
Description: The configuration file "D:\Configuration.dtsConfig" cannot be found. Check the directory and file name.
End Warning
Warning: 2006-08-21 10:58:19.96
Code: 0x80012059
Source: export process
Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.
End Warning
I managed to reproduce this on my development machine by renaming D:\Configuration.dtsConfig to .dtsConfigx and running the package. It seems that SSIS checks for the configuration in the location it was originally created before processing the /conf override. Once I'd sorted this out I could concentrate on the real errors :-)
Labels: Development, SSIS
// posted by Jane @ 2:22 PM
Comments:
Tuesday, August 22, 2006
SSIS, SP1 and Testing
Having installed my package onto the pre-production environment, and ironed out a heap of database permissions problems, I found myself contemplating the following error...
Error: 2006-08-22 13:56:32.93
Code: 0xC002F210
Source: Get Data
Description: Executing the query "EXEC PRC_DATA_SEL ?" failed with the following error: "End tag 'Description' does not match the start tag 'ROOT'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
When I connect to the pre-production database via my dev package having changed the configuration file, it functions as expected without a problem. So it isn't a database problem. When I change the pre-production configuration to point at my dev database, I get problems - so it looks like a machine related problem.
I then put Integration Services on to another test machine, and tried from there, and had success. So there was obviously something different about our pre-production box to both my dev environment and my newly borrowed test kit. The OS's were different, so I then spent a frantic 5 minutes checking that Integration Services should run on Windows 2000 Server SP4 - which it should.
Finally, I decided to install SP1 on my test (previously working) machine, and that too stopped working - not with exactly the same error but at this stage that was good enough. So, I've rolled back the pre-production environment to be pre-SP1 and now the package runs.
Not a great solution, but it'll do for the moment. Next step - upgrade my dev environment to be SP1 and re-work as necessary. And the moral of this story is always ensure your dev environment matches your pre-production and live. I'm glad I found out before the live stage though :-)
Error: 2006-08-22 13:56:32.93
Code: 0xC002F210
Source: Get Data
Description: Executing the query "EXEC PRC_DATA_SEL ?" failed with the following error: "End tag 'Description' does not match the start tag 'ROOT'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
When I connect to the pre-production database via my dev package having changed the configuration file, it functions as expected without a problem. So it isn't a database problem. When I change the pre-production configuration to point at my dev database, I get problems - so it looks like a machine related problem.
I then put Integration Services on to another test machine, and tried from there, and had success. So there was obviously something different about our pre-production box to both my dev environment and my newly borrowed test kit. The OS's were different, so I then spent a frantic 5 minutes checking that Integration Services should run on Windows 2000 Server SP4 - which it should.
Finally, I decided to install SP1 on my test (previously working) machine, and that too stopped working - not with exactly the same error but at this stage that was good enough. So, I've rolled back the pre-production environment to be pre-SP1 and now the package runs.
Not a great solution, but it'll do for the moment. Next step - upgrade my dev environment to be SP1 and re-work as necessary. And the moral of this story is always ensure your dev environment matches your pre-production and live. I'm glad I found out before the live stage though :-)
Labels: Development, SSIS
// posted by Jane @ 6:04 PM
Comments:
Friday, August 18, 2006
SSIS Course and Book
I'm going to be attending Wise Ltd's Advanced SQL Server Integration Services training course in September. Now admittedly by that point the current project will be live (fingers crossed), but hopefully I'll have a lot more knowledge for the design of the next one, rather than the trial and error approach that I've used this time.
Also our copy of Microsoft SQL Server 2005 Integration Services arrived today. I've only had a quick flick through so far but it looks far better than the other one. Nice work Kirk.
Also our copy of Microsoft SQL Server 2005 Integration Services arrived today. I've only had a quick flick through so far but it looks far better than the other one. Nice work Kirk.
Labels: Development, SSIS
// posted by Jane @ 8:05 PM
Comments:
Monday, August 14, 2006
SSIS Foreach Nodelist enumerators Part 2
My euphoria at getting my SSIS Foreach Nodelist enumerators working was short lived. I had some really strange behaviour going on with my foreach nodelist. It appeared that the XML being interpreted by the node wasn't the XML being passed in. I am using the method described in the DrivenBySQL example, using a node and putting the node contents into an object variable objNode. This objNode is then broken into its composite elements in some script (as described the other day). Those composite elements are then processed. What was happening was that the processing was doing the data for this iteration, but also every other iteration. I wanted to prove that this was really happening, and I wasn't just having a funny five minutes. So I started a new project and created some standard Direct Input XML, one for images and one for some random data.
Images XML - outer loop
<images>
<image>
<source>8162_5260.JPG</source>
<destination>87775588_01.JPG</destination>
</image>
<image>
<source>8162_5260_01.JPG</source>
<destination>87775588_02.JPG</destination>
</image>
</images>
Data XML - Inner loop
<ROOT>
<Item>
<Data>Entry</Data>
</Item>
</ROOT>
I've used a string variable to put comments of what is going on in. So, it puts a started tag and a date time stamp in.
I loop through the image nodes in the outer loop - which should result in two iterations.
Within the outer loop
I add a log entry to the string with the Source entity data in.
I then loop through the Item nodes in the inner loop - which should result in one iteration.
Within the inner loop
I add a log entry with the datetime stamp and the Data entity.
Inner loop ends
Outer loop ends
Expected Output
Started at 11/08/2006 12:05:11
Image 8162_5260.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:05:12 with data of Entry
Completed at 11/08/2006 12:05:12
so with 1 For each line per iteration, but what I actually get is:
Output
Started at 11/08/2006 12:05:11
Image 8162_5260.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
For each logged at 11/08/2006 12:05:12 with data of Entry
Completed at 11/08/2006 12:05:12
which implies that the data being returned in the inner foreach contains data from both the 1st and 2nd iterations, which is what I was seeing in my problematic project.
I then changed the images XML to add a third image, and ran the test and got the following output:
Output
Started at 11/08/2006 12:36:21
Image 8162_5260.JPG
For each logged at 11/08/2006 12:36:21 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:36:21 with data of Entry
For each logged at 11/08/2006 12:36:21 with data of Entry
Image 100_1000.JPG
For each logged at 11/08/2006 12:36:22 with data of Entry
For each logged at 11/08/2006 12:36:22 with data of Entry
For each logged at 11/08/2006 12:36:22 with data of Entry
Completed at 11/08/2006 12:36:22
again implying that the data in the inner foreach contains both the current iteration, and any previous iterations.
I took a search on the MSDN SQL Server Integration Services and found Nested Loops in the Control Flow to be the closest to my issue. Having to create a child package sounds like a workaround rather than a solution to me but I figured it was worth a try and created a child package which just has the inner for each loop in it, and got the following output:
Output
Started at 14/08/2006 15:38:47
Image 8162_5260.JPG
For each logged at 14/08/2006 15:38:48 with data of Entry
Image 8162_5260_01.JPG
For each logged at 14/08/2006 15:38:49 with data of Entry
Completed at 14/08/2006 15:38:49
So, finally it works as expected. Now to go and integrate it into the main project rather than just this test one.
Images XML - outer loop
<images>
<image>
<source>8162_5260.JPG</source>
<destination>87775588_01.JPG</destination>
</image>
<image>
<source>8162_5260_01.JPG</source>
<destination>87775588_02.JPG</destination>
</image>
</images>
Data XML - Inner loop
<ROOT>
<Item>
<Data>Entry</Data>
</Item>
</ROOT>
I've used a string variable to put comments of what is going on in. So, it puts a started tag and a date time stamp in.
I loop through the image nodes in the outer loop - which should result in two iterations.
Within the outer loop
I add a log entry to the string with the Source entity data in.
I then loop through the Item nodes in the inner loop - which should result in one iteration.
Within the inner loop
I add a log entry with the datetime stamp and the Data entity.
Inner loop ends
Outer loop ends
Expected Output
Started at 11/08/2006 12:05:11
Image 8162_5260.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:05:12 with data of Entry
Completed at 11/08/2006 12:05:12
so with 1 For each line per iteration, but what I actually get is:
Output
Started at 11/08/2006 12:05:11
Image 8162_5260.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:05:11 with data of Entry
For each logged at 11/08/2006 12:05:12 with data of Entry
Completed at 11/08/2006 12:05:12
which implies that the data being returned in the inner foreach contains data from both the 1st and 2nd iterations, which is what I was seeing in my problematic project.
I then changed the images XML to add a third image, and ran the test and got the following output:
Output
Started at 11/08/2006 12:36:21
Image 8162_5260.JPG
For each logged at 11/08/2006 12:36:21 with data of Entry
Image 8162_5260_01.JPG
For each logged at 11/08/2006 12:36:21 with data of Entry
For each logged at 11/08/2006 12:36:21 with data of Entry
Image 100_1000.JPG
For each logged at 11/08/2006 12:36:22 with data of Entry
For each logged at 11/08/2006 12:36:22 with data of Entry
For each logged at 11/08/2006 12:36:22 with data of Entry
Completed at 11/08/2006 12:36:22
again implying that the data in the inner foreach contains both the current iteration, and any previous iterations.
I took a search on the MSDN SQL Server Integration Services and found Nested Loops in the Control Flow to be the closest to my issue. Having to create a child package sounds like a workaround rather than a solution to me but I figured it was worth a try and created a child package which just has the inner for each loop in it, and got the following output:
Output
Started at 14/08/2006 15:38:47
Image 8162_5260.JPG
For each logged at 14/08/2006 15:38:48 with data of Entry
Image 8162_5260_01.JPG
For each logged at 14/08/2006 15:38:49 with data of Entry
Completed at 14/08/2006 15:38:49
So, finally it works as expected. Now to go and integrate it into the main project rather than just this test one.
Labels: Development, SSIS
// posted by Jane @ 3:47 PM
Comments:
SQL Server Integration Services
I've been doing some work with Integration Services in the past few weeks. Some resources have proved to be a bit too much like hard work, others have been more useful.
MSDN has a series of tutorials
Blogs:
There are some good blogs kicking around - these are the ones that I've had a few hints and tips from and that have been added to my RSS aggregator:
Forums:
Webcasts:
Books:
Update: After signing up for one of the webcasts the other day, a useful email came through pointing me at MSDN Integration Services which has an impressive set of links.
Futher Update: Thanks to Linda for pointing out via Daniel Read's index that there is an index to the SSIS articles on Database Journal.
MSDN has a series of tutorials
Blogs:
There are some good blogs kicking around - these are the ones that I've had a few hints and tips from and that have been added to my RSS aggregator:
- Kirk Haselden
- Jamie Thomson - especially the SSIS: Suggested Best Practices and naming conventions
- Daniel Read who amongst his other helpful posts has put together an index to the excellent SQL 2005 SSIS How to series by Marcin Policht at Database Journal.
Forums:
Webcasts:
Books:
- Professional SQL Server 2005 Integration Services is a bit basic and doesn't really go into any of the more complex bits and pieces. An alright introduction, but to be honest some of the web sites mentioned above can give you that level of information.
- Microsoft SQL Server 2005 Integration Services. On order, not released until end of August 2006.
Update: After signing up for one of the webcasts the other day, a useful email came through pointing me at MSDN Integration Services which has an impressive set of links.
Futher Update: Thanks to Linda for pointing out via Daniel Read's index that there is an index to the SSIS articles on Database Journal.
Labels: Development, SSIS
// posted by Jane @ 11:09 AM SQL Server Integration Services'), 'delicious','toolbar=no,width=700,height=400'); return false;">
Comments:
Wednesday, August 09, 2006
SSIS Foreach Nodelist enumerators
I have some simple XML to parse within my SSIS package
<images>
<image>
<source>8162_5260.JPG</source>
<destination>87775588_01.JPG</destination>
</image>
<image>
<source>8162_5260_01.JPG</source>
<destination>87775588_02.JPG</destination>
</image>
</images>
and I want to take an image and copy the physical image referred to by source to destination. I knew I needed to use the foreach Nodelist enumerator, but I didn't really know where to start as the book I have on my desk doesn't seem to cover it at all.
Attempt One
I found a useful article SSIS Debugging foreach NodeList enumerators which was a start, but didn't seem to get me where I wanted to be as with XPath settings of
Enumeration type: NodeText
OuterXPathString: /
mapping to a string variable named variable (original heh, but this was a proof on cencept) with index 0 I got data in the variable of 8162_5260.JPG87775588_01.JPG8162_5260_01.JPG87775588_02.JPG. Right data, but not in a useable format.
With the XPath settings of
Enumeration type: ElementCollection
OuterXPathString: /images
InnerElementType: NodeText
InnerXPathString: *
my variable has the data 8162_5260.JPG87775588_01.JPG in it on the first, and worryingly only, iteration.
Attempt Two
Obviously doing something wrong, so I hit google again, and this time found a DrivenBySQL example, which has pictures and everything (and it contains a link to Visual XPath, which was a useful tool for validating my XPath statement).
So, I changed the XPath settings to be
OuterXPathString: /images/image
InnerElementType: Node
InnerXPathString: .
capturing the output in a variable of type Object
I then wrote some script to get the data from my XmlNode into 2 string variables, this script was heavily based upon the sample code from the DrivenBySQL example but with the follwing definitions for string and destination based on the appropriate entites, rather than the attributes in the example
strSource = objNode.SelectSingleNode("source").InnerText.ToString
strDest = objNode.SelectSingleNode("destination").InnerText.ToString
All now seems to be working. Hurrah!
<images>
<image>
<source>8162_5260.JPG</source>
<destination>87775588_01.JPG</destination>
</image>
<image>
<source>8162_5260_01.JPG</source>
<destination>87775588_02.JPG</destination>
</image>
</images>
and I want to take an image and copy the physical image referred to by source to destination. I knew I needed to use the foreach Nodelist enumerator, but I didn't really know where to start as the book I have on my desk doesn't seem to cover it at all.
Attempt One
I found a useful article SSIS Debugging foreach NodeList enumerators which was a start, but didn't seem to get me where I wanted to be as with XPath settings of
Enumeration type: NodeText
OuterXPathString: /
mapping to a string variable named variable (original heh, but this was a proof on cencept) with index 0 I got data in the variable of 8162_5260.JPG87775588_01.JPG8162_5260_01.JPG87775588_02.JPG. Right data, but not in a useable format.
With the XPath settings of
Enumeration type: ElementCollection
OuterXPathString: /images
InnerElementType: NodeText
InnerXPathString: *
my variable has the data 8162_5260.JPG87775588_01.JPG in it on the first, and worryingly only, iteration.
Attempt Two
Obviously doing something wrong, so I hit google again, and this time found a DrivenBySQL example, which has pictures and everything (and it contains a link to Visual XPath, which was a useful tool for validating my XPath statement).
So, I changed the XPath settings to be
OuterXPathString: /images/image
InnerElementType: Node
InnerXPathString: .
capturing the output in a variable of type Object
I then wrote some script to get the data from my XmlNode into 2 string variables, this script was heavily based upon the sample code from the DrivenBySQL example but with the follwing definitions for string and destination based on the appropriate entites, rather than the attributes in the example
strSource = objNode.SelectSingleNode("source").InnerText.ToString
strDest = objNode.SelectSingleNode("destination").InnerText.ToString
All now seems to be working. Hurrah!
Labels: Development, SSIS
// posted by Jane @ 12:40 PM
Comments:
Monday, August 07, 2006
SSIS and Microsoft Connect
Jamie Thomson has an article about Improving the debugging in Katmai (the new SQL Server version) by making requests via Microsoft Connect.
I hadn't heard of Microsoft Connect before, but it seems like a great idea:
By submitting feedback, you can actively work with members of the Microsoft product team to identify and evaluate the importance of issues, recommend workarounds, and help ensure that the product is reliable and meets customer needs. Your feedback can take the form of a suggestion for improving product design or behavior, or a description of a problem that interferes with your work or causes the product to behave unexpectedly.
You can submit feedback, or vote and add comments to existing requests. I've voted on a couple of SSIS issues (as mentioned in Jamie's blog), Use breakpoints in script components and Use breakpoints in script tasks that are in an eventhandler as these both caused me problems last week.
I hadn't heard of Microsoft Connect before, but it seems like a great idea:
By submitting feedback, you can actively work with members of the Microsoft product team to identify and evaluate the importance of issues, recommend workarounds, and help ensure that the product is reliable and meets customer needs. Your feedback can take the form of a suggestion for improving product design or behavior, or a description of a problem that interferes with your work or causes the product to behave unexpectedly.
You can submit feedback, or vote and add comments to existing requests. I've voted on a couple of SSIS issues (as mentioned in Jamie's blog), Use breakpoints in script components and Use breakpoints in script tasks that are in an eventhandler as these both caused me problems last week.
Labels: Development, SSIS
// posted by Jane @ 2:45 PM
Comments:
Thursday, August 03, 2006
SSIS and .NET Assemblies
The internet is a great place, there I was scratching my head trying to work out how to get my .NET assembly to appear in the references section of an Integration Services Script Task when I stumbled across Daniel Read's helpful Fun With SSIS, Script Tasks, and the GAC (Global Assembly Cache). In a nutshell, and I'm sure Daniel puts it much better than I can, to get an assembly to appear in the Add references dialog you have to both register your dll into the GAC AND copy the file into the appropriate framework directory - yeuch!
From the Professional SQL Server 2005 Integration Services book SSIS can only use assemblies located in the .NET framework installation folder for version 2.0
So far this is the only yeuch moment I've had with SSIS
From the Professional SQL Server 2005 Integration Services book SSIS can only use assemblies located in the .NET framework installation folder for version 2.0
So far this is the only yeuch moment I've had with SSIS
Labels: Development, SSIS
// posted by Jane @ 12:58 PM
Comments:
