none
Reference OLE DB Destination from Script task

    Question

  • Hi all, I was hopeing someone could please help me.

    I am trying to reference the OpenRowset property of an existing OLE DB Destination component that is in a data flow task within a sequence container. I want to do this from within a Script Task so I can set a variable to hold the Openrowset property which is used later in the package.

    I have tried referencing the MainPipe class but can't work out how to reference the collection property of an existing object.

    Hopw that makes sense, any help is much appreciated!!!

    Many thanks!

     

     

    Monday, October 18, 2010 3:49 PM

Answers

  • You cannot reference another task from script in the package, it just cannot be done.

    All the samples you see that use MainPipe and such like are the build or ammend and existing package, but that is extrenal to the package. A package cannot be self modifying. The subtle exception to this is at design-time when a tasks's user interface can influence other objects, but there is no exception at run-time.

    Perhaps try and assign the table name to a variable in the first place then make the OLE-DB Destination use that variable, along with the latter process you orginally wanted it for. Swap the dependency around.

    Generating a package in code may not be as hard as you think, the key is generaly working out how you can map the columns, the rest is easy. Perhaps could look at a hybrid solution, part generated, part user managed, or use an existing package and modify it in code.

    This may give you a start if you want to look at generating packages - http://www.sqlis.com/sqlis/post/Flat-File-To-Sql.aspx


    http://www.sqlis.com | http://www.konesans.com
    Tuesday, October 19, 2010 10:00 AM
    Moderator

All replies

  • I'm not quite sure what you're trying to accomplish - but the short answer is that you won't be able to do it the way you're trying.  Script Tasks don't have any access to any other tasks - including Data Flow Tasks that contain Destination components.

    What exactly is the "OpenRowset" property you're looking for, and why are you wanting to change it?  (I don't see such a property when using an OLE DB Destination.)


    Todd McDermid's Blog Talk to me now on
    Monday, October 18, 2010 5:11 PM
    Moderator
  • Hi, the OpenrowSet property is simply the table name defined in the OLE DB destination component, the reason I need to get the table name assigned to a variable is that it is used as part of a delete script at the end of the package to remove inported rows should any errors occur. The table name is manually selected but I don't want the user to have to also change the hardcoded tablename in the delete script.

    Having looked at the MS site I found examples of referencing the MainPipe class and the ComponentMetaDataCollection but I'm not sure if you can actually reference them within a script task.

    P.S. i don't want to actually change the property just to read it to assign to a variable.

    Thanks

    Monday, October 18, 2010 5:22 PM
  • I'm afraid you're confusing me more... what do you mean the table name is manually selected?  How is that done?  Are you allowing your users to open up your package in BIDS, modify the package, and execute it?  I hope not!

    You should use a variable to store your "dynamic" table name, then set up the OLE DB Destination to use the variable as the source of the table name.  If you do that, then your script can read that variable.  But this also leads me to question why you have a cleanup "script"... as in Script Task.  Unless you're doing very complex stuff with files, you shouldn't need (or want) a Script Task - the builtin File System Tasks and Execute SQL Tasks should work (better) for you.


    Todd McDermid's Blog Talk to me now on
    Monday, October 18, 2010 5:39 PM
    Moderator
  • Hi, thanks for the reply, yes we are allowing the users to amend the package manually but users are IT staff and we have to allow this due to time constraints and also because the field names need to be mapped manually and using a variable won't allow this. 

    We have to load 70 different csv files into tables hence creating a package for each file, the field names in the csv files match the field names in the table exactly but we also need to keep track of exactly which rows of data error as its vital to get the correct data into the database as its for a datawarehouse. We only have a script task at the beginning to set our variables for filenames etc.. and the clean up script is just a sql task used to delete data from the table should any errors occur.

    Ideally I would like to create each package programmatically based on the metadata from the data source files but don't really have time for this and I don't see any other approach.

    Many thanks

    Tuesday, October 19, 2010 9:06 AM
  • You cannot reference another task from script in the package, it just cannot be done.

    All the samples you see that use MainPipe and such like are the build or ammend and existing package, but that is extrenal to the package. A package cannot be self modifying. The subtle exception to this is at design-time when a tasks's user interface can influence other objects, but there is no exception at run-time.

    Perhaps try and assign the table name to a variable in the first place then make the OLE-DB Destination use that variable, along with the latter process you orginally wanted it for. Swap the dependency around.

    Generating a package in code may not be as hard as you think, the key is generaly working out how you can map the columns, the rest is easy. Perhaps could look at a hybrid solution, part generated, part user managed, or use an existing package and modify it in code.

    This may give you a start if you want to look at generating packages - http://www.sqlis.com/sqlis/post/Flat-File-To-Sql.aspx


    http://www.sqlis.com | http://www.konesans.com
    Tuesday, October 19, 2010 10:00 AM
    Moderator
  • Hi, the problem in using the variable as a table name is mapping the columns still becomes a manual task but I guess that's just SSIS the way it is, but I think will have a go at the programming route see how I get on, thanks for the link and info!!!

    Cheers


    Tuesday, October 19, 2010 10:08 AM