Loop Through Each Value Dynamically

May 2, 2014

I have a query that pulls many values and I want to loop through each value dynamically using a query.

SELECT COL1 FROM TABLE1

Results:
A
B
C
D

I want those results to loop and run this:

SELECT * FROM TABLE2 WHERE COL2 = A
SELECT * FROM TABLE2 WHERE COL2 = B
SELECT * FROM TABLE2 WHERE COL2 = C
SELECT * FROM TABLE2 WHERE COL2 = D

View 2 Replies


ADVERTISEMENT

Dynamically Change SSIS For Each Loop Container

Jul 10, 2006

Hello,

I would like to modify "Files" attribute of the Foreach Loop of type File
Enumerator.  This attribute is used to set the mask (for example *.txt) to
specify which files to include in the selection.  I need to be able to change
this mask dynamically depending on package global variable.  Is this possible?

Thank you!

Michael

View 3 Replies View Related

Dynamically Changing Task Name Displayed Within ForEach Loop Container

Oct 27, 2006

Does anyone know how to change the task name displayed within a ForEach Loop Container (or of the ForEach Loop Container task itself) based on a variable. I am pretty familiar with setting variable values during task execution and using expressions to alter task properties based on variables. I have tried using an expression to alter the value of the Name property of the ForEach Loop Container but the name of the ForEach Loop Container does not change during execution. Since the color of the various tasks change during execution, I would think that the task names could be changed as well.

View 7 Replies View Related

Integration Services :: Create Excel Sheet Dynamically In For Each Loop Container

Jun 4, 2014

I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.

How can i create an excel dynamically in the foreach loop container.

View 10 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

How To Put Table Name At Dynamically ???

Oct 14, 2006

Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
 

View 2 Replies View Related

How To Get A Database Name Dynamically ?

Aug 13, 2007

How to create a sql statement that dynamically matches a database name in a stored proc. Somthing like this:
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%Master'
and (DATABASENAME=????????????????)

View 6 Replies View Related

SQL Query ... Dynamically

Jun 7, 2008

 Hi....I am fresh to asp.net .... i want to get the Request.QueryString Value Dynamically in SQL Query ... I have attached the coding of two files please gothrough and give the solution..First File : -actyearwise.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actyearwise.aspx.cs" Inherits="acts_actyearwise"    MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">    <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="AccessDataSource1"        Width="80%" align="center" runat="server">        <ItemTemplate>            <tr style="font-family: Arial; font-size: small;">                <td align="left" width="7%">                    <a id="href1" href="actcheck.aspx?2007" target="_self">2007</a>                </td>                <td align="left" width="7%">                    <a id="A1" href="actcheck.aspx?2006" target="_self">2006 </a>                </td>                <td align="left" width="7%">                    <a id="A2" href="actcheck.aspx?2005" target="_self">2005 </a>                </td>                <td align="left" width="7%">                    <a id="A3" href="actcheck.aspx?2004" target="_self">2004 </a>                </td>                           </tr>        </ItemTemplate>    </asp:FormView>    <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting"        ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True"        ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex "></asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actyearwise.aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actyearwise : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {            }    protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {    }}// This page works Properly...   In the actyearwise.aspx ..just some years with link... i have entered ...the href page is actcheck.aspx(below).. In this page i want the years dynamically.. please go throughSecond file : -actcheck. aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actcheck.aspx.cs" Inherits="acts_actfiles_actcheck"    MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3"        DataKeyNames="actno" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true">        <Columns>            <asp:BoundField HeaderText="actno" Visible="False"/>            <asp:TemplateField SortExpression="actno">                <ItemTemplate>                    <table width="80%" align="center">                        <tr>                            <td align="left" width="3%" colspan="3">                                <%# Eval("actno") %>                            </td>                            <td align="left" width="75%">                                <a id="href1" href="actshorttitle.aspx?<%# Eval("actid") %>" target="_self">                                    <%# Eval("actname") %>                                </a>                            </td>                        </tr>                    </table>                </ItemTemplate>            </asp:TemplateField>        </Columns>    </asp:GridView>    <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting"        ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True"        ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex where actyear = 'check' ">    </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actcheck. aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actfiles_actcheck : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {        String check = Request.QueryString.ToString();    }    protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {    }}  The text i had bold and italics in the actcheck.aspx... is the problem..... From the actyearwise.aspx .. i was passing the actyear value ..... In actcheck,aspx .. i want to get it dynamically in SQL Query...... Please give the solution .. I am using the SQL  database.Table name actindexfields nameactnoactnameactidactyear In the actyear field i have different years .... Thanks in advance      

View 5 Replies View Related

Dynamically Specifying TOP In Query

Apr 23, 2002

I'm trying to dynamically set the TOP value in a query, but it doesn't work. Any other way it can be done?

