none
Dynamic Oledb Destination RRS feed

  • Question

  • Hello,

    I am a beginner for the SSIS and would like to know how to modify the OLDEB Destination connectionString property at run time like using "for each loop container".

    My requirement is that I have a single source which would be Sql Server 2005 and my destination is in MS-Access database residing in 100 places. I do not want to manually design in the data flow to these 100 destinations.

    I have all the destinations stored in a table and would like to pick these destinations from the table and loop through the same at run time by modifying destination connection string.

    I have planned using dts but the for each loop container does not work through as it works with flat file connection manager , but does not go well with OLDEB connection.

    Highly appreciate any help in this regard.

    Regards

    Sameer

    Sunday, February 5, 2006 6:11 AM

Answers

  • You would use a variable to hold the table name, and use the "Table name or view name variable" option under Data Access Mode in the properties page for the OLE DB Destination.

     

    Monday, November 12, 2007 4:12 PM
    Moderator

All replies

  • Sameer,

    The OLE DB Destination does not have a connection string property however the OLE DB Connection Manager that it uses DOES. You can change this connection string so that you are pointing at (e.g.) a different server or a different .mdb file.

    What you cannot do however is change the name of the table in the OLE DB Destination that you are inserting into. Well actually you can but the metadata (i.e. column names, types) of the table into which you are inserting must be the same as that selected at design-time.

    If you are inserting into 100 identical MS Access databases then you can do this by modifying the connection string property of the OLE DB Connection Manager and looping using the For Each Loop container. If you want to insert to 100 different tables then you need 100 different data-flows.

    -Jamie

     

     

    Sunday, February 5, 2006 7:20 PM
    Moderator
  • Jamie,

    Thanks for the reply. Yes you are right it is the OLEDB Connection manger.

    My requirement is that the these are 100 identical MS access tables which needs to be loaded with production data after every 1 hour.  Would like to know the details of looping the For Each loop container using my Configuration table, which holds information about MDB file locations. How do I configure the For each loop container to point to the Configuration table.  I have now started feeling that connection manager goes well only with the flat file and does not go well with OLEDB.

    Can you provide me a small example which loops the files. Also these access files are spread across the network, and many time the network connectiion goes down, hence I have made an extra column in my table which updates that the data has reached/Unreached to final destination. How do i capture the same in SSIS. In old traditional way of DTS I remember I used to have privledge of doing the

    Set oPkg = DTSGlobalVariables.Parent through which I could find out the execution result like this, which is oPkg.steps(i).ExecutionResult. How do I achieve the same in SSIS.

    Again if you could provide me an hint or working example that would be great. I do have working example for flat file connection manager , but I am unable to replicate the same using OLEDB destination.

    Thanks in advance for the suggestions

    Regards

     

    Monday, February 6, 2006 3:28 AM
  •  

    Greetings Sameer,

     

    I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

     

    my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

     

    Any feedback you can provide would be greatly appreciated.

     

    Thanks

    Monday, August 20, 2007 3:19 PM
  •  bowmandba wrote:

     

    Greetings Sameer,

     

    I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

     

    my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

     

    Any feedback you can provide would be greatly appreciated.

     

    Thanks

     

    You need to use expressions.

     

    SSIS>>Expressions

    (http://blogs.conchango.com/jamiethomson/archive/tags/SSIS/Expressions/default.aspx)

     

    Any questions, reply here.

     

    -Jamie

     

    Monday, August 20, 2007 4:27 PM
    Moderator
  • Jamie:

     

    In your post, you noted the following "Well actually you can but the metadata (i.e. column names, types) of the table into which you are inserting must be the same as that selected at design-time".

    I am wondering how you would go about setting the table name for an OLE DB Destination at run-time?  I cannot determine how to do this using Expressions.

     

    -Sean Fitzgerald

    Monday, November 12, 2007 4:01 PM
  • You would use a variable to hold the table name, and use the "Table name or view name variable" option under Data Access Mode in the properties page for the OLE DB Destination.

     

    Monday, November 12, 2007 4:12 PM
    Moderator
  • Exactly what I was looking for.  Thank you for your prompt response!

    Monday, November 12, 2007 8:21 PM
  • When I am executing DTS package in SQL 2008 I am getting this error-

      " Description: The Maximum insert commit size property of the OLE DB destination "OLE DB Destination" is set to 0. This property setting can cause the running package to stop responding. For more information, see the F1 Help topic for OLE DB Destination"

    I want to know where is oledb destination ? so I can set The Maximum insert commit size property

    please help ........

    thanks
    Prakash
    Friday, February 6, 2009 11:24 AM
  • (Please don't post on a really old thread with a new question.)

    Open your package and look in your Data Flow Tasks.  One of your data flow tasks will have an object called "OLE DB Destination" in it, with a maximum insert commit size set to zero.
    Todd McDermid's Blog
    Friday, February 6, 2009 5:20 PM
    Moderator