loop through certain files

Using SSIS foreach loop, I am looping through files to get the filenames and pass them on as variable.
How is it possible to loop through the files but only pass the files that do not end with ..._Parameters.xml? or loop through only the ones which end with ..._Parameters.xml
I think this is to do with the scripting or expression in the foreachloop?
ThanksYou could use a script task to determine which path to take (i.e. is _Parameters.xml or isNot _Paremeters.xml). One way you could do this would be by using the "Fail" path if the task if it is _Parameters.xml, and otherwise use the sucess path. I'm not sure if this is the best way to go about this or not though... (You would need to make sure that this task would not cause the container to fail when it does)|||Can't you set the files property (available in the dialog) to "*_Parameters.xml" to get the ones that do match?|||

Good thinking.

Done that now.

Thanks

sql

loop through all stored procedures in database and modify.

I have a need to loop through all stored procedures within a database and modify the procedure if it meets certain criteria. Using .NET I was able to develop a project to loop through the stored procedures, but that's as far as I got. What I need to do is open the stored procedure, search for a certain text string, and change it. Does anybody have a suggestion as to how I can accomplish this? Thanks.

Karen

create the script of all the sp to a single file (which is possible in EM or SSMS) . then replace the string by FIND & REPLACE method .. then run the script in the database, remember to script the permission too...

Madhu

|||

Hmm.

I have two servers with about 100 databases total. Each database uses a single stored procedure that may be different from the ones in the other databases. I wanted to be able to open the stored procedure, search for the string, then modify the text and save the script in an automated way. And I was wondering if SQLDMO can be used through .NET.

|||

You can get the script of this storedprocedure to a file using SQLCMD. but replacing the text u will have to do by .NET program.

read about SQLCMD

SQLCMD -q"SP_HelpText 'Your SP Name'" -o "Outputfile.sql"

Madhu

|||If you are using SQL Server 2000, all of this is available via SQLDMO. You can get a collection of the databases, loop across all of the databases, get a list of procedures, loop across the procedures and extract the definition, then use the string functions in .NET to find and replace. If this is SQL Server 2005, the same capabilities are available with SMO. The samples that ship with SQL Server pretty much have all of the code that you would need.

loop over fields?

Hi,
i have to check all fields for a certain value, can I loop over all fields
in a custom code function?
Thx, NigelHello,
I have a similar situation as to the one you've described below. Did you
ever get an answer to your question or did you come up with a solution that
you can share?
Thanks,
Dave
"Nigel Jensen" wrote:
> Hi,
> i have to check all fields for a certain value, can I loop over all fields
> in a custom code function?
> Thx, Nigel
>
>

Loooonnngggg delay in loading due to "pre-validation". Can it be turned OFF?

During dev. I _know_ certain things are invalid. I don't need the validation, it just wastes a lot of time...

You can set SSIS to be offline (SSIS Menu), or set the delay validation on the component.

Both these mean you can't validate the component at design time.

|||

Perfect. Thanks!

And thanks for not saying "RTFM"! (I have been reading but all it takes is one moment of lack of concentration and you miss some detail like this).

Ken

Looking for query equivalent

To extract a certain type of information we use a query like this in Oracle:

WHERE MOD(TRUNC(ACCOUNT_TYPE / POWER(2,0)),1) > 0

I am hoping someone can help me with the equivalent query syntax for sql server

Scott

Does POWER(2,0) mean "two to the zero power"? If so, why not just write 1? It appears that POWER(2,x) mean the same in both dialects.

Does MOD(x,1) mean MODULUS? Are you wanting the fractional part of x? If so try x - floor(x)

I am guessing that floor(x) is the same as TRUNC(x)

The "Modulus" operator in SQL server is the "%" operator; for instance 17%3 = 2; 21%10=1

Give a look to "Mathematical Functions" in books online

(Somebody check me, please)

|||

Do would it look something like this:

MOD(m,n) - where mod returns the remainder of m divided by n

TRUNC(num, x) - A number is truncated to x decimal places

Power(x,y) - x raised to the y power

Oracle Version: MOD(TRUNC(ACCOUNT_TYPE / POWER(2, $x)), 2) > 0

SQL Version: floor(ACCOUNT_TYPE / POWER(2, $x))% 2) > 0

Maybe?

|||

If your looking to deterine the xth bit, probably:

set nocount on
declare @.mockUp table
( ACCOUNT_TYPE integer,
powerOfTwo tinyint
)
insert into @.mockUp values (29, 3)
insert into @.mockUp values (14, 0)
insert into @.mockUp values (3, 2)
insert into @.mockup values (900, 7)
insert into @.mockup values (625, 5)
insert into @.mockup values (convert(int, 0xffffffff), 1)
insert into @.mockup values (convert(int, 0xffffffff), 2)
insert into @.mockup values (convert(int, 0xffffffff), 3)
insert into @.mockup values (convert(int, 0xffffffff), 4)
insert into @.mockup values (convert(int, 0xffffffff), 5)
insert into @.mockup values (convert(int, 0xffffffff), 6)
insert into @.mockup values (convert(int, 0xffffffff), 7)
insert into @.mockup values (convert(int, 0xffffffff), 8)
insert into @.mockup values (convert(int, 0xffffffff), 9)
insert into @.mockup values (convert(int, 0xffffffff), 10)
insert into @.mockup values (convert(int, 0xffffffff), 11)
insert into @.mockup values (convert(int, 0xffffffff), 12)
insert into @.mockup values (convert(int, 0xffffffff), 13)
insert into @.mockup values (convert(int, 0xffffffff), 14)
insert into @.mockup values (convert(int, 0xffffffff), 15)
insert into @.mockup values (convert(int, 0xffffffff), 16)
insert into @.mockup values (convert(int, 0xffffffff), 17)
insert into @.mockup values (convert(int, 0xffffffff), 18)
insert into @.mockup values (convert(int, 0xffffffff), 19)
insert into @.mockup values (convert(int, 0xffffffff), 20)
insert into @.mockup values (convert(int, 0xffffffff), 21)
insert into @.mockup values (convert(int, 0xffffffff), 22)
insert into @.mockup values (convert(int, 0xffffffff), 23)
insert into @.mockup values (convert(int, 0xffffffff), 24)
insert into @.mockup values (convert(int, 0xffffffff), 25)
insert into @.mockup values (convert(int, 0xffffffff), 26)
insert into @.mockup values (convert(int, 0xffffffff), 27)
insert into @.mockup values (convert(int, 0xffffffff), 28)
insert into @.mockup values (convert(int, 0xffffffff), 29)
insert into @.mockup values (convert(int, 0xffffffff), 30)

select ACCOUNT_TYPE,
powerOfTwo,
floor(ACCOUNT_TYPE / POWER(2, powerOfTwo))% 2 as [floor routine],
(ACCOUNT_TYPE & POWER(2, powerOfTwo))/power(2, powerOfTwo) as [Masked routine]
from @.mockUp

-- ACCOUNT_TYPE powerOfTwo floor routine Masked routine
-- - - --
-- 29 3 1 1
-- 14 0 0 0
-- 3 2 0 0
-- 900 7 1 1
-- 625 5 1 1
-- -1 1 0 1
-- -1 2 0 1
-- ...
-- -1 29 0 1
-- -1 30 0 1