Doesn't work:
declare @sometopvalue int
set @sometopvalue = 100
select top @sometopvalue
field1,
field2,
field3
from table1 ORDER BY BRDCST_DT_TM field1 desc

View 1 Replies View Related

How To Pass Top A Value Dynamically

May 19, 2004

Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work

SELECT TOP
(SELECT COUNT(DISTINCT RegionID) AS COUNT
FROM Sales)
*
FROM Sales

but when I hard code the value it works


SELECT TOP
2
*
FROM Sales

any ideas...Thanks

View 6 Replies View Related

Create SP Dynamically

Sep 2, 2005

Hello,

I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:

CREATE PROCEDURE .....

to

Alter PROCEDURE ....

Then I am obvoiusly getting the error msg:

Invalid object name.

How can I get rid of that?

View 14 Replies View Related

How To Set Column Name Dynamically?

Oct 18, 2006

HI chaps

my scenario is that, i have a table in which i have column such as col01,col02,col03....col31

i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col??') and i have to do integer increment in last two character (???01...???31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?

waiting for your reply

regards

View 7 Replies View Related

Get Filename Dynamically

Jul 26, 2007



Hi,
Using a ssis package I have several tasks.
A variable is also created called FileName.
There is a flat file connection called InputFileName.
initially when I designed the package, I hardcoded the pass of one of the .csv files into the path of the flatfile connection.

There is a dataflow which has flatfile source and oledb destination. These two basically pickup the file and import into the database using mapping.

So therefore the package works and data gets imported into the database.

Then I used the variable to make the filename dynamic. So I removed the hardcoded connectionstring in the InputFileName.

Is this the right thing to do to make the package dynamic? I would like to pass in any .csv filename and so the package runs.
My question is now that the connectionstring is deleted but instead placed an expression for the filename, then how does the system know which file to use?

Thanks

View 1 Replies View Related

Import *.dbf Dynamically

Mar 5, 2007

Hi, there;

I am doing a SSIS project, my target is:

I have a source folder which have some *.dbf files (the number of
files will change as time goes on) exported from another application. I want my SSIS package to load data from these dbf
files in the source folder to my SQL2005 database no mather how many
dbf files will be.If the destination table already exists in the database,
the logic logic will do update (delete and import again), if not, create a new table on th fly and then
pump the data.



What I did here is that I have a foreach loop container which contains Script
task, Execute SQL task and a data flow. The Foreach container now can
connect to the *.dbf files successfully, the Script task can read
schema information from each dbf file and create script "Create table ...."
successfully and Execute SQL task can create table in my new empty
database successfully! But I have problem in the data flow task: my OLEDB Source points to the dbf file folder and set Data Access mode to "Table name or view name variable",but when I set Variable Name to a package level variable ("tableName"), I get error: Error at Data Flow Task_Pumping data from Admar [OLE DB Source [2737]]: A destination table name has not been provided.
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

Does anybody know what to do to create table and pumping data on the fly? (Using variable as table name)

Big Thanks here.

View 2 Replies View Related

How To Get The Database Name Dynamically?

Aug 13, 2007

I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt



Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%'

and (DATABASENAME=????????????????)


Can someone help me out?

Thanks.

View 4 Replies View Related

How To Dynamically Set The ServerPassword

Aug 18, 2006

I have an FTP Task where I have to set the server password at run-time. I have the ftp connection manager set up. What I normally do is configure the ftp server connection manager using a dtsConfig file but I can't do that in this case. I won't know the serverpassword until I get a parameter from the user.

View 3 Replies View Related

File Name Dynamically

Apr 11, 2008



I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv

trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv so it will vary as per user selection . i need to run the same SSIS package to execute the package whichever the file name.

Please let me know the solution for that how to pass the file name dynamically to SSIS package.

View 3 Replies View Related

Dynamically Name Folders

Feb 12, 2007

Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.

I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.

Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.

Thanks for the help,

Chris

View 3 Replies View Related

Passing Value To SqlDataSource Dynamically

Jul 10, 2006

Hello
Following is my code which is not working.
<asp:SqlDataSource ID=events runat=serverSelectCommand="SELECT * FROM TBL_EVENT WHERE SECTION_ID=<%= Session("ID")%>"ConnectionString = "<%$ appSettings:SQLConnectionString %>"</asp:SqlDataSource>
I want to pass the value of Session("ID") into that query. How can I do that?

View 4 Replies View Related

Dynamically Choosing Connection --please Help

Jul 26, 2007

 i have to store some data on a remote sever(MS SQL SERVER2000). The scenario is like 1. The web application runs on a local machine. User (who inputs) uses through LAN.2. The Input should  be stored in the remote server. if the remote connection is ok. otherwise it should be saved in local server's database(MS SQL 2000).3.  In the application's web.config  there is a connection string pointing to the remote server and another one (alternating one) points the local server's database. in scenario like this i first  to tested the remote connection. if it is not ok  then i initialize the local server's connection like thisprivate MyConnection()    {                        try            {                connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForRemote"].ToString());                connectionSql.Open();            }            catch (Exception ex)            {                connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForLocal"].ToString());            }            finally            {                connectionSql.Close();            }        connectionSql2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Temp"].ToString());                   }My problem is when the remote connection is lost it takes almost 1 minute to store in local database. how can i make it more time efficient. Thanks....

