How can I put inside my Look and Feel?

How can I put inside my Look and Feel?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 2.
MIEMBRO ACTIVO DE ALIANZADEVNot really sure if this is what you are looking for, but do a search these
boards for:
modifying the home page
It should return a single post which has info on changing the look of the
page.
"Luis Esteban Valencia" wrote:
> How can I put inside my Look and Feel?
>
>
>
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 2.
> MIEMBRO ACTIVO DE ALIANZADEV
>
>

How can I open an OS text file inside one Stored Procedure

HI,
I have one Operating System text file in some directory in UNIX environment . How can I open this file inside one Stored Procedure.
Thanks in advance.
DillipHello,

to open flat files in the OS use the package SYS.UTL_FILE. You can use the functions/procedures fopen, get_line and fclose to access the datas in the ff.

You get direct access to the file you must enter the path of the file in your initial parameter. Otherwise Oracle can not read or write the file.

The parameter is UTL_FILE_DIR.

Here is a short example:

declare
fptr utl_file.file_type;
buff varchar2(2048);
line_no number(10):=0;
loc_no integer;
begin
fptr:=utl_file.fopen('C:\Oracle\admin\PENT\udump', 'ORA00324.TRC','R');
utl_file.get_line(fptr,buff);
utl_file.fclose(fptr);
exception
when no_data_found then
utl_file.fclose(fptr);
dbms_output.put_line('Number of lines parsed ='||line_no);
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
raise_application_error(-20100,'file error');
when utl_file.invalid_mode then
dbms_output.put_line('invalid_mode');
raise_application_error(-20100,'file error');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid_filehandle');
raise_application_error(-20100,'file error');
when utl_file.invalid_operation then
dbms_output.put_line('invalid_operation');
raise_application_error(-20100,'file error');
when utl_file.read_error then
dbms_output.put_line('read_error');
raise_application_error(-20100,'file error');
when utl_file.write_error then
dbms_output.put_line('write_error');
raise_application_error(-20100,'file error');
when utl_file.internal_error then
dbms_output.put_line('internal_error');
raise_application_error(-20100,'file error');
when others then
dbms_output.put_line('un-handled');
raise_application_error(-20100,'file error');
end;

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie

How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie

How can I get a table reference knowing his name inside a system f

I want to call the system function
DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
parameter a @.TableName varchar variable represanting the table's name.
DBCC CHECKIDENT( ) requires as input the table object, while I have the
table string name.
Is there any way that I can get the table object using its string name
inside the DBCC CHECKIDENT() function ?
example
Create procedure procName (@.TableName varchar)
begin
.......
DBCC CHECKIDENT( theTableObject , RESEED, 100)
......
end;Aigiris, Try using the OBJECT_ID function. From the BOL below. - RLF
OBJECT_ID
Returns the database object identification number.
Syntax
OBJECT_ID ( 'object' )
"Argiris Petromelidis" <Argiris Petromelidis@.discussions.microsoft.com>
wrote in message news:AC4CE899-E310-47C3-9101-B12779C2FDA8@.microsoft.com...
>I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @.TableName varchar variable represanting the table's name.
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
> example
> Create procedure procName (@.TableName varchar)
> begin
> ........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ......
> end;|||Hi
You may want to try using dynamic SQL e.g.
CREATE PROCEDURE MyCheck ( @.objectname sysname )
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@.objectname)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
AND EXISTS ( SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(@.objectname)
AND COLUMNPROPERTY(id,name,'IsIdentity') = 1)
BEGIN
DECLARE @.cmd varchar(8000)
SET @.cmd = 'DBCC CHECKIDENT( ''' + QUOTENAME(@.objectname) + ''', RESEED,
100)'
EXEC (@.cmd)
END
John
"Argiris Petromelidis" wrote:

> I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @.TableName varchar variable represanting the table's name.
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
> example
> Create procedure procName (@.TableName varchar)
> begin
> ........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ......
> end;