Foreach Loop based on Variable String separated by Semi-Colon Items?

Hi,

How can i integrate a foreach loop based on a variable that have for example "ONE;TWO;THREE" and i want to run for ONE, TWO and for THREE items...

Is this possible using the foreach task? Or do i have to code it in a script (not a good ideia)?
Best Regards,
Luis Sim?esNot sure if you can do this using what is provided but if not then you can write your own enumerator.

-Jamie|||How can i write my own enumerator? And how is it integrated in production servers?

Best Regards,
Luis Sim?es|||

Hello LUIS,

Could you pse give more details. a For Loop initially sounds more like what

you may like.

The variable that holds "ONE;TWO;THREE". You would have to convert the text

Three into 3 or if you know everything starts at ONE then you could count

the elements in the array using a Script task and assign that to a variable.

Why are you using the ForEach enum?

Is it that you want to do something like enumerate over a directory and only

pick up say 3 files?

Thanks

Allan

> Hi,

>

> How can i integrate a foreach loop based on a variable that have for

> example "ONE;TWO;THREE" and i want to run for ONE, TWO and for THREE

> items...

>

> Is this possible using the foreach task? Or do i have to code it in a

> script (not a good ideia)?

>

> Best Regards,

> Luis Sim?es

|||I have solved the problem by creating a specific foreach enumerator.

But in this case this is what i needed:

Imagine i pass parameters over a variable and those parameters are sent separated by a semi-colon.

For example company's names:

variable1 = "company1;company2;company3"

and i want to execute some task's for "company1", "company2" and so on...
Best Regards,
Luis Sim?es|||The easiest way to do this would be to have a script task inside a for loop that strips off the first item in the string and stores it in a variable. You can then use that variable in whatever way you want to.

When the string is empty - set a boolean variable to false. The for loop should only execute if that boolean variable is true.

-Jamie

Forcing a Job Failure Based on time

Hi everyone.
I have a SQL job in which the last step calls an outside cmd batch. This batch often gets suspended indefinitely. In order to allow the SQL job to complete I periodically run a scheduled command that kills the suspended batch. This allows the SQL job t
o end, but it does not end it in a failure. The job reports successful execution. Is there a creative way to force the job to return failure status in this case?
I appreciate your help
Oscar
Is there a way that you could include some commands within that non-SQL job
that will handle the timeout?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Forcing a Job Failure Based on time

Hi everyone.
I have a SQL job in which the last step calls an outside cmd batch. This ba
tch often gets suspended indefinitely. In order to allow the SQL job to com
plete I periodically run a scheduled command that kills the suspended batch.
This allows the SQL job t
o end, but it does not end it in a failure. The job reports successful exec
ution. Is there a creative way to force the job to return failure status in
this case?
I appreciate your help
OscarIs there a way that you could include some commands within that non-SQL job
that will handle the timeout?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Forcing a Job Failure Based on time

Hi everyone.
I have a SQL job in which the last step calls an outside cmd batch. This batch often gets suspended indefinitely. In order to allow the SQL job to complete I periodically run a scheduled command that kills the suspended batch. This allows the SQL job to end, but it does not end it in a failure. The job reports successful execution. Is there a creative way to force the job to return failure status in this case?
I appreciate your help
OscarIs there a way that you could include some commands within that non-SQL job
that will handle the timeout?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

FOR XML AUTO returns incomplete xml

