Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think it is possible. I would make this way:</p> <ol> <li>Foreach Loop's Collection should be "Foreach Item Enumerator". At this point in the Enumerator Configuration panel you can make a little table. Add a column with a type string. Here will come the connection strings of the databases.</li> <li>Create a string variable and associate it with this column: Go to Variable Mappings in Foreach Loop editor, choose the string variable (e.g. User::ConnString), give index 0. The loop will iterate through the entries you set in first step, and you can access the values through this User::ConnString variable.</li> <li>Add a Data Flow task in to the Foreach Loop container. Inside set up an OLE DB Source. It needs a connection manager. Select the connection manager associated with OLE DB Connection, go to Propertes, Expressions. Select the ConnectionString property, and type @[User::ConnString] to expression column (or use your variable name).</li> <li>Locate all connection string you currently use and type them to Enumeration Items. </li> </ol> <p>I think with these steps you can simplify your data flow.</p> <p>This process assumes that you have the same table names in all databases. In other case you can also iterate the table names to connect to. (Second column in the Enumeration configuration and fill the cells with the apropriate table names. You also need a second variable.)</p> <p>Of course you not have to copy the whole connection string but the changing part of it. I this case you should form your expression appropriately.</p> <p><strong>EDIT</strong></p> <p>You are right. There are some problem with this example. (I think it's a bit complicated for your solution, but it can be used.) </p> <p>The first one is (as you mentioned) it did not pass the values to the variables. I would complete the 6th step with these: After you set User::SourceList as ADO object source variable, select Variable Mappings on the left side. In Variables column choose User::ConnString first (set index to 0), then User::SourceTable second (set index to 1). This will solve your second edit with the MessageBox. From now on it should show the right values.</p> <p>The second problem is what you wrote, with this truncation error. If you look at the error messages carefully they say that some string columns have length of 1. And that's why some truncation occures. To solve this, right click OLE DB source, choose Show Advanced editor. Go to the last tab (Input and Output Properties), expand OLE DB Source output/Output columns. Choose TableName first. On the right side, in Data Type Properties you should see Lenght = 1. (SSIS could not correctly determine the length of this column, because you choose SQL command variable as Data access mode, I guess). According to the Result table definition, set this value to 128. Also change the length of ConnString column from 1 to 256.</p> <p>After these changes the example should work. (In my case it worked.) This example uses much the same concept that I wrote, just it gets the connection string from a data store but not from inside the package itself. Former can be a more robust solution, I accept. I hope you can make your package work. Please, let me know if not.</p>
    singulars
    1. This table or related slice is empty.
    1. POssis - using Foreach to read from multipe dbs
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USluviktor
    1. USluviktor
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POssis - using Foreach to read from multipe dbs
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COHi, I have edited my question, could you please check it? Thanks!
      singulars
      1. PO
      1. USilija veselica
    2. COLook the edited answer.
      singulars
      1. PO
      1. USluviktor
    3. COIt works, in the meantime I figured that these queries are not what I need so I wrote my own, but this part with variable mapping is just what I needed. Thanks!!
      singulars
      1. PO
      1. USilija veselica
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload