changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

sql

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

Change tablerow height at runtime

Hi,
I need to change the height of a row at runtime, anyone knows how can
I do it?
Thanks for your help.On Oct 17, 5:26 am, BPiano <bernardo.pi...@.sapo.pt> wrote:
> Hi,
> I need to change the height of a row at runtime, anyone knows how can
> I do it?
> Thanks for your help.
There's not really a way to directly control this; however, if you are
wanting to increase the row/cell size across the board you can set the
'Textbox Height' property to: 'Can increase to accommodate
contents' (via: selecting a table cell in Layout view >> right-click
>> select Properties >> select Format tab). Something that could
possibly work is to set the BorderWidth: Top or Bottom property (via:
selecting a table cell in Layout view >> select F4/Properties Window
>> select drop-down to the right of item >> select <Expression...>) to
an expression. I'm not sure of your particular scenario; but you might
use an expression like:
=iif(Fields!SomeField.Value > 50, "5pt", "1pt")
Where if SomeField's value is greater than 50 set the border width for
the top to 5pt otherwise, set it to 1pt. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Change SQL Command at runtime?

I have tried some of the fixes for the other posts but it still doesn't work.
My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?Use an expression as your source. Note that once you do this in the dataset
tab that you can no longer execute the query or get the field list returned
so make sure you have the appropriate field list before you change it to an
expression.
="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
Parameters!myParam.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
>I have tried some of the fixes for the other posts but it still doesn't
>work.
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?|||How do I set the source? This query is a very large query with many other
parameters being used to narrow down the search and return data. I use a
text command type in the data tab.
"Bruce L-C [MVP]" wrote:
> Use an expression as your source. Note that once you do this in the dataset
> tab that you can no longer execute the query or get the field list returned
> so make sure you have the appropriate field list before you change it to an
> expression.
> ="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
> Parameters!myParam.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
> news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
> >I have tried some of the fixes for the other posts but it still doesn't
> >work.
> >
> > My query looks like this:
> > SELECT *
> > FROM table1 AS t1, table1 AS t2
> > WHERE t1.id =t2.id + @.myParam
> >
> > I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> >
> > How can I make this work?
>
>|||Ok I have it figured out. I left the existing parameters setup as is and
just put a =" at the beginning of the query, a " at the end, and took out all
Carriage Returns and it works. Thanks for all of your help!
"KCBTDev" wrote:
> How do I set the source? This query is a very large query with many other
> parameters being used to narrow down the search and return data. I use a
> text command type in the data tab.
> "Bruce L-C [MVP]" wrote:
> > Use an expression as your source. Note that once you do this in the dataset
> > tab that you can no longer execute the query or get the field list returned
> > so make sure you have the appropriate field list before you change it to an
> > expression.
> >
> > ="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
> > Parameters!myParam.Value
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
> > news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
> > >I have tried some of the fixes for the other posts but it still doesn't
> > >work.
> > >
> > > My query looks like this:
> > > SELECT *
> > > FROM table1 AS t1, table1 AS t2
> > > WHERE t1.id =t2.id + @.myParam
> > >
> > > I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> > >
> > > How can I make this work?
> >
> >
> >

Change SQL command at runtime using a stored procedure?

My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?
I did have my report working correctly with my source as an expression. But
with added complexities to the query, it will be easier to maintain in a
stored procedure.Sounds like you need to research dynamic sql
"KCBTDev" wrote:
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?
> I did have my report working correctly with my source as an expression. But
> with added complexities to the query, it will be easier to maintain in a
> stored procedure.

Change SQL (order by) at runtime?

I'm creating a fairly simple report using the ReportViewer control in
a Windows app. I have credentials and everything working, but I want
to change the SQL at run time. The dataset in the designer has this
SQL:
Select * from CPS.Main Order By Docket
Easy enough, but how can I replace that Docket with something at
runtime. I tried
Select * from CPS.Main Order By ?
and passed in a parameter. It runs, but ignores the Order By.
Am I going about this the wrong way? It's an ODBC database, not
SQLServer if that matters.First, one option for you is to allow the user to sort as they want. User
sortable columns is availabe starting in RS 2005.
Otherwise, what you want to do is to set the data source as an expression.
You are creating a string that equates to the SQL you want.
= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
Be sure you have the field list you want because once you switch to this you
will not get a field list refreshed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:rcggs31e1ni0a29noueeetlud5qugmguko@.4ax.com...
> I'm creating a fairly simple report using the ReportViewer control in
> a Windows app. I have credentials and everything working, but I want
> to change the SQL at run time. The dataset in the designer has this
> SQL:
> Select * from CPS.Main Order By Docket
> Easy enough, but how can I replace that Docket with something at
> runtime. I tried
> Select * from CPS.Main Order By ?
> and passed in a parameter. It runs, but ignores the Order By.
>
> Am I going about this the wrong way? It's an ODBC database, not
> SQLServer if that matters.|||On Fri, 29 Feb 2008 12:14:12 -0600, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>First, one option for you is to allow the user to sort as they want. User
>sortable columns is availabe starting in RS 2005.
>Otherwise, what you want to do is to set the data source as an expression.
>You are creating a string that equates to the SQL you want.
>= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
>Be sure you have the field list you want because once you switch to this you
>will not get a field list refreshed.
Not option one, each page is a separate law case, the Order By will be
putting those pages in some sort of order.
I was about to tell you that your second approach doesn't work,
because the red ! in the data tab of the designer (VS2005) is greyed
out, but it does work when switching to the Preview tab.
Thanks, problem solved.|||I should have said you don't get a field list AND you can't execute from the
dataset tab.
But, you figured that out yourself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:53lgs3l06d1gkp5k2cgrul7c16dq7kam80@.4ax.com...
> On Fri, 29 Feb 2008 12:14:12 -0600, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>First, one option for you is to allow the user to sort as they want. User
>>sortable columns is availabe starting in RS 2005.
>>Otherwise, what you want to do is to set the data source as an expression.
>>You are creating a string that equates to the SQL you want.
>>= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
>>Be sure you have the field list you want because once you switch to this
>>you
>>will not get a field list refreshed.
>
> Not option one, each page is a separate law case, the Order By will be
> putting those pages in some sort of order.
> I was about to tell you that your second approach doesn't work,
> because the red ! in the data tab of the designer (VS2005) is greyed
> out, but it does work when switching to the Preview tab.
> Thanks, problem solved.
>

Change Shared Data Source at runtime based on a parameter

I have 11 databases identical databases except that they have different usename and passwords. I want to setup 11 Shared Data Sources and set the report to choose a different Shared Data Source based off of a parameter. Is this possiable

Changing shared data sources dynamically is currently not supported.

The closest you can get through expressions in the report is to use a non-shared data source with an expression-based connection string. This option and other options are discussed in more detail in this relating thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16395&SiteID=1

-- Robert

Change report groups at RunTime

Hi,
Ho can we change the order of groups in Crystal report at runtime through VB code

Thanks in Advance
Manoj.

To make it more clear..
Eg.
Group X
Group Y
Group Z
Group M
...
...

But some time I want the groups like
Group X
Group M
Group Z
Group Y
...
...If you're passing a recordset to Crystal, you could just set the 'ORDER BY' in your query