Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

sql

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

Deploying Data Processing Extension to Report Designer

I have a problem getting my reports working in the designer in vs.net
2005. I have migrated a sql server 2000 report server project to 2005
and added my custom data processing extension to the config files. The
reports works in the report server but although I have added data
extension to both RSReportDesigner.config and given it full trust in
RSPreviewPolicy.config in much the same way as in the reportserver
config files I cant get it to work.
This is my entry in the RSReportDesigner.config in the Data Section:
<Extension Name="HM_SQL_3"
Type="HM.IPS.Report.DataExtension.PermissionProvider.Connection,
HM.IPS.Report.DataExtension"/>
And this is my entry in the RSPreviewPolicy.config:
<CodeGroup class="UnionCodeGroup" version="1"
PermissionSetName="FullTrust" Name="HM_IPSPermission3"
Description="Group for HM DataExtension3">
<IMembershipCondition
class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft Visual
Studio 8\Common7\IDE\PrivateAssemblies
\HM.IPS.Report.DataExtension.dll"/>
</CodeGroup>
and I still get :
an attempt has been made to use a data extension 'HM_SQL_3' that is
not registered for this report server
when I try to preview reports.
Does anyone have any idea what to try next? This worked flawlessly in
vs.net 2003 and as mentioned before it works when I use the Report
Server through the web.On Apr 11, 6:52 am, jonas.rosqv...@.ecru.se wrote:
> I have a problem getting my reports working in the designer in vs.net
> 2005. I have migrated a sql server 2000 report server project to 2005
> and added my custom data processing extension to the config files. The
> reports works in the report server but although I have added data
> extension to both RSReportDesigner.config and given it full trust in
> RSPreviewPolicy.config in much the same way as in the reportserver
> config files I cant get it to work.
> This is my entry in the RSReportDesigner.config in the Data Section:
> <Extension Name="HM_SQL_3"
> Type="HM.IPS.Report.DataExtension.PermissionProvider.Connection,
> HM.IPS.Report.DataExtension"/>
> And this is my entry in the RSPreviewPolicy.config:
> <CodeGroup class="UnionCodeGroup" version="1"
> PermissionSetName="FullTrust" Name="HM_IPSPermission3"
> Description="Group for HM DataExtension3">
> <IMembershipCondition
> class="UrlMembershipCondition" version="1"
> Url="C:\Program Files\Microsoft Visual
> Studio 8\Common7\IDE\PrivateAssemblies
> \HM.IPS.Report.DataExtension.dll"/>
> </CodeGroup>
> and I still get :
> an attempt has been made to use a data extension 'HM_SQL_3' that is
> not registered for this report server
> when I try to preview reports.
> Does anyone have any idea what to try next? This worked flawlessly in
> vs.net 2003 and as mentioned before it works when I use the Report
> Server through the web.
This link might be helpful (refer to Bryan E's final response):
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e1289b66066a07b9/0ba37f7653d8a851?lnk=st&q=an+attempt+has+been+made+to+use+a+data+extension+that+is&rnum=1#0ba37f7653d8a851
Regards,
Enrique Martinez
Sr. Software Consultant

Deny user roles from a Stored Procedure.

Hello,

I am trying to deny a user from processing a query, however the user still seems to be able to see the procedure.

I have executed the following command on both the 'Public' and specific user name.

DENY EXECUTE ON [OBJECT_NAME] TO [PUBLIC]

However when i run the command 'sp_helprotect OBJECT_NAME' afterwards it shows that deny is set on 'Public', yet the user can still run the procedure from a web report... I have tried restarting IIS and this also does nothing, any ideas?

Thanks,

RichAre you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.|||

Quote:

Originally Posted by rob313

Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.

the user might be a "dbo".|||If the user is dbo then SQL Server does not check any other permissions (grant or deny), so that would be why you are seeing this behavior. You will need to remove the user from dbo and grant that account the needed permissions or role.