View 5 Replies View Related

Dynamically Generate SqlDataSource

Jul 31, 2007

Hi, how I do create SqlDataSource in the code-behind,setting Select SQL along with its parameters and, finally automically generating Del, Update, Insert SQL.
Is there a way to achieve this goal as it would in VS designer?
Thanks,
Ricky.

View 2 Replies View Related

Create Tables Dynamically

Sep 12, 2007

Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
 
Thanks & Regrads
Jai Shankar
 
 
 

View 1 Replies View Related

Dynamically Change FilterExpression

Oct 1, 2007

I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.
Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx
 Here is some of my code..Private Sub ApplyFilter()
Dim _filterExpression As String = ""
    If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then
        _filterExpression = "CategoryName = '{0}'"
    End If    Me.SqlDataSource1.FilterExpression = _filterExpression
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    ApplyFilter()
End Sub
But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.

View 5 Replies View Related

Configuring Dynamically An SqlDataSource

Jan 4, 2008

I have several Sql Tables, each containing more than 100 fields, on which I need to perform display operation through GridView, Insert and Update through an array of dynamically created and databound TextBoxes.
I would like to use a single page to manage all Sql Tables on the basis of the name of the specific one invoked by the user.
In order to achieve that, I need to  dynamically configure the SqlDataSource Web Control present on the page for each Sql Table. In particular I need to generate at runtime the InsertCommand and the UpdateCommand.
On how to do it I have no idea.
Federico

View 2 Replies View Related

Choose A Column Dynamically

Mar 3, 2008

I want to choose a column dynamically depending on a session variable.
In other words, each customer has a different price level. so I need to choose the column that corresponds to thier price level in thier customer record
Any help is appreciated.
below is my futile attempt
 
SelectCommand="SELECT cove.CATALOG.ITEM_NO,cove.catalog."& <%#= session("pl") %> & ", cove.CATALOG.DESCRIPTION, cove.CATALOG.DESCRIPTION2, cove.CATALOG.DESCRIPTION3, cove.PRODLINE.DESCRIPTION AS Expr1, cove.CATALOG.GRP, cove.COMPANY.PRL, cove.COMPANY.COMPANY FROM cove.CATALOG INNER JOIN cove.COMPANY ON cove.CATALOG.WEB_STATUS <>'[I]' cove.COMPANY.I LEFT OUTER JOIN cove.PRODLINE ON cove.CATALOG.PRODLINE = cove.PRODLINE.CODE WHERE (cove.COMPANY.COMPANY = @company) AND (cove.CATALOG.GRP <> '[ACC]')">
<SelectParameters>

View 3 Replies View Related

Dynamically Creating A SQL Connection

Mar 6, 2008

 Hi - hope someone can help. We're creating a ASP.NET C# site that allows a user to connect to any of our 100+ SQL Servers and query a database that sits on every server. I'm trying to work out (and failing) how we can set the 'Data Source' in a connection string dynamically using the server name selected by the user in a drop down box. Any help appreciated, thanks in advance, 

View 2 Replies View Related

Dynamically Choose Database

Jun 21, 2004

I have to write a large data migration script to move data from one SQL Server database to another. Is there any way to dynamically specify the server and database name? I would like to do something like the following, (but this does not seem to work):

Delete From [@ServerName].[@ImportToDatabase].[MyTable]

Any help appreciated

View 2 Replies View Related

Dynamically Select Column

Sep 29, 2004

Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:


CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS

DECLARE @SQLStatement varchar(255)
SET @SQLStatement = 'UPDATE myTable SET '+ @BootNumber+'='+ @VoteID + ' WHERE (PlayerID = '+ @PlayerID +')'

EXEC(@SQLStatement)

GO


I get the following error:

Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int


The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.

CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS

UPDATE
myTable
SET
Boot3 = @VoteID
WHERE
PlayerID = @PlayerID
GO


Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!

View 2 Replies View Related

Evaluating A Parameter's Value Dynamically

Jan 25, 2006

Hi, I'm creating a dynamic SQL statement in MS SQL Server that is similiar to this:
EXEC('IF @' + @current_column + ' (SELECT ' + + @current_column etc...
I'm basically looping through a large list of parameters that correspond to column names.  However, since SQL Server treats EXEC() as its own scope when it gets to what "@' + @current_column" evaluates to it says the parameter must be declared.
Is there a way to convert "@' + @current_column " into the actual value of the parameter?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved