Annoying Inner Join Problem

Greetings SSIS friends,

I have the following problem in SSIS and it's driving me nuts!!!!!

My situation is as follows :

Data Source 1 & Data Source 2are joined using a merge join. This bit works fine.

The output of the above join is then joined to a third data source but this time, I only get 63 rows coming through instead of 77097 even though the join key in the second merge join component is the same as the first one!!!

I thought I was going mad so I decided to see if the same problem occurs if I was to do this with T-SQL. I created 3 temporary tables for each of my data sources.

I did an inner join between tmpTable_Source1 and tmpTable_Source2, I then stored the result in tempTable4

The final inner join was performed between tempTable4 and tempTable3 and the result produced 77097 and not 63 rows.

What the hell is SSIS playing at?! The merge keys I used in T-SQL is the same one I used in my SSIS package!!!!

Are all the inputs of your merge joins actually sorted? The inputs require to be sorted; otherwise you will get unexpected results.|||

Hi Rafael,

Yes my inputs are sorted but anyway, I decided to do away with one of my data sources and combined it as part of the SQL command in my second data source.

Now I have another problem!!!

In my UNION ALL component I get an error for one of my Input fields. It says that the metadata for my input column does not match with the metadata for the associated output column. The trouble is, this was working just fine before and I haven't touched it!!!!

What would cause this kind of problem?!!

Your help would be much appreciated.

|||

Sorry Rafael,

I've solved this little problem. Looks like the data type for one of my input columns was a unicode string.

I will look in to your idea with the other saved package that I've got. Still unsure aboutt the ordering business but will try it and let you know.

Thanks for your help.

|||

Union all transforms are not very friendly when refreshing metadata. I have found faster to delete the faulty column mapping inside of the lookup and added it back. To delete it select the whole row representing the column with the problem and select delete from the right click menu. Then add it back using the drop down lists. Alternative, you could delete the whole Union all and place it back.

Analysis Services Role created via AMO has unknown problem.

Hi,

This is a wierd problem so I hope I can explain the situation clearly

I'm creating a single role via AMO for an Analysis Services database and adding domain users to the role just fine. When our 3rd party reporting tool tries to create a report an error gets generated and they bubble back the error shown below.

Now here is the wierd part...if I simply view the Role in SQL Server Managment Management Studio and click OK the problem goes away. If I click Cancel when viewing the Role the problem persists. Obviously when I click "OK" in the Role dialog some sort of save routine is going on and fixes whatever problem is present.

I'm creating the role using standard MSDN examples and everything looks in order. Any suggestions on how I can better track down what the problem is?

System.Exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) Thanks

Could you post the code you use to generate the role? I'll compare it to some code we have that is working. My first guess is there is probably something not quite right in the permissions.

Bryan

|||

To see what is different (between the role create with AMO and the role fixed by the dialog), you can do this:

- re-create the role with AMO

- connect with 'SQL Management Studio' and script the Create or Alter operation of the role (right click on the role and there should be the 'Script' option)

- run the role dialog, press 'OK'

- script again and compare with the previous script

Adrian Dumitrascu