Getting OpenRowset properties from OLEDB Destination

Question

Getting OpenRowset properties from OLEDB Destination

gervasio_marco

SSC Veteran

Points: 270
More actions
June 24, 2008 at 7:11 am

#122272

Hi
I' m a new user and i have a question for you!:)
I have a data flow task that's is containing: An excel source and a OLEDB destination on sql 2005.
Now i want to make a script (with script component) that gets the name of sqlserver destination table and store it in a global variable.
I was googling yesterday at and i find that the properties to get is the "OpenRowset "properties.
Now i don't know how to reference in my script component to this properties.:crying:
I wrote my code in VB.net.
I post the code but it not seem to work (got error on the first line, it' can't reconize the package object)
****************************CODE***************************************
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
End Sub
Public Overrides Sub PreExecute()
Dim value As String
'IT CAN'T RECONIZE THIS OBJECT, BUT PACKAGE OBJECT HAS NO PROPERTIES
Dim MyTaskHost As TaskHost = Package.Executables(0)
'....SO EVEN THIS SEEM WRONG
Dim dataFlowTask As MainPipe = MyTaskHost.InnerObject
'SQLSERVER IS THE NAME OF MY OLEDB DESTINATION
'HERE SEEMS GOOD
Dim MyComponentMetadata As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection("SQLSERVER")
'GETTING PROPERTIES
Dim CustomProperty As IDTSCustomProperty90 = MyComponentMetadata.CustomPropertyCollection("OpenRowset")
'STORING VALUE IN A LOCAL VARIABLE
value = CustomProperty.Value.ToString
'WRITE GLOBAL VARIABLE
WriteVariable(Me.Variables.CurrentTable, value)
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
'don't fail but send a warning and set variiable to blank
Me.ComponentMetaData.FireWarning(1, Me.ComponentMetaData.Name.ToString, Me.ComponentMetaData.Name & "-" & ex.Message, ex.HelpLink, 1)
vars(varName).Value = ""
Finally
vars.Unlock()
End Try
End Sub
End Class
********************************END CODE*********************************
Alternatively, there is another way to refer this object using script component?Or there is a way to store in a global variable the name of this property?
:ermm:
Thanks!
:w00t:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You are trying to get the table your OLEDB Destination is writing to into a variable? Are you selecting a table in the component editor to choose the table?

I don't get the point. The OLEDB Destination allows you to choose a table, use an expression to specify a table, or use a variable to specify a table. If something is changing the table name dynamically (package configuration, expression, etc.) why wouldn't you have this process set the variable as well?

gervasio_marco SSC Veteran Points: 270 More actions · Answer 2

Hi

tahks for reply.Now i 'll try to explain better...

So, i have an OLEDB destination on sql server.I specified the name of the table into the component editor, for example "dbo.Table1" using normal editor for oledb destination.

Now, i want to write a component script that can read this property and store it in an external to package variable (for example @variable1) so that it could be read globally from this data flow task.

The problem is this.I ' ve an integration service package with 26 data flow task that are all the same but that load data from different excel sheet and table in sequence

I need the currenttable name to write it on a table that i need for other operation (logging, control and so on).

Is there anyway another method to know at execution time what is the table i'm loading now?

Now i resolved it MANUALLY assigning the tablename in each data flow task....

So i 've a script that make this:

.....

me.variable.currenttable ="DBO.TABLE1"

......

BLAH BLAH

Where currentTable is mapped to my global variable

It 's possible to get this information Dinamically , getting it from OLEDB destionation task???

I apologize if it's not so clear.

My english is not so well .:doze:

Thanks

hmaharaj Right there with Babe Points: 781 More actions · Answer 3

hmaharaj

Right there with Babe

Points: 781

June 24, 2008 at 8:53 am

#833360

make sure alwaysusedefaultcodepage is set to true

gervasio_marco SSC Veteran Points: 270 More actions · Answer 4

Ok now i can explain better

I want to know how i can extract information from ComponentMetaDataCollection.

In particular i want to get openrowset proerties for an oledb destination task.

thanks:rolleyes:

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 5

Why use a scripting task at all?

I dont understand?

You have an an excel source with connectionstring, and OLEDB destination with connectionstring, which in all likelihood contains a catalog.

Your OLEDB destination also contains an openrowset property.

You can set all of these via SSIS configurations. Therefore, you have access to this during the management of your load, and as such you should be able to write their values to a different place if you need to do that

~PD

gervasio_marco SSC Veteran Points: 270 More actions · Answer 6

Ok, but the problem is that i don't know how i can access to this properites to store one of this in a variable.

The only things i need is the tablename i 'm loading in that moment (contained in OpenRowset properties) and storing it in a global variable or writing it in a table.

How i can do it?

thanks:w00t: