Showing posts with label loop. Show all posts
Showing posts with label loop. Show all posts

Monday, March 12, 2012

How to use Loop statement

I have a table which contains 170K of customer_numbers. How can I write a query (loop) to create 340 tables or excel files which contains 500 customer numbers each.

Thanks for your assistance in advance.

A couple of questions.

1. How do you want to split the data up into groups? Randomly?

2. Tables or excel files are two VERY different things. For excel files, I would suggest you not use SQL to do this. I would probably suggest you use a .NET language to build Excel files. If you really mean tables, then I would suggest you just use one table and assign a number to each customer_number permanently at the start of your process and then you have 340 logical files.

3. What version of SQL Server are you using? In 2005 the query to assign the numbers is pretty easy.

Something like:

SELECT customer_number, NTILE (340) Over ( order by customer_number)
into #hold
from table_which_contains_170K_customer_numbers

Or update your table:

;with tiledCustomers as
(
SELECT customer_number, NTILE (340) Over ( order by customer_number) as groupValue
FROM table_which_contains_170K_customer_numbers
)
update
set group = tiledCustomers.groupValue
from table_which_contains_170K_customer_numbers as t170
join tiledCustomers
on tiledCustomers.customer_number = t170.customer_number

|||

here's a psedou code

1. select disticnt customers

2. write a script to create an empty excel files per customer

3. create a script that will generate the script for bcp out to excel files

step 3. outputs bcp scripts so you dont need to write one for each user

4. group the scripts to 10

5. simultaneously run the 10 groups at the same time if you have a powerful box to reduce your

time to complete the task.

The idea here is to write sql server scripts that will write the scripts that you need to run.

Friday, March 9, 2012

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.

Wednesday, March 7, 2012

How to Use Data Type GUID in UserVariable or foreach loop?

Hi Folks,

I am trying to build a Package that selects a list of Uniqueidentifiers by "Execute SQL-Task" and then loops through the ResultSet of the query using ADO.Net Enumerator to do something with this GUID Value, namely deleting all entrys with this PK in a different DataBase.

The main Problem I am facing is that you can′t select the type "GUID" for a user Variable in "Execute SQL-Task". All the Datatypes are there except for GUID!

This leads to the following error:

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

Is this a bug? Or am I doing something terribly wrong?

Best Regards and thanks in advance for your help

Chucker

Hi,

What is the variable initially defined as in the variables section? I believe it has to match that of the one that is being referenced in the SQL parameter mapping editor.

Is it possible for you to perform a CAST or CONVERT in the SQL statement to return the GUID ID as a string/varchar and do a string comparison when checking the GUID against the primary key?

Its just a though but i hope it helps.

Grant|||

For the Iterator (in the ForEach Loop), the only type that works (without converting) is "object".

When using this Type in the Delete Statement, I get the error that this is the wrong type.

So this may work if I only knew how to convert that "object" to GUID in a SQL Statement

I want to write something like

delete from table where pk = @.Uservariable

in the Execute SQL-Task within the ForEach Loop

then I get the error Message that @.Uservariable is not of type GUID

as I already mentioned in the Property Page of the ForEach Loop where

the colum pk can be mapped to a uservariable @.uservariable, the data

Type GUID does not exist.

Cheers

Martin

|||Did you try string? I had a similar issue, but it involved integers. According to books online this shouldn't be an issue, but stranger things have happen.

Sunday, February 19, 2012

How to use a Variable from the For Loop Container as a input parameter to a SP

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

MA2005 wrote:

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

The ForEach loop allows you to store the currently iterated values from the collection that you are iterating over in variables. You can then use those variables in any way that you would normally use variables in a parameterised Execute SQL Task.

I haven't gone into much detail here because I don't want to talk about stuff you already understand. Tell me what isn't clear about what I have said and I'll be glad to fill you in.

-Jamie

How to use a script to break or continue a For Each loop task?

Okay, I have a For each loop container that has an enumeration of folder locations. I run a script to filter the files in those folders and prepare them to the next task to be copy. I want the script to "continue" the loop if none of the files meet the criteria. Can someone point me into the right direction? I need some way to advance through the loop when this happens.

Thanks!

Not quite understanding your requirement but I think conditional precedence constraints will help you here: http://www.sqlis.com/default.aspx?306

-Jamie