Automatic synchronization of lookup columns

Data Flow:

Step 1: I use a OleDb Source Task to read data from a table (say table A) with 5 columns.

Step 2: Then I use a lookup task to add a sixth column (resulting from lookup on table B).

Step 3: Then I save the 6 columns to table C using a OleDb Destination Task.

Now, I go to underlying database and modify the tables like:

- remove 1 column from table A (not the column used for lookup later).
- remove 1 column from table B (not the columns used for lookup and not the columns retrieved after lookup)
- remove 1 column from table C (same as the one removed from table A).

If I get back to SSIS designer, it is nice enough to show warnings and ask whether I want to fix the metadata mismatch automatically. But, it does this for OleDb Source and Destination tasks only and NOT for lookup task. If I open the lookup task and hit the columns tab, it still shows old coulmn set for table B. It will be nice if these can be fixed automatically too. Or is it too much to ask for?

thanks,
Nitesh

Yes it'd be nice :)
You will notice similar behaviors also in other trasformations in the data flow (like Union All eg.). If you modify the structure of your data sources, you do need to check almost all of the sources, tasks and destinations involved.
Some months ago i asked in the beta place for a "Synchronize All" botton in the Data Flow editor form. But perhaps there will be better solutions then mine.
|||

Can you open a DCR for this?

thanks

Automate the configuration of the Query Log

Hi

I am looking at automating an AS 2005 build which will include setting up the Query Log. Can this step be automated.

Thanks

There are a couple of server properties for QueryLog settings (you can see all of them with SQL Management Studio: right click on the server item, chose 'Properties' and look for 'Log \ QueryLog' items). The query log settings are per entire server.

To automate setting up query log settings, here are some options:

1. Script the Server Properties dialog (from SQL Management Studio - the dialog has a 'Script' button at the top) and then run the script with an Integration Services task or with AMO. You can remove from the script all the other server properties that you don't want to touch (keeping the script small, clear and safe).

To run the script with AMO, this is sample C# code:

(in your C# project, add a reference to Microsoft.AnalysisServices.DLL from '%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies')

...
using Microsoft.AnalysisServices;
...

Server s = new Server();

s.Connect("localhost");

try
{
XmlaResultCollection results = s.Execute("Put here the script copy-pasted from SQL Management Studio");

foreach (XmlaResult result in results)
{
foreach( XmlaMessage message in result.Messages )
{
Console.WriteLine(message.Description);
if (message is XmlaError)
{
// FAILURE: the script failed, you need to do something here
}
}
}
}
finally
{
s.Disconnect();
}

2. Use AMO to change the server properties. Sample code:

...
using Microsoft.AnalysisServices;
...

Server s = new Server();

s.Connect("localhost");

try
{
ServerProperty sp;

// setup the query log file path
sp = s.ServerProperties[@."Log\QueryLog\QueryLogFileName"]; // this throws exception if lookup fails
sp.Value = @."c:\MyQueryLog.txt";

// setup the query log file size
sp = s.ServerProperties[@."Log\QueryLog\QueryLogFileSize"]; // this throws exception if lookup fails
sp.Value = "10"; // 10 MB

// Now save everything; the properties we set don't require server restart.
s.Update();
}
finally
{
s.Disconnect();
}

Adrian Dumitrascu

|||

Adrian

Cheers i'll give it a try.