MS SQL Server :: SP Not Firing In Foreach Loop

ForEach Loop With Excel

Hi,

I'm attempting to use the Foreach loop container to loop through the excel files located on a shared network folder. I've set up the Excel file connection manager to include the user variable generated from the container, but I get the below error messages when applying that variable to the connection string in the data flow. I've tried everything but I can't seem to get SSIS to recognize the path of the Excel files. I've tried copying the files to my PC, I tried running the package on the server, etc. The connection works fine if I set it up to point to any of the excel files in the network directory, but not with the Foreach loop connection name.

Any help or suggestions would be greatly appreciated! I've looked everywhere and tried everything but to no avail...

Thanks,

Kevin

TITLE: Microsoft Visual Studio
------------------------------

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------

View Replies !

Using A ForEach Loop Container
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

View Replies !

Foreach Endless Loop
I'm having a problem where I'm using a Execute SQL Task to retrieve a dataset and storing that in an object variable. Then on success of that execute sql task I use a foreach loop task to go through the dataset and do 2 tasks inside the foreach loop. When I execute this package I have ~12 records in the dataset however when I get to the foreach loop in the 2nd iteration it keeps repeating it. It acts like it is stuck on the second record (tuple) and never goes on. I'm using an ForEach ADO Enumerator in the foreach. I've even set a breakpoint on each iteration and no task fails in side the foreach loop. I'm completely perplexed why it will iterate to the 2nd record but get stuck there in an endless loop. I've tried this on 2 different computers (with different data values) and the same thing happens. Does anyone have any suggestions?

View Replies !

Using Foreach Loop Container
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

View Replies !

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....

View Replies !

Issues About Foreach Loop
I defined Foreach File Enumerator,there are more than 2 files needed to deal with,it donesn't work if i don't specify filename in flat file connection manager editor,however if i specify filename then doesn't foreach files

what should i do? thanks

View Replies !

ForEach Loop Editor
Help,

Trying to through a process together and using the "ForEach Loop" task. When configuring the task and using the ForEach Loop Editor I do not have the "ForEach File Enumerator" in the drop down. Anyone know whats going on or what I need to do to make this appear. I only see 5 selections: ADO, ADO.Net Schema Rowset, Variable, NodeList, and SMO Enumerators. I am also using the SQL 2005 eval with SP1.

Thanks,

John

View Replies !

Help -- How Do I Exit A Foreach Loop
I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

View Replies !

Foreach Loop Is Killing Me!!!!!!!!!!!!!!!!!!!!!!
I would like your help with the Foreach loop container. Boy, am I having issues with using it for looping through an ADO.NET dataset or what!?!? My control flow has a data flow task that is executing a Data Reader task (creating a .NET dataset for me). Now I go back to the control flow and add a Foreach loop container to loop through each record in my dataset. But which type of the Foreach loop container should I be using? I see an option to use Foreach ADO.NET schema enumerator but not sure how to configure it. Also I used the Foreach ADO option, but setting up variables to go through every column is an absolute pain in the you know what!!?!! I have about 200 columns and I want an easier way to refer to those columns in my transformation phase.

Hope you are able to help me out here.

View Replies !

Configure FTP In Foreach Loop
I need to put a FTP task inside a Foreach Loop Container to upload data files to many different FTP servers. The container holds a variable with object data type that includes necessory FTP info (FTP server address, login, password and remote path). How do I configure FTP task so that I can pass the variables to it?

Thanks,

Jia

View Replies !

Foreach Loop Problem (Bug?)
We discovered that the Foreach loop isn't recalculating the number of files found upon each new loop iteration, so if any new files are placed into the receive directory during the looping, they are not detected/processed. is this a bug or product intention?

View Replies !

Queation About Foreach Loop
All,

I€™m new to SSIS and have a question about foreach loop container.
Basically, I have 4 data flows and each of them has a variable that need be pass on to the next step, each of the variables will call a same stored procedure.
What should I use here? I guess I can use foreach loop container with the stored procedure inside of it, is that right? I tried all day yesterday and cannot make it work.
The foreach loop container did not call the procedure at all. Could someone kindly tell me how to do it?

View Replies !

Doing An Insert In A ForEach Loop

Help with Looping in a SSIS Package
Scenario:
We have a web app that lets our existing clients insert new locations into a table (Clients) in a SQL Server DB. This table has an Identity column as the PK. This table also has another ID field HBID that is the PK for another table (HLocation) in another Database system (Sybase). The HBID field is given a Default value of €˜1€™ during an insert operation in our (SQL Server) table, Clients. The Sybase database uses a Sequence table for inserting a PK into the table. Whenever our clients insert a new record in Clients (SQL Server), I need to generate a new HBID from the Sequence table in (Sybase), update the HBID in the Clients table (SQL Server) and then finally insert the record with the new HBID into table HLocation (Sybase).
I have devised a SSIS package for this as following: Note all variables are scoped at the Package Level.
Execute SQL Task #1 that drives a For Each Loop with the following properties:
General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
SELECT     COID, HBID, Name, DateCreated
FROM         Client
WHERE   HBID = '1'

ResultSet: = Full Resultset

Result Set:
Result Name = 0
VariableName = USER::rsClient Variable Type = Object

ForEachLoop with the follwing properties:

Collection:
Foreach ADO Enumerator
ADO Object Source Variable = USER::rsClient
Enumeration Mode = Rows in first table
Variable Mappings:
Variable:               Index:
COID                    0
HBID                    1
Name                    2
DateCreated             3

Inside of the ForEachLoop I have another Execute SQL Task to generate a new HBID from Sybase set up as following.

Execute SQL Task #2

Connection Type: OLE DB
Connection: Sybase
SQL SourceType: Direct Input
SQLStatement:
UPDATE Autoinc SET INC_LAST = (INC_LAST+1) WHERE INC_KEY ='HBID';
SELECT INC_LAST AS NewHBID FROM Autoinc WHERE INC_KEY ='HBID'

ResultSet = SingleRow

Result Set:
Result Name = NewHBID
VariableName = USER::NewHBID, VariableType = Int32

Also Inside the ForEachLoop is a Script Task that has all of my variables as ReadWrite = COID, HBID, Name, DateCreated and NewHBID. I concatenate the values in a string a pass the string into a Message Box to make sure they are looping correctly and they are.For example the results might look like the following:

12698, 1, John Doe Trucking, 10/1/2007, 14550
13974, 1, Joe Smith Trucking, 10/1/2007, 14551
10542, 1, Dave Jones Trucking, 10/1/2007, 14552
Etc.

The values 14550 -14552 are the new HBID being generated in the loop.

The problem is that when I try to Update the HBID in the Client table (SQL Server) with another Execute SQL Task I keep getting the same NewHBID number.
In this case 14550 would be updated for every record instead of the next number in the loop.

I have set up Execute SQL Task #3 as follows:

General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
UPDATE Client
SET HBID = ?
WHERE HBID = '1'(SELECT COID, HBID, Name, DateCreated FROM Client)

ResultSet: = Full ResultSet

Result Set:
Result Name = 0
VariableName = USER::rsNewClient, VariableType = Object

Parameter Mapping:
VariableName USER::NewHBID, Direction = INPUT, DataType = Long
ParameterName = 0

I€™ve tried putting Execute SQL Task #3 inside of the ForEachLoop, connecting it to the output of the ForEachLoop. I€™ve tried setting up a dataflow with a Derived Column using the USER::NewHBID as the Expression.

I still get the same results, 14550 added to every row.

Can any one help or shed some light?

Any and all suggestion will be deeply appreciated!

Thanks !!!

View Replies !

How Do I Fail A ForEach Loop...

if there are no files in the directory i'm searching? Or if not fail, give me something so that I know & can raise a flag as needed?

thanks

View Replies !

Foreach Loop Container
Hello Experts,

Sorry that I open a new thread again but I didn€™t 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

View Replies !

Foreach Loop - Value Back To 0
Hi,

Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data.

Somewhere inside the Foreach Loop, you set an int variable MyInt to a value.

My question:

Is it possable when each iteration begins to set MyInt to 0?

Thanks in advance.

View Replies !

ForEach Loop Container Question
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

View Replies !