I have two SQL tables that are populated based on data in SQL system tables.
When I run the FOR XML AUTO select statement on these tables, certain fields
will be missing end tags AND the data will not all be returned. In some
cases the end tags are there but the data is incomplete. When running the
query against the actual system table I'll get similar results but not
exactly the same. Any suggestions?
Using sql 2000 sp3. SQLXML 3 sp2
--master..sysaltfiles table data. Return only a few db's and an incomplete
select rtrim(filename) filename from mridiag..tbldbfiles for xml auto
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Data.MDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Log.LDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\master.mdf
NT\data\pubs_log.ldf
(24 row(s) affected)
--master..sysprocesses table. Only returns 1 incomplete record
select LASTWAITTYPE test from master..sysprocesses for xml auto
<master..sysprocesses test="SLEEP
(15 row(s) affected)
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
>I have two SQL tables that are populated based on data in SQL system
>tables.
> When I run the FOR XML AUTO select statement on these tables, certain
> fields
> will be missing end tags AND the data will not all be returned. In some
> cases the end tags are there but the data is incomplete. When running the
> query against the actual system table I'll get similar results but not
> exactly the same. Any suggestions?
What client are you using to retrieve the results?
You might also check this FAQ:
http://sqlxml.org/faqs.aspx?faq=76
Bryant
|||Thanks for the response.
I reviewed the link to the FAQ and compared it to how I'm doing it. First,
the information I posted was using Query Analyzer but I get the same results
when executing it from my vb script.
I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
link I based my code on is below. It's basically the same as the "VB
Example" on the faq you pointed me to but the version of XML on the FAQ is
3.0 and the version used in my script is 4.0. Other than that, I can't see
any differences.
Any more suggestions or questions? It really has me puzzled.
Thanks again.
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||Here is the link I mentioned.
http://www.sqlxml.org/faqs.aspx?faq=10
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||The query analyzer is using ODBC and not the OLEDB stream object and thus
only get junked XML back. Also, unless you increase the number of bytes
displayed per line, it does drop information.
If you are using the SQLOLEDB stream interface, you should get the XML back.
Can you try it with the SQLXML HTTP component to see if the XML is correctly
generated by the FOR XML query?
Thanks
Michael
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:9679959F-BC15-48CC-B4F9-7B521D883DCC@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I reviewed the link to the FAQ and compared it to how I'm doing it.
> First,
> the information I posted was using Query Analyzer but I get the same
> results
> when executing it from my vb script.
> I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
> link I based my code on is below. It's basically the same as the "VB
> Example" on the faq you pointed me to but the version of XML on the FAQ is
> 3.0 and the version used in my script is 4.0. Other than that, I can't
> see
> any differences.
> Any more suggestions or questions? It really has me puzzled.
> Thanks again.
> "Bryant Likes" wrote:

For Loop Container with Dates

I'm trying to set up a For Loop Container that steps through the loop based on date logic.

Here's an example of what I'd like to do:

InitExpression | @.SomeDate = 1/1/2007

EvalExpression | @.SomeDate < GETDATE()

AssignExpression | @.SomeDate = DATEADD(DAY, 1, @.SomeDate)

The above syntax does not work, and I can't find an example that uses date logic with the For Loop Container.

Can anyone help me with this, or point me to an example that uses the For Loop Container with dats?

Thanks in advance for the help!

Is @.SomeDate an SSIS variable of DateTime type? Make it so.

Otherwise it looks fine. I tried this expression as my AssignExpression and it seemed to work:
@.SomeDate = dateadd("d",1,@.SomeDate)

Are you getting any errors that you care to share with us? That usually helps the most.|||

Thanks for the prompt reply!

I think this is my problem. Here's the message I get:

Error at SomeDate Loop: The data types "DT_DATE" and "DT_I4" are incompatible for binary operator "<". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Where would I configure the data type for @.SomeDate? I created a package variable called SomeDate and set the data type there. I guess there is not a direct link between these variable?

Thanks again for the help!

|||Yep, you have that right, but you are missing quotes.

InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)|||

Phil Brammer wrote:

Yep, you have that right, but you are missing quotes.

InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)

I guess it's not strictly required to have the double quotes when it comes to the parser. Just make sure that the user variable, SomeDate that you created in SSIS has a DateTime data type and that it is scoped properly.|||

Please pardon my ignorance, I'm still not getting this.

I have the following variable:

Name Scope Data Type

SomeDate Package DateTime

The For Loop has the following:

InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)

Here is the error I receive:

Error at SomeDate Loop: The data types "DT_DATE" and "DT_WSTR" are incompatible for binary operator "<". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Thanks again for your patience and assistance.

|||Select the For loop and ensure that you don't have another variable of the same name, SomeDate, scoped to the for loop. (You can have the same named variables but with different scopes.) I'm expecting to see a SomeDate listed somewhere with a data type of String.

FOR EACH Loop to export files based on Header Record

Hey guys,,

Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..

I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.

I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..

Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..

Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..

thanks again, scotty

I just created my first blog entry with something for you; let me know if it helps.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

Thanks Rafael.. This is exactly what i wanted to do and you have described the process exactly

Much appriciated..

Merry xmas and have a happpy new year..

Thaks

Scotty