SSIS - debug a "script component" control

If you are reading this, you might also have faced a situation where you added a breakpoint and thought of debugging a script component in SSIS package ..

Ok, I found this is not possible. At least I could not do it in SQL 2005. Maybe its possible in 2008. Do note that you can debug scripts in a "Script task" control but not within a "script component" control within a data flow section.

I tried everything .. changing the "Precompile" property to false/true, changing the script control's name (in the design script window - changed the default generated control name from ScriptComponent_7dac1823375d4ad0bddf242bed9e1391 to ScriptComponent) etc .. but nothing worked ..

So maybe this post will save someone's time .. I copied my code to script task and I could manage to debug part of the code (the part I needed to check) .. after that I deleted the script task and returned back to my original flow/code of using the "script component"

Rejo

System.Object(), SSIS, Active directory and MemeberOf property

For the last couple of months I have working on sharepoint solutions and one of the requirement involved in fetching all users and groups from active directory. The data fetched had to be then inserted into a local SQL databaseb. As this information changes with time, the process had to be run once daily. So I opted on using SSIS to automate the whole process

It seemed pretty straight forward. I used a OLEDB connection of "Microsoft data services" to read the data from active directory. As a test I fetched couple of fields like the cn, name etc and everything worked fine, till I decieded to get the groups that the users belong to. I knew that it would be part of the "memberof" property. I used the same approach and I noticed that value gets inserted as "System.Object[]" into the database. I figured out that the value is returned as a blobcolumn and anyway I tried to parse it, it always gave me the same value. I tried

Dim lByte() As Byte = CType(dataRow(0), Byte()) .. where datarow(0) is the memberof field
System.Text.Encoding.Unicode.GetChars(lByte)

... also tried converting the column to string, collection, byte etc etc ..

and many other options .. all gave me the same result or an error .. I was really getting frustrated and I searched the net for solution. There are some examples on using SSIS and active directory and no where do they talk about how to read value from this column. So I came back to trying it on the own. I wanted to check the type of this column. But I could not add a breakpoint into a "script component" (and that is another story) .. I learned that I could add a breakpoint into a "script task" and so I copied my code over there and added breakpoint to see what data is fetched.

I saw that the column actually contains a array value. I had never seen this before .. The content of a single column looked like
(0) CN=CN1,OU=OU1,DC=corp1,DC=corp2
(1) CN=CN2,OU=OU1,DC=corp1,DC=corp2
(2) CN=CN3,OU=OU1,DC=corp1,DC=corp2

so i converted the column value to "system.array". and got the information that I needed ...

Hope this helps some people out there .. and not have to spend hours or days looking for a solution.

Rejo