hi
i have a for-loop-conatiner with two sql-tasks inside. i'm unable to add a precendence between this two tasks inside the container. what's wrong? i want first execute one task in the container, then the other. so, i guess, i have to link the two tasks. but it's not possible.
thanks for your support.
Suppose if I have a “Foreach Loop Container” that iterates over a list. Is it possible to execute different data flow tasks based on the input?
Example : List contains elements L1, L2 & L3.
ForEach Loop Container checks the input. If its L1 then it should execute DF Task1, If L2 then execute
DF Task2 and similarly for L3.
Is it possible to achieve this?
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated.
Yours
Jayne
Hi,
I am trying to programmatically execute a package that contains an Execute SQL Task component bound to a variable for its "SqlStatementSource" property (via an expression). The variable is of type String and contains a simple value of "SELECT 1". The Execute SQL Task contains an expression that sets the SqlStatementSource property to the value of this variable.
The package runs fine when I execute it via dtexec or BIDS, but when I attempt to run it via the object model, I receive the following error message:
The result of the expression ""@[User::Sql]"" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I did a search on this forum and noticed quite a few threads about this same issue, but no explanation/solution. We have quite a few packages that have dynamically constructed SQL statements for Execute SQL Tasks, and they are all failing to run via the object model. Is there something that I am missing?
Thanks,
Vitaly
Hello,
I've been googling for quite a while trying to find information on this but i'm having no luck.
What I'm trying to find out is when joining 3+ tables together how can you explicitly specify the order in which the joins are applied? I understand that the basic order is "FROM" then "WHERE" then "HAVING". I've also read that the query optimizer does a bit of work. but if I have a more complex join e.g.:
table1 inner join
table2 on t1.a = t2.a inner join
table3 on t2.b = t3.b inner join
table4 on t4.a = t1.a
(so here I'm joining table1 to table2, then table2 to table3, and then table4 to table1.)
what exactly specifys the order in which these tables are joined? Does it just join the tables in order I've referenced the table? Or perhaps the order of the "ON" clauses? What about if I did this:
table1 inner join
(table2 inner join table3 on t2.b = t3.b) on t1.a = t2.a
inner join table4 on t4.a = t1.a
would the parenthes make any difference whatsoever?
If someone could explain or point me in the direction of some documentation on this then I'd be very grateful.
Cheers,
Olly
Hi,
Any insights to this issue would be great.
Following is my problem statement. I have write around 20Batch programs and in each batch i have atleast 7-8 data validations. If any of the data validation fails then i have to perform a log operation and exit.
Now I have each of my data validation as a Script Task (Control Flow) which inturn would call my SP and set the "Status" variable accordingly
I have written a "OnVariableValueChanged" Event with Raise Change Event for "Status" variable set to "True" Now in this event i check if Status = False, if it is false then i perform the log operation and throw a new DTSException to abort the control flow execution. The event gets fired but it continues to process the next control step(but i wanted it stop there). I could have acheived this by setting a precendence constraint (Status = True) for all of my control flow task but i feel the other approach to be very elegant.
Any suggestions to make this work??
Thanks & Regards
Arvind T N
Has anyone ever seen this issue?
If i place a loop container inside a sequence container the connectors within the loop container disappear.... is this a bug - will the loop tasks still run in the correct order?? or will they fire off willy nilly??
I have an execute sql task that uses the following query: (modified for this thread but normally its going to return all records in the table)
Code Snippet
SELECT
'PrimaryID' AS 'Parameter/name',
PrimaryID AS 'Parameter/value',
'LastName' AS 'Parameter/name',
LastName AS 'Parameter/value'
FROM ParkingUpload
WHERE primaryid = '1234' or primaryid = '4321'
FOR XML PATH('T2Method')
The output of this query looks like this: (I know normally this would be a bad xml file, but its needed for my purpose)
Code Snippet
<T2Method>
<Parameter>
<name>PrimaryID</name>
<value>1234</value>
<name>LastName</name>
<value>Abiola</value>
</Parameter>
</T2Method>
<T2Method>
<Parameter>
<name>PrimaryID</name>
<value>4321</value>
<name>LastName</name>
<value>Aboud</value>
</Parameter>
</T2Method>
That xml is stored in a user variable in the Execute SQL Task. I need to take that user variable and send out that xml to a webservice. The web service is expecting one record at a time(a new record is defined by the <T2Method> node.
So question is, does my approach seem fine so far? I was wondering if my next step should be to put this variable in a For Each Container and send out one record at a time to the webservice. If so, how would I go about doing that?
If anyone else has a different suggestion as how to approach this, or be able to help with the For Each portion (I am brand new to SSIS), I would really appreciate it.
i am trying to insert data into a table and roll back if this fails. What i have done is created an Execute SQL task with a SQL statement of BEGIN TRANS .. if the constraint = success then it goes to a Sequence Conatiner that amoungst other thing includes an Execute SQl task that does INSERT statement . outsied of the container i have 2 execute sql tasks that to a COMMIT TRANS ond a ROLLBACK Trans . when executing it i get an error the Rollback Transaction request has no corrasponding Begin tranaction. I have seen this in a SSIS created by one of the Wizards and it works fine, im oviously missing something fundemental .. any pointers ?
View 1 Replies View RelatedHello Experts,
Sorry that I open a new thread again but I didnt found any good tutorials about foreach loop container. I would like to read every row from one table and do something.
I red something about sql task Can someone give a little instruction, please?
Kind regards,
Alex
Hi,
I'm using data flow as flat file source -> derived column ->Data Conversion-> oledb destination
I have a fifty thousand record in a delimited file. while processing i got error in 45000th row. In database only 40000 records are there(why is there no all 45000 records). Is there any way to roll back all the 40000 records.
Hi
I using data flow task to import from flat file to database but i need to use Foreach Loop Container to import multiple files in specific folder and all will be insert in the same table
tahnks & Regards
hi people, i have crashed on a this problem. I have a sequence container and on this container I have set "FailPackageOnFailure=true". Now in this container there are 2 tasks. The first one is preceeding the second one. Now both this task have set "FailParentOnFailure=true". Both task are the same and their purpose is to drop table A.
1) I run the package and it fails, because there is no table to drop.
2) I create the table manualy and run package again.
3) I see, that the first task is beeing just SIMPLE OMMITED and the second task runs
In general, everytime any task in a sequence container invokes failure, next time is beeing ommited regardelss of its status. How can this be fixed ? Thanks
What I'm trying to do is take multiple "snapshots" of a membership for many months. I was trying to do this in a For Loop Container in which I added one to the month variable and subtracted one from an iteration variable each loop, unless the month variable = 13...then I would add one to the year variable, set the month variable to 1 and subtract one from the iteration variable. This, I was hoping, would allow me to initialize 24 to the iteration variable, 1 to the month variable and 2004 to the year variable, kick off the package, and get 2 years worth of "snapshots" in seperate files (one for each month). Here is the AssignExpression I thought would work:
@Member_Month == 13 ? @Member_Month = 1 && @Member_Year= @Member_Year + 1 && @Member_Iteration = @Member_Iteration - 1 : @Member_Month = @Member_Month + 1 && @Member_Iteration = @Member_Iteration - 1
The package states that the single "=" should be double, but I don't think that that is what I want to do (I tried doing this just to check, and it didn't like this either). I think that it is having problems with doing multiple things on both sides of the ":". What am I doing wrong?
Thanks,
Brian Layden
Afternoon Folk ...
Here is the question.
I have a RAID 10 container consisting of 4 146GB, SAS, 3.5-inch 15K RPM Hard Drives.
We have the OS on its own RAID so we are strictly dealing with setting up SQL Server 2005 on the RAID 10.
SO ... is it better to create seperate volumes for the Data, TLog and TempDB file placements (i,e, C:, F: and G or is it alright to create just plain-jane folders for the file placements?
Does it make a difference?
Is there a better way to carve this RAID 10 up?
Thanks ALL!
Has anyone investigated extending any of the SSIS Container classes?
I have been looking into it because we'd like to add a set of standard logging calls on events, standard startup procedures, etc. on any package that we execute.
I've been looking into the Sequence Container, For-Each, etc. They are all sealed classes. I'm not sure why MS has sealed them.
We're currently thinking of implementing our own version of the Sequence Container -- we'd really like to be able to extend the functionality of a standard container class, but we don't want to have to implement the actual container class itself.
Any insight appreciated.
Is there a way to set a variable as a substring of the enumerator in a Foreach File Enumerator type? The Variable Mappings tab in the Foreach Loop Editor does not allow you to enter an expression.
Fred
hi guys,
I was wondering about a silly thing but, anyway, which is the maximum number of grouping for a container? I mean, when you group from, i.e, a sql task and then over that group do again another one and so on...
Thanks in advance for your time,
We have a config table which has columns like pollingfolder, cop to folder etc.
Data looks like this
Config id polling folder
1 d:dataloadacn
2 d:dataloadship
under ACN and Ship folders we can have more than 1 source files which had to be loaded into target
the load process has to be repeated for each file in all the folders like ACN and ship
I have used for each loop container to poll the folder.
Can we set the directory to be polled at runtime taking from config table.
For doing this I have used the property Directory in expression editor of for each loop. But it is not working.
If I hard code the variable value in variables section , then its working.
How to change the directory to be polled value at runtime?
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!
Hi,
I am using For each loop container in my SSIS package which will loop through more than one input file in the input folder.
All the input files in the input folder are picked by one by one. All the input files in the input folder are executed with the same ExecutionInstanceGUID.
Now, my requirement is to get a new ExecutionInstanceGUID during the execution of each input file in the For each loop container. My package only contains the For each loop container with some logic inside that.
Please let me know if any one can help me for the same.
Regards,
Sriram.
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,
A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?
Your help is much welcome. Thanks in Advance.
João Cruz
Hi
Can anyone point me to an example of using a ForEach Loop Container to step through the rows in a recordset. I cannot seem to see how this is done from the help files.
Thanks
Hello all,
I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.
To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".
The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.
Any idea?
Thanks,
Liran
WHAT IS THE USE OF THE FOR LOOP CONTINER
PLEASE GIVE ME THE EXAMPLE
REGARDS
KOTI
Hi,
I am having problem when setting for-each container's EvalExpression as a variable comparison.
I have set this in expression editor
@[User::Syn] == 1
Editor says that it couldnt convert from System.Bool to System.String.
If I put it in quote, then for-each container says that it needs Bool, not String.
Please help me with this,
Thanks
I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?
Here's the scenario:
I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints.
The package is setup to use checkpoints so that if a task fails the package will restart from that failed task
TaskA is insignificant here.
TaskB fetches some data and puts it in a raw file
TaskC inserts that raw file data into a table.
Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.
So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.
How do I configure the package in order to execute TaskB again? Is it even possible?
Regards
-Jamie
How can I force a Next Iteration in a ForEach Loop container?
I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).
If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.
Is it possible to force "Next Interation' on the parent container?
Thanks - Covi
Hi
Currently the ForEach Loop container is set to loop over the entire collection of records in my table, but I only need to loop thru the records where the status = x. How is that possible? Can I use expressions to set the enumerator to do something like this. Basiclly the collection values are used for further processing, and I would like to avoid looping thru records which have been processed already, which means they already have a status set to y. Please let me know. Thanks.
MA
I am trying to move files from one directory to anonther using the For Each Loop Container and a File System Task. However, on the FIRST iteration of the ForEach Loop the variable that I am setting to the 0 index of the For Each Loop is returning me a valid directory with no file ( path only ) from who knows where ????
On the second iteration of the For Each Loop everything works as expected and I get the full path and file name and it iterates through all the files in the directory o.k.
So I have had to put a Hack to skip the fist iteration of the loop and then execute the File Task on the second iteration but I dont see anyone else doing this.
Where in the heck is it getting this odd directory from on the fist iteration ( its not my User varible because I am initializing it to \nowhereofile ). What am I doing wrong?
hi everyone,
I've created a Send Mail task which works properly and now I'd like to send that email n times.
How do I such thing. I moved that task inside Foreach loop container and then I choosen "Foreach From Variable Enumerator" and then I've created a Int32 variable.
And now, I don't know how to tell that increments that variable
TIA,
Hi. I am running a package using 'execute package' inside a for each loop file iteration. I have to iterate through some 10,000s of files to ETL source data, a resonably complex package. I want to set going and get some sleep but every 200 files or so I get a memory exhausted error and the task fails. Is there a short easy fix to refresh memory each iteration. If so please let me know. Just ignoring the failure at task level won't refresh the memory, will it?
Thanks for the help.