Foreach Loop Runs Out Of Memory
Hi

I have a for each loop which steps through an ado recordset (approx. 5,000 rows), this passes two variables to an SQL statement which populates a second recordset (normally 8 to 10 rows). I use the second recordset in a dataflow task which was a simple Script which returns approximately 30 rows for inclusion in my destination table. The package runs for a while OK, although the loop appears to execute slowly, then I get the below message constantly repeated in the debug window.

[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 174 buffers were considered and 174 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
I have 2gb of virtual memory on my machine, and the recordsets are relatively small. Have I missed a seting some where?

View Replies !

ForEach Loop Sequence Question
Hi,

This is related to an earlier post.

I have a ForEach loop that contains 3 script tasks in it.

The script tasks are connected by precedence constraints, as in:

script1 --> script2 --> script3

So they should execute in order.

When I run the debugger however, I see that script3 turns green before script2. It is a little disconcerting, however it seems to working correctly. Script3 can't even do what it's supposed to do until script1 and script2 are finished.

But as I've said, it's working. So why does script3 appear to finish before script2 is done?

Thanks

View Replies !

ForEach Loop Container Issue!!!
Hi everyone,

I am having hard time with foreach loop container. The for each loop container in my package goes over all the rows in a ADO enumerator recordset variable and shows row values one by one in message box. The problem is that it just keeps printing the first row infinitely. Could anyone tell me what could be wrong?

Thanks in Advance,

View Replies !

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

View Replies !

Enumerating Foreach Loop From Rowset
Please let me know if I am on the right track here.

I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.

I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.

Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection > Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).

Thanks - Dana Reed

View Replies !

Combining Records/Foreach Loop
I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

View Replies !

Multiple Wildcards In Foreach Loop
Hi, I am using Foreach Loop to loop through files in a directory...
I would like to use more than one wildcards (e.g. *.txt *.log ).. but the container does not seem to work that way. It only takes one wildcard...

Is there anyway i can pass in multiple file extensions ?
thanks

View Replies !

ForEach Loop - Getting MD5 Hash Of File
Hi Guys,

I'm importing logs from multiple servers from CSV format into a table. Inside the loop the 1st thing I do is insert the FileName into a table to ensure that I never process the same file twice.

The problem is that multiple servers produce the same filename, so if server A produces Log20061110.csv - when I import the log from server B ( also called Log20061110.csv ) into the table, it of course runs into a problem.

If there was some way for me to get an MD5 Hash of the file as a variable that would be solve my problem.

Does anyone know how I could accomplish that?

Thanks
Warrick

View Replies !

ForEach Loop Construct: ADO.NET And SMO Usuage
I am wanting to build a SSIS job that compares the table and column names in a destination database againse a source database. It would be nice for our developers to be able to update their local copy of various development databases against the QA version.

My thinking was to use a ForEach Loop construct to loop through each table name of the source system and compare against the target system. If the table name existed in both databases then perform another compare looping through the column names. If there was a column missing in the target system...I would like to flag it for inclusion in an email to the developer.

Since we use SQL Server here for all of our databases...I thought SMO would be more effecient than ADO.NET.

What are the strengths of each type of access.

Thank you for your thoughts.

...cordell...

View Replies !

ForEach Loop Container And First Task
Hi,

My Foreach Loop container has 10 different task inside. I want to execute the first task only one time. I have a variable with increases for each repition. How can I put precedence contstraint on the first task so that it should execute only first time and other task has to execute all the time.

Thanks

View Replies !

Foreach Loop With XML Source Failure
I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
          <xs:complexType>
            <xs:attribute name="GUID" type="xs:string" use="optional" />
            <xs:attribute name="WSUSServer" type="xs:string" use="optional" />
            <xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
            <xs:attribute name="computerName" type="xs:string" use="optional" />
            <xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
            <xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
            <xs:attribute name="Model" type="xs:string" use="optional" />
            <xs:attribute name="Make" type="xs:string" use="optional" />
            <xs:attribute name="BIOS" type="xs:string" use="optional" />
            <xs:attribute name="Processor" type="xs:string" use="optional" />
            <xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
            <xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
    <computerCount QTY="1" />
    <computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

View Replies !

ForEach Loop And Data Flow
In my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.

Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.

This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?

Thanks,
Gary

View Replies !

FOREACH Loop Container: Continue ?
With a ForEach container, configured to loop through files in a
directory, if I have a problem with a file.. can I direct the loop to
skip on to the next file?

I'm processing structured files, first record of each is some
header info, body records are in the middle, and then the last record
is a trailer containg a checksum

So, for each file in the directory, I split the records into three raw
files, one for header rec(s) , one for body recs and one for trailer
recs. (based on line numbers and using a conditional split to direct
the records)

Then I start by processing the header recs in a dataflow.. if all goes
well there I move on to the next dataflow to process the body recs from
the DataRecs raw file.. etc...

I would like to do some validation at each processing step.. if a
header rec fails validation say... then I'd like to just stop
processing that file and move onto the next file...

Now, I don't see my validation throwing an exception... so its more
that I'd decide (maybe using an Audit ) that the header doesn't pass
validation.. then I'd like to put a record in an error table (with info
about filename, source etc, not just content of the current data row)

But not sure what approach to take on this...

If there is an appropriate section in BOL please point me at it...

Thanks

PJ

View Replies !

Please Help! Dynamic DB Connection In Foreach Loop
Hello-

I'm trying to read a table that has database connection information for other DBs and use this within an "Execute SL Task" task. I have seen a number of posts that talk about this possibility, but I have not been able to get it to work yet.

When I've tried to set the connection to a variable (@[User::DB_ConnectionStr]) in the Expressions area of the SQL Task, the Connection type defaults to OLE DB and I can't seem to force it back to ADO or ADO.net.

I've tried doing this with the variable being set to both a connection object and a String with the connection string, but neither seems to work.

Any suggestions? Should this be a string value of the connection string? Am I missing something when trying to set the connection type?

Thanks much!

Tristan

View Replies !

ForEach Loop File Locks
I vaguely remember a post a few weeks back about people having file
locks using a ForEach container looping over a directory.

I'm currently working on a package that loops through a directory and
then performs multiple dataflows/operations inside the for loop and

Here is the basic flow

For Each srcF in Dir

    Build srcF, tempF, dstF variables, basically filename variables (Script Task)

    Parse srcF and output tempF (Data flow task using
Flat File Source, Derived Columns, and Flat File Destination)

    Create dstF, write header, and copy contents of
tempF into dstF (Script Task using StreamWriter and StreamReader)

Next

The reason I need to use the script task to create the final file is
because I need to output a multi-line header to the top of the file.

The problem is that occasionally the Create dstF Script Task fails
because tempF is in use, almost like the Script Task is executing prior
to the Flat File Destination closing it's handle to the file.

Have other people run across this?

Larry Pope

View Replies !

ForEach Loop Execution Order
Hi

I am designing a ETL system to extract data from multiple systems. I have designed a batch control application and database to manage the process. I was thinking of extending this to include the execution of the SSIS packages. I would basically store all of the package details in the database, and when I am executing a particular systems load, I would get the list of packages required and loop through them in a ForEach loop. The question I have is can I guarantee the order of execution? I will put an order or execution in the DB and when I select the data, I can order by these columns.
I am concerned that in putting the data into a record set in SSIS its order could be changed resulting in the packages executing incorrectly.

Has anyone done anything similar to this and and run into problems, or is it not an issue?
Many Thanks
Michael

View Replies !

Foreach Loop With Parallel Execution
Is is possible to get the iterations in a foreach loop to run in parallel? What I need to do is to spawn an arbitrary number of parallel execution paths that all look exactly the same. The number is equal to the number of input files, which varies from time to time. Any help is appreciated!

View Replies !

Expressions In Foreach Loop Container

Dear All!
I have a "Foreach Loop Container" to search files in a folder. And I only want to search .txt and .txt.gz file. I set property "FileSpec" as "*.txt,*.txt.gz" but my container dont run. Can you help me?
Thanks

View Replies !

Foreach Loop For DBASE IV Problem
I've followed the steps on how to use the ForEach loop container using a Flat Text file and it's working fine. I tried reading the text file and put it in my SQL table. However, I have some DBF files as well and the ForEach loop does not work at all with my DBF files.

