Merge Join is not working properly
-
Hi there
I really need some help and I thought, and will be really glad, you can help me. I am at college learning about BI and SQL Server Data Tools. Actually, I'm working on a class project and we need to create a FactTable with its respective Dimensions (also, we need to implement the updating process of both elements). As for now I'm just starting to fill the DW with the Dimensions, in order to create the FactTable, however Im having some issues while doing this. My project is about projects (a little redundant) so those projects have an ID (some of them are only numbers some others are mixed) and I have stored those IDs in a varchar column. Aprox. 7000 registers (repeated registers because one project ID can have subcodes... and so on) and through a Merge Join Im trying to bring the budget of that specific project ID as many times it is required. I mean, it doesnt matter that the project ID is repeated, just bring it and it into a new column. This is working perfectly for those registers with numeric project ID but for others, i.e. project ID B00001, the DataFlow is not bringing anything, just NULL. I also tried to do a Lookup but it is not working properly neither.
Any advise you can provide me with? Id really appreciate it.
* Sorry if I didnt keep it short!.
Have a nice day everybody. - camsuarezmun - Thursday, September 28, 2017 10:25 AMHi there I really need some help and I thought, and will be really glad, you can help me. I am at college learning about BI and SQL Server Data Tools. Actually, I'm working on a class project and we need to create a FactTable with its respective Dimensions (also, we need to implement the updating process of both elements). As for now I'm just starting to fill the DW with the Dimensions, in order to create the FactTable, however Im having some issues while doing this. My project is about projects (a little redundant) so those projects have an ID (some of them are only numbers some others are mixed) and I have stored those IDs in a varchar column. Aprox. 7000 registers (repeated registers because one project ID can have subcodes... and so on) and through a Merge Join Im trying to bring the budget of that specific project ID as many times it is required. I mean, it doesnt matter that the project ID is repeated, just bring it and it into a new column. This is working perfectly for those registers with numeric project ID but for others, i.e. project ID B00001, the DataFlow is not bringing anything, just NULL. I also tried to do a Lookup but it is not working properly neither.
Any advise you can provide me with? Id really appreciate it.
* Sorry if I didnt keep it short!.
Have a nice day everybody.Are you using Excel as your data source?
- Phil Parkin - Thursday, September 28, 2017 11:22 AMcamsuarezmun - Thursday, September 28, 2017 10:25 AMHi there I really need some help and I thought, and will be really glad, you can help me. I am at college learning about BI and SQL Server Data Tools. Actually, I'm working on a class project and we need to create a FactTable with its respective Dimensions (also, we need to implement the updating process of both elements). As for now I'm just starting to fill the DW with the Dimensions, in order to create the FactTable, however Im having some issues while doing this. My project is about projects (a little redundant) so those projects have an ID (some of them are only numbers some others are mixed) and I have stored those IDs in a varchar column. Aprox. 7000 registers (repeated registers because one project ID can have subcodes... and so on) and through a Merge Join Im trying to bring the budget of that specific project ID as many times it is required. I mean, it doesnt matter that the project ID is repeated, just bring it and it into a new column. This is working perfectly for those registers with numeric project ID but for others, i.e. project ID B00001, the DataFlow is not bringing anything, just NULL. I also tried to do a Lookup but it is not working properly neither.
Any advise you can provide me with? Id really appreciate it.
* Sorry if I didnt keep it short!.
Have a nice day everybody.Are you using Excel as your data source?
Hi Phil, first of all, thank you for putting your attention on my post.
The answer is yes. Im using a DB in SQL which contains the projects ID and their details and the other source where I am getting the budget is an Excel file. - camsuarezmun - Thursday, September 28, 2017 11:28 AM
My pleasure. As you've probably guessed, you have a datatype issue. Excel (actually, the ACE driver) samples the first few rows of data in the Excel file and makes a best guess about the datatype for each column based on that. If it guesses that the datatype for a particular column is numeric, any non-numeric data in that column is ignored.
The easiest solution is for you to export your data from Excel into a flat text file and use that as your source.
If that is not an option, post back and I'll give you another option (or you can search the internet for "IMEX=1 excel SSIS" and read for yourself.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply