ForEachLoop Container and Variables

Hi Guys

I am trying to do the following and am quite new to SSIS.

I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.

I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.

I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.

I have then created another SQL task insert the Foreach which will perform the Update/Insert.

obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.

having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.

Which of the properties from the drop down do i need to use to map the variables against?

Any help would be massively appreciated asI am tearing my hair out!

Thanks

Scott

Hi Scott,

We're all still learning SSIS.

It sounds like you're most of the way there.

There are a couple ways to approach this solution. The simplest way, from what I understand from your post, is to use placeholders and parameters in your Update/Insert statements. If you already have the Code and Supplier variables defined, you could perform an insert using an Execute SQL Task with something similar to the following code:

Code Snippet

INSERT INTO Vendors

(Code, Supplier)

VALUES(?, ?)

You could then supply Parameters:

Code Snippet

VariableName Direction DataType ParameterName ParameterSize

User::Code Input Int 0 -1

User::Supplier Input VarChar 1 -1

This would substitute the question marks in the SQL Statement property with the values contained in your variables.

Hope this helps,

Andy

|||

Scott,

Any special reason for not using a dataflow with a lookup transform to detect if the rows exists(update) or not (insert). That is by far a pretty common practice in these scenarios.

|||

Hi Rafael

Still new to this (and database stuff as a whole) and am going on someone elses advice!

I have looked at your suggestion and have got as far as the following:

OLEDB Source with a SQL select statement to return the data required

Look Up transform to look up the 2 columns from the Select against the destination table

After that I am a bit lost. I guess i have to add a OLEDB destination but do I do it to a table or a SQL Command?

thanks again

Scott

|||

I think you are on the right track. I would add an OLE DB Destination against the destination table.

Keep in mind you have to tweak the lookup to 'redirect' errors. Lookup will treat the no matches as errors; hence will be send to the error output of the component (red arrow). Then you have to connect the error output of the Lup to the input of the destination.

Now the updates; every row going to the green output of the L.up is an existing/to-updated row. Here you have 2 options; use an OLE DB Commnad to update the row in the destination table; or send those rows to an estiging table (yes a seconf OLE DB Destination) and then back in control flow use an Execute SQl task to do a 1 time update. The advantage of the second method is performance. the Update runs 1 time updating all the required rows. The First one will perform an update for every row passing trhough; wich depending on the volume of data can be performance killer; the good thing is that you don't need a second table.

This thread has some examples

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

Foreach Loop read table data and write to file

Hi,

I want to do the following with a ssis package:

INPUT:

A table contains 2 columns with data i need. column A=Filename and column B=FileContent

PROCESS:

I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.

I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.

Is there any example similiar to that so i could learn how to start on that?

Thanks

Danny

(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform

|||

The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.

What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.

|||

You can do in following way :

1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table

2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :

"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"

3. Add an Export Column transformation; in Export Column transformation editor set

Extract Column= ColumnB

File Path Column=NewFilePath

so SSIS will get the file from columnB and put in the folder using NewFilePath

Foreach Loop Issue

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

Please check the FailPackageOnFailure and FailParentOnFailure properties of ForeachLoop Container as well as Execute SQL Task Object. if any of them is defined as true then set it to false.

If this will not help you let me know.

|||

I have checked these properties and i have both of them set to 'False'... Still Fails...

Any other suggestons?

|||I suppose you could ForceExectionResult = Success|||

Hi Steve,

Here is the Solution:

1. For Foreach Loop container set "ForceExecutionResult" to "Success" so that this container never failes on execution.
2. For precedence constraint of all your tasks in Foreach loop container set the "Value" property as "Completion" so that next SQL task get executed only on COMPLETION of previous SQL task and not SUCCESS of previous one.
3. Set the "FailParentOnFailure", "FailPackageOnFailure" property to "False" for all SQL Task in container. Set "ForceExecutionResult" property to "None" for all SQL Task in container.
4. I am sure you are using "Failure" precedence constraint to send mail task from SQL Tasks.

I created a test package to try this scenario and it works :)

Thanks
Mohit

Foreach Loop Container causes package to crash

We have a problem with a SSIS package containing a Foreach Loop Container that causes the package to fail unpredictably.

We are using a Foreach Loop Container to process records in a source table one by one. We do this by executing a SQL statement on the source table, putting the resultset in a package variable and using that variable as an ADO object source variable in a Foreach Loop Container. In that container, we do four things:

1) copy the record we want to process into a temporary table,
2) run a dataflow task on that temporary table to actually process the record,
3) truncate the temporary table filled in step 1 and
4) delete the processed record from the source table.

This part of the package validates and runs fine, but every now and then the package fails somewhere in the Foreach Loop Container without any useful notification. We cannot tell where exactly the package fails: it differs. It's often in the dataflow task, but not always. If we clean up the step in which the package fails and rerun it (such that the last row in the source table is processed again), it continues without a problem. Sometimes it stops in the middle of processing a specific record. If we leave the record in the source and process that record again, it processes fine without failing on that record again. So it's not one of the source records causing the problem. One time it will take a couple of hundred iterations before the failure occurs, the next time it might take less than a hundred.

Does anybody have any clue on what might cause this problem or what we can do to further investigate this?

Thanks in advance, Hans Geurtsen

Does "without any useful notification" mean that no errors are shown or that you don't find the error(s) useful. If there are errors then can you please provide them. Without the errors all I can do is hazard a guess. Perhaps you are encountering locking issues or perhaps there are memory problems due to fragmentation.

Thanks,

Matt

Foreach loop container

Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop Container, and I don't know how to process it with Table, means for each record of this table I have to import from one folder some text files. so Idon't know how to connect my table to Foreach Loop container.

Thanks

1. Use Exec. SQL Task to query the table
2. Store the result record set in a variable
3. Use FE Loop to iterate over the record set

|||So it sounds like you want to loop over some files and then import that

file am I right?

Would this help?

Looping over files with the Foreach Loop

(http://www.sqlis.com/default.aspx?55)

Allan

"Mbd@.discussions.microsoft.com" wrote in

message

news:a05fa88a-80ad-4525-a833-370c4473b67a@.discussions.microsoft.com:

> Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop

> Container, and I don't know how to process it with Table, means for each

> record of this table I have to import from one folder some text files.

> so Idon't know how to connect my table to Foreach Loop container.

>

> Thanks|||Hi ,

thanks for your help, but is not what I need, maybe my request was not clear, so my question is : Looping over table with the Foreach Loop,

thanks in advance for your answer.

Mdb|||Hi,

Thanks for your replay, please how to do the third part ,

3.Use FE Loop to iterate over the record set

thanks|||

? how to do the third part

- In FE Loop use Foreach ADO Enumerator
- As ADO Object source variable - choose the variable used in the step (2) above
- In Variables Mapping page you can map column #1 to variable ..., column #2 to variable ..., etc

sql

Foreach loop container

Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop Container, and I don't know how to process it with Table, means for each record of this table I have to import from one folder some text files. so Idon't know how to connect my table to Foreach Loop container.

Thanks

1. Use Exec. SQL Task to query the table
2. Store the result record set in a variable
3. Use FE Loop to iterate over the record set

|||So it sounds like you want to loop over some files and then import that file am I right? Would this help? Looping over files with the Foreach Loop (http://www.sqlis.com/default.aspx?55) Allan "Mbd@.discussions.microsoft.com" wrote in message news:a05fa88a-80ad-4525-a833-370c4473b67a@.discussions.microsoft.com:
> Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop
> Container, and I don't know how to process it with Table, means for each
> record of this table I have to import from one folder some text files.
> so Idon't know how to connect my table to Foreach Loop container. >
> Thanks|||Hi ,

thanks for your help, but is not what I need, maybe my request was not clear, so my question is : Looping over table with the Foreach Loop,

thanks in advance for your answer.

Mdb|||Hi,

Thanks for your replay, please how to do the third part ,

3.Use FE Loop to iterate over the record set

thanks

|||

? how to do the third part

- In FE Loop use Foreach ADO Enumerator
- As ADO Object source variable - choose the variable used in the step (2) above
- In Variables Mapping page you can map column #1 to variable ..., column #2 to variable ..., etc

Foreach Loop and distributed files

Hi - I'm new to SSIS and am having problems figuring out how to do the following.

I need to load data from flat files into SQLserver 2005 and have created the data flows ok, but my data files are *not* located in a single directory so I cannot use the foreach file enumerator option in the foreach loop container collection. Please correct me if I'm wrong?

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

My problem comes when I edit the properties of the connection manager to try to use that variable for the connectionstring property in the expression editor. I get an error because the datatype of the variable is not supported in an expression.

Can anyone tell me how to correct this or outline another way to solve my problem?

thanks

Brian McLean wrote:

I get an error because the datatype of the variable is not supported in an expression.

Why not? You should be posting the result of the foreach loop into a string variable.|||

But you cannot return a recordset into a string! I tried and the sql execution failed with the following error...

Error: 0xC001F009 at DAOphotLoad: The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Select Catalog files from HLA DB, Execute SQL Task: Executing the query "Select DAOcat_filename from ImgFileInfo where DAOCat_status like '%Processed%'" failed with the following error: "The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||You return a recordset into an object typed variable. Then the foreach loop works on that object variable. Using the variable mappings on the foreach loop, you can store the pieces of data in the object variable in string, int, whatver, variables.|||

Brian McLean wrote:

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

You are in the right track; but you are missing one part; you need to shred the rowset into string variables:

Jamie has a sample package here; pay special attention to Collection and Variable mapping tabs inside of the forEach loop container:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

ForEach Loop AMO Enumerator

Does anyone have a need for an AMO Enumerator in the ForEach loop as well as an SMO Enumerator? I think this would be a fantastic addition to SSIS.

If you agree, vote for it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=163202 and LEAVE A COMMENT!!!!

-Jamie

[Microsoft follow-up]

Hi Jamie,

I see it is still open in our database of Katmai items, but I would not give it a great chance after reading the comments there.

There is a workaround of using the Script task mentioned in the DCR notes.

Thanks.

|||

Fair enough.

katmai + 1 then hopefully....

ForEach Loop AMO Enumerator

Does anyone have a need for an AMO Enumerator in the ForEach loop as well as an SMO Enumerator? I think this would be a fantastic addition to SSIS.

If you agree, vote for it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=163202 and LEAVE A COMMENT!!!!

-Jamie

[Microsoft follow-up]

Hi Jamie,

I see it is still open in our database of Katmai items, but I would not give it a great chance after reading the comments there.

There is a workaround of using the Script task mentioned in the DCR notes.

Thanks.

|||

Fair enough.

katmai + 1 then hopefully....

ForEach Loop - Testing for when Enumerator is Empty

I have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate.

What would be the best way to test for that condition?

Cordell,

You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the loop to decide to mail based on the count variable still being zero.

Here are some helpful links to get you started:
About variables - http://msdn2.microsoft.com/en-us/library/ms141085.aspx
Using variables in Script Tasks - http://msdn2.microsoft.com/en-us/library/ms135941.aspx
Precendence Constraint - http://msdn2.microsoft.com/en-us/library/ms141261.aspx

Cheers,
Patrik

|||

thank you Patrik for researching a solution for my need. I thought this is what I would have to end up doing, but wanted to make sure I was not missing something obvious.

It would be nice in the next major update of SSIS that this condition would be provided as an attribute/event to test for in the ForEach Loop tool.

...cordell...

p.s. Is there a place at MSDN to enter feature requests such as this?

|||

Cordell,

Feedback can be submitted to http://connect.microsoft.com.

Glad I could help,
Patrik

ForEach from query

Hi All

I'm sure this is a simple thing to do, but I'm new to SSIS and trying to catch up fast.

I want to execute a query on the database which will give me a path and a filespec, say:

c:\apps\testapp1

and

fred*.csv

No problems here.

I then want to feed them into a ForEach loop and interate through all the files matching the filespec at that location. I can't figure this out at all.

Thanks for you help in advance.

FG

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Jamie's blog has good info on how to use the ForEach loop container with the ForEachFile enumerator. You can supply a filepath and extenstion with wildcard, fetch back filenames and update a connection strimg. Good stuff.

But I think what you were asking was how to dynamically update the enumerator with different paths and filenames? First it might depend on what your really trying to do.

If you just want to stream in the files then you can also not bother with the loop and instead update the connection string of a 'multiflatfile' connection manager with a property expression of what the file info should be. The key diffference between the multiflat file is that it will accept wildcards...so it can take c:\test\*.txt and the flat file source that uses that conection manager will just load all of the files. So, its functionally different than Jamies route...both have their uses. Looping over the files will load them one at a time, starting/stoping the dataflow each time BUT you can get file specific information such as useing rowcount transform. If you used a rowcount with the wildcard approach and multifileconnection mgr then you just get 1 rowcount result which would include all rows from all files. Again, each method has its place.

Now I think what you really are asking is how to tweak on the fly the folder (directory) and files (fielspec). Well, unfortunately you cannot use property expressions on those properties. Its a current limitation. They are not really properties of the ForEach Container but of the specific enumerator (ForEachFile) which you chose. However you can do it indirectly, using Configurations and having 2 packages, one calling the other, passing in the appropriate new values.

So the parent package uses and ExecuteSQL task to fetch the inforation from a table,returning "path" and "extension" to 2 varirables, all defined in the ExecuteSQL task.

You create a 2nd package with a For Each Loop.
Child: you create 'package Configurations' of the type "parent Package Variable'. one maps to the 'filespec' property and one to the 'directory' of the For each loop

Parent: Then from the parent you add an ExecutePackage task which calls the child package.

So flow is...

Parent ExecuteSQL to populate 2 vars
Parent Executes Child Package
Child Configurations are first thing to be 'pulled' from parent as Child package starts
Child ForEach Loop excutes and the appropriate properties are already update

I suggest reading aobut parent package configurations if you have not already. I think I also have a sample I could send you.

Hope that helps

|||

Very many thanks for both replies.

Craig is correct in his understanding of what I am trying to do. ie, get a path and a filespec from the DB and use these to control the ForEach loop. I'm pleased to hear that it can't be done directly at present, I hadn't missed something too obvious!

I will try your suggestion shortly Craig.

Thanks to Jamie for his input too.

FG

ForEach file in folder - missing from dropdown list

I'm working through the SSIS tutorial, and am on lesson 2.
This says
"Double-click Foreach File in Folder to reopen the Foreach Loop Editor.

Click Collection.

On the Collection page, select Foreach File Enumerator."

The drop down only contains

foreach ado enumerator
foreach ado.net schema_rowset.enumerator
foreach from variable.enumerator
foreach nodelist.enumerator
foreach smo.enumerator

How do I get the "foreach file in folder" to appear in the drop down list?

Gary
A quick search of the forum will yield you the answer to your question.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1461435&SiteID=1|||

Mmmn,

I got our technical support team to load the sql server service pack 2 to fix another problem a couple of days ago.

This has not cured this problem (but did fix the other problem I was having).

Funny enough, both problems indicated that certain registry keys could not be read..

I'll get our technical support team to set up a restricted user and run the C# program the knowledgebase article gives.

Gary

Forcing 32 bit SSIS

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

I haven't used Itanium, but I suspect the following method will hold true-

On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTEXC should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.

x64
(http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html)

|||Thanks so much, that will get us by great until we can resolve the issue we're having with the 64 bit version.

Forcing 32 bit SSIS

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

I haven't used Itanium, but I suspect the following method will hold true-

On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTEXC should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.

x64
(http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html)

|||Thanks so much, that will get us by great until we can resolve the issue we're having with the 64 bit version.|||

Larry does it worked for you in Itanium 2,please share your experience.

Thanks

|||It worked for us in 32bit mode ok. We had lots of little things that didn't work or worked differently on the Itanium and eventually decided to move all jobs to a 32bit box. We haven't had any issues since.

For new in SSIS. To send data from operational d/base to data warehouse

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?

Thank you!!

You data-flow needs to contain more than just an OLE DB Destination. It needs an OLE DB Source as well.

-Jamie

|||

Also, consider watching the Kimball webcast on using SSIS in datawarehouses. It's on the main page of this forum and has very good information for beginners.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

|||

Thanks Jamie for your suggestion. Moreover, I will try to use SQL command in Data Access mode for OLE DB Source.

Corres

|||

Thanks Phil for your suggestion. To be honest, it is a bit complicated for me for now, but I have found a lot of useful info for me. Thanks.

Corres

|||When I started, I watched that webcast several times in order to pick up on several topics... Just give it time and play around with data and you'll get the hang of it.

For new in SSIS. To send data from operational d/base to data warehouse

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?

Thank you!!

You data-flow needs to contain more than just an OLE DB Destination. It needs an OLE DB Source as well.

-Jamie

|||

Also, consider watching the Kimball webcast on using SSIS in datawarehouses. It's on the main page of this forum and has very good information for beginners.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

|||

Thanks Jamie for your suggestion. Moreover, I will try to use SQL command in Data Access mode for OLE DB Source.

Corres

|||

Thanks Phil for your suggestion. To be honest, it is a bit complicated for me for now, but I have found a lot of useful info for me. Thanks.

Corres

|||When I started, I watched that webcast several times in order to pick up on several topics... Just give it time and play around with data and you'll get the hang of it.

For MSFT: Change of behaviour in configurations in SP1?

In SSIS SP1, if you use a .dtsconfig file that references a non-existent object in your package then you get a warning.

I don't have a pre-SP1 install to hand so I can't test it out but I seem to remember that pre-SP1 this used to cause an error.

Can anyone confirm that this behaviour has been changed in SP1?

Thanks

Jamie

Anyone?|||

Information: 0x40016041 at ConfigTest: The package is attempting to configure from the XML file "C:\Yukon\ISPlaying\ISPlaying\ConfigTest.dtsConfig".
Warning: 0x80012017 at ConfigTest: The package path referenced an object that cannot be found: "\Package.Variables[User::VariableX].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
SSIS package "ConfigTest.dtsx" starting.
SSIS package "ConfigTest.dtsx" finished: Success.

Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

Your memory is not up to it I'm afraid, a sad thing to see in a man of your age. :)

|||

I seem to remember that you thought the same as me on Friday

Oh, and I'll always be younger than you!!!

-Jamie

for loop container in SSIS

WHAT IS THE USE OF THE FOR LOOP CONTINER

PLEASE GIVE ME THE EXAMPLE

REGARDS

KOTI

If you have a variable set to an integer of 10, you might use a for loop to iterate 10 times, the contents contained therein.

A for loop will start at a given point (myVariable == 1) and increment by 1, perhaps, till it gets to the end (myEndVariable == 10).|||

hi brother i placed the controle and declare a variable i with increment that is repeating to 10 time fine

but inside the forloop of i place execute sl statment i want to insert the i valu to the sql command i was give but it

always inserting 1 only but not accordingly what we are incrementing .

so please give real time example for this usage

@. = @. + 1

|||

Configure the Exec SQL Task to use the variable. See the Parameter Mapping feature.

Configuring the container should be simple enough.

For Loop Container Samples
(http://www.sqlis.com/310.aspx)

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

For Each Loop Container in SSIS

Is anyone aware of any good links on how to use the
For Each Loop Container for ADO.NET dataset?
Thanks,
SHivayou can check out http://sqlis.com. Alan and Darren have some good info
there.
-oj
"Shiva" <arbitsquare@.hotmail.com> wrote in message
news:ef8P%23P3fGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Is anyone aware of any good links on how to use the
> For Each Loop Container for ADO.NET dataset?
> Thanks,
> SHiva
>
>