Here is my scenario:

A connection manager pointed to C:DBF_FILES

On the Control Flow: a Foreach loop container with Foreach file enumerator. (checks for IFUL*.DBF files and retrieves the Fully qualified name.

Within the foreach loop container is my DataFlow (import)

Within the dataflow:

OLEDB datasource
      data access mode: Table name or View Name variable
      Variable nam: username::Filefound

Now from this point on, I'm already having problems, here is the error:

An OLEDB error has occured.
An OLEDB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers.
Data source name not found and no default driver specified.
Error at data flow task...

There is also some error that say i don't have a destination table specified. Plus the error is on the OLEDB datasource not having a destination table? I don't understand this part.

Please help!

View Replies !

Multiple Wildcards In A Foreach Loop
I have a complex Foreach loop that needs to operate on files beginning ABC*.* and BCD*.* (but not files beginning CDE*.*). But the enumerator configuration only seems to allow one wildcard.
Any suggestions on how I do this? Thanks.

View Replies !

Foreach Loop Though All Databases On A Server
Hi

I am trying to write an SSIS package that will look though all the databases on a server (in this case 2k server) and get the list of tables in each database

I have created a Execute SQL Task with select name from master..sysdatabases where name <> 'Tempdb'

This is connected to a Foreach Loop that uses Foreach ADO Enumerator on the first table from the var generated in step 1 (List of databases)

Within the Foreach loop there is a dataflow that has a a sql command

select @@servername SQLInstanceName
,db_name() DatabaseName
, name TableName
--, getdate() RecordDate
from sysobjects where type = 'u'

This should return all the tables in a database however the databases is not changing it just lists all the tables in the master database times the number of databases on the server

Can anyone point me in the correct direction for this one

Thanks

Kevin

View Replies !

Recordset Destination And Foreach Loop
I have a csv file in which I am reading into a recordset destination and want to than use a foreach loop to cycle through those records and do some things. The problem I am having is after defining the variable name of the records set as results, and than going into the foreach loop, choosing collection, using the foreach ado enumerator, i dont see anything in the dropdown under ado object source varable?????? I am new to SSIS but I basically want to parse through this file, change some columns in each line and than either update or insert data in a sql table.

View Replies !

Foreach Loop Container In SSIS
Could someone send me any links or information on how to loop through an ADO.NET dataset in SSIS? I need step by step information please.

Thank you,

Shiva

View Replies !

ForEach Loop Or For Loop??
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 Replies !

FOREACH LOOP Container And Looping Through Record Set ...
We have a pretty simple data flow that fetches data from our SourceERP system and dumps it into a SQL Server table. This functionalityworks, what we are trying to do now is to extend this functionality bylooping through a table in SQL Server which has a list of all ODBCconnections, changing the source connection dynamically. How do I dothis? For some reason, the SQL Server online tutorial is useless sinceit just deals with file looping.Appreciate any feedback.ThanksJagannathan Santhanam

View Replies !

Foreach Loop Over Excel Files Seems 'fragile'
All,

I have a package that loops over ~60 Excel files in a directory. Each
file has three named ranges in it, which I import into different
tables. Sometimes the package runs without a hitch, sometimes it
chokes. But it is intermittent.

If I pull the control flow components out of the foreach loop and
point the Excel connection manager to the specific Excel file that has
caused the package to choke, I get a message in the dataflow component
pointing to the named range that "the metadata of the following output
columns does not match the metadata of the external columns......Do
you want to replace the metadata of the output columns with the
metadata of the external columns?" When I choose 'yes', then the
file will be loaded. then I can put the control flow components back
into the foreach loop and the file will run again, successfully, along
with some more, until it chokes again....

So, first of all, does anyone have any insight into this? Sometimes,
somedays, these files will load with no problems. These exact files;
I am having to reload constantly... Other times, like today, it is a
battle.

Otherwise, is there a way to get Integration Svcs to handle the
metadata issue on the fly???

Any ideas, resources, references, war stories, or good clean jokes
would be appreciated,
Kathryn

View Replies !