Hi,
How to exclude a filter on a dataset such that I may either apply the filter or not?
I would like to control that through a boolean report parameter.
Edmund
try creating a stored procedure as your dataset. in the sp write an If statement. this lets you set up logic so thet a different query can be run e.g if "true" is selected query A is run filter applied
if "false" is selected query b can be run with no filter
I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? 
See if the aggregate transformation can help you...
This paper has also some suggestions:
http://technet.microsoft.com/en-us/library/aa964137.aspx|||
Can you avoid creating duplicates in the first place? Perhpas the Conditional Split could be used instead of the Multicast?
I have found Sort to be the best de-duplication option, generally faster than the aggregate, but test it with your data if performance is an issue.
I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite?
See if the aggregate transformation can help you...
This paper has also some suggestions:
http://technet.microsoft.com/en-us/library/aa964137.aspx|||
Can you avoid creating duplicates in the first place? Perhpas the Conditional Split could be used instead of the Multicast?
I have found Sort to be the best de-duplication option, generally faster than the aggregate, but test it with your data if performance is an issue.
Hi,
I need to load data from excel files which will be provided by a number (around 100 monthly) of external suppliers, so we don't get 100% control over the files themselves.
What my solution involves is copying the excel file to a common name (e.g. supplierExcel.xls), turning this into a pipe delimited txt and then loading the txt. I had trouble switching files when trying to load directly from excel.
All these files should arrive in the same format of 36 fields and of course in the right order; there will be rejections if they fail.
I've come across a problem extracting the data from excel where I'm getting 'the value could not be converted because of a potential loss of data' on field 1. It only happens on excel files where there is a quote mark as the first character and I have loaded other files quite happily without the quotemark.
Has anyone seen this before? Is this a known issue and how can I get around it, without recourse to manually changing the individual files?
Thanks
nathan
Hi,
It is difficult to answer, you should be more specific on a couple of things.
- Why cannot you load direct from Excel. this could be the only fix you need, You could use the File System task.
- How do you convert the Excel to text
- Do you use table load or SQL Command
- Do you need the quote when it is the initial char? In Excel it means you want to force the cell content to be text.
If you do not need the quote, strip it from the file.
declare @.sometext as varchar(255)
set @.sometext = '''There is an initial '' in this test string'
select substring(@.sometext, patindex('''%',@.sometext)+1,255)
if you need te quote, try to replace it by 3 quotes or make sure the entry is not too long, May be you could do this
declare @.sometext as varchar(255)
set @.sometext = '''There is an initial '' in this test string'
select case left(@.sometext,1) when char(39) then char(39) + char(39) + @.sometext else @.sometext end
.Philippe
