Interesting scenario - Multiple files updating one table

Hi,

i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...

Here is my problem...

The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.

File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...

The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.

What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I

Regards
Mike

You have more than one way to accomplish that, I think.

You could add an extra column to the staging table that will act as a flag to indicate whether a row was properly updated by the 2 file or not; then further steps should filter the rows based on the value of that column.

Or...

Why you don't create 2 staging tables; one for each file. Then you can use SQL statements to join them, perform some data quality checks and decide which rows are going to be processed and which ones would be rejected.

|||You could also use a Merge Join transform with an inner join prior to loading the table - you would take the good record pipelines from the two flat file sources into the Merge Join and set up an inner join on the key from each file. The pipeline output from the Merge Join would then have only the ten records that had a key match and all 9 columns, which you can then load to the SQL table desitination.|||Thanks, I used the flag method and its doing fine.

Much Appreciated Rafael
Mike

Interesting scenario - Multiple files updating one table

Hi,

i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...

Here is my problem...

The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.

File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...

The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.

What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I

Regards
Mike

You have more than one way to accomplish that, I think.

You could add an extra column to the staging table that will act as a flag to indicate whether a row was properly updated by the 2 file or not; then further steps should filter the rows based on the value of that column.

Or...

Why you don't create 2 staging tables; one for each file. Then you can use SQL statements to join them, perform some data quality checks and decide which rows are going to be processed and which ones would be rejected.

|||You could also use a Merge Join transform with an inner join prior to loading the table - you would take the good record pipelines from the two flat file sources into the Merge Join and set up an inner join on the key from each file. The pipeline output from the Merge Join would then have only the ten records that had a key match and all 9 columns, which you can then load to the SQL table desitination.|||Thanks, I used the flag method and its doing fine.

Much Appreciated Rafael
Mike

Interesting problem with table/filesystem growth

Any body have any ideas on this one?
Have an Sqlserver 2000 instance with SP3a on it and had a
problem where we had multiple data files within the
primary filegroup. An end user was adding rows into the
table and received an out of space error on the data
component. Checking the properties on the files the auto
extend option had been turned off , which was fine in our
environment, however, checking the space used in each of
these files showed that there was plenty of space
available for use in all. (No, it wasnt the trans log that
gave grief), I allowed the autoextend on each file and got
over the problem for the table in the short term , (and
saw one of the files extend.)
This lead me to do some thinking about the way Sqlserver
handles the growth of tables on multiple files. The good
book says that Sqlserver will allocate in a round robin
fashion the data pages to a table, however this doesnt
seem to be the case. Also, How does the table what file it
is on. I found this in the sysindexes table and decoding
the first value (Contains fileid and page id and row
offset).
Thats fine, however the major problem is, if Sqlserver
doesnt do the round robin allocation of datapages like it
should, then are all your free space calcs on the file
allocation valid?
Any thoughts' Any stored procedures about to handle this'
cheers
MikeWe've had an experience in the past, where the disks seemed to get overused
and reported an error similar to (unable to allocate space). Unfortunately,
I cannot
remember the specifics. We've had a problem where the auto-extend conflicts
with
an insert.
With regard to the round robin filling, sql server will fill the files using
a proporitional
algorithm. Therefore if you fill one file then add another, the 2nd file
will get filled. If
you create 2 files at the same time, then you will observe that each file is
filled with the
same amount of data each time. In short a 20GB insert will put 10GB in each
file.
HTH
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...
> Any body have any ideas on this one?
> Have an Sqlserver 2000 instance with SP3a on it and had a
> problem where we had multiple data files within the
> primary filegroup. An end user was adding rows into the
> table and received an out of space error on the data
> component. Checking the properties on the files the auto
> extend option had been turned off , which was fine in our
> environment, however, checking the space used in each of
> these files showed that there was plenty of space
> available for use in all. (No, it wasnt the trans log that
> gave grief), I allowed the autoextend on each file and got
> over the problem for the table in the short term , (and
> saw one of the files extend.)
> This lead me to do some thinking about the way Sqlserver
> handles the growth of tables on multiple files. The good
> book says that Sqlserver will allocate in a round robin
> fashion the data pages to a table, however this doesnt
> seem to be the case. Also, How does the table what file it
> is on. I found this in the sysindexes table and decoding
> the first value (Contains fileid and page id and row
> offset).
> Thats fine, however the major problem is, if Sqlserver
> doesnt do the round robin allocation of datapages like it
> should, then are all your free space calcs on the file
> allocation valid?
> Any thoughts' Any stored procedures about to handle this'
> cheers
> Mike|||Interesting,
still leads to the problem where you think you should have
space available because you tally the total of all file
systems and unfortunately one is full!!! Kind of makes one
stop and think about what level should your space
statistcs be collected at and how you manage them!
>--Original Message--
>We've had an experience in the past, where the disks
seemed to get overused
>and reported an error similar to (unable to allocate
space). Unfortunately,
>I cannot
>remember the specifics. We've had a problem where the
auto-extend conflicts
>with
>an insert.
>
>With regard to the round robin filling, sql server will
fill the files using
>a proporitional
>algorithm. Therefore if you fill one file then add
another, the 2nd file
>will get filled. If
>you create 2 files at the same time, then you will
observe that each file is
>filled with the
>same amount of data each time. In short a 20GB insert
will put 10GB in each
>file.
>HTH
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...
>> Any body have any ideas on this one?
>> Have an Sqlserver 2000 instance with SP3a on it and had
a
>> problem where we had multiple data files within the
>> primary filegroup. An end user was adding rows into the
>> table and received an out of space error on the data
>> component. Checking the properties on the files the auto
>> extend option had been turned off , which was fine in
our
>> environment, however, checking the space used in each of
>> these files showed that there was plenty of space
>> available for use in all. (No, it wasnt the trans log
that
>> gave grief), I allowed the autoextend on each file and
got
>> over the problem for the table in the short term , (and
>> saw one of the files extend.)
>> This lead me to do some thinking about the way Sqlserver
>> handles the growth of tables on multiple files. The good
>> book says that Sqlserver will allocate in a round robin
>> fashion the data pages to a table, however this doesnt
>> seem to be the case. Also, How does the table what file
it
>> is on. I found this in the sysindexes table and decoding
>> the first value (Contains fileid and page id and row
>> offset).
>> Thats fine, however the major problem is, if Sqlserver
>> doesnt do the round robin allocation of datapages like
it
>> should, then are all your free space calcs on the file
>> allocation valid?
>> Any thoughts' Any stored procedures about to handle
this'
>> cheers
>> Mike
>
>.
>

Interesting problem with table/filesystem growth

Any body have any ideas on this one?
Have an Sqlserver 2000 instance with SP3a on it and had a
problem where we had multiple data files within the
primary filegroup. An end user was adding rows into the
table and received an out of space error on the data
component. Checking the properties on the files the auto
extend option had been turned off , which was fine in our
environment, however, checking the space used in each of
these files showed that there was plenty of space
available for use in all. (No, it wasnt the trans log that
gave grief), I allowed the autoextend on each file and got
over the problem for the table in the short term , (and
saw one of the files extend.)
This lead me to do some thinking about the way Sqlserver
handles the growth of tables on multiple files. The good
book says that Sqlserver will allocate in a round robin
fashion the data pages to a table, however this doesnt
seem to be the case. Also, How does the table what file it
is on. I found this in the sysindexes table and decoding
the first value (Contains fileid and page id and row
offset).
Thats fine, however the major problem is, if Sqlserver
doesnt do the round robin allocation of datapages like it
should, then are all your free space calcs on the file
allocation valid?
Any thoughts' Any stored procedures about to handle this'
cheers
MikeWe've had an experience in the past, where the disks seemed to get overused
and reported an error similar to (unable to allocate space). Unfortunately,
I cannot
remember the specifics. We've had a problem where the auto-extend conflicts
with
an insert.
With regard to the round robin filling, sql server will fill the files using
a proporitional
algorithm. Therefore if you fill one file then add another, the 2nd file
will get filled. If
you create 2 files at the same time, then you will observe that each file is
filled with the
same amount of data each time. In short a 20GB insert will put 10GB in each
file.
HTH
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...

quote:

---

> Any body have any ideas on this one?
> Have an Sqlserver 2000 instance with SP3a on it and had a
> problem where we had multiple data files within the
> primary filegroup. An end user was adding rows into the
> table and received an out of space error on the data
> component. Checking the properties on the files the auto
> extend option had been turned off , which was fine in our
> environment, however, checking the space used in each of
> these files showed that there was plenty of space
> available for use in all. (No, it wasnt the trans log that
> gave grief), I allowed the autoextend on each file and got
> over the problem for the table in the short term , (and
> saw one of the files extend.)
> This lead me to do some thinking about the way Sqlserver
> handles the growth of tables on multiple files. The good
> book says that Sqlserver will allocate in a round robin
> fashion the data pages to a table, however this doesnt
> seem to be the case. Also, How does the table what file it
> is on. I found this in the sysindexes table and decoding
> the first value (Contains fileid and page id and row
> offset).
> Thats fine, however the major problem is, if Sqlserver
> doesnt do the round robin allocation of datapages like it
> should, then are all your free space calcs on the file
> allocation valid?
> Any thoughts' Any stored procedures about to handle this'
> cheers
> Mike

---

|||Interesting,
still leads to the problem where you think you should have
space available because you tally the total of all file
systems and unfortunately one is full!!! Kind of makes one
stop and think about what level should your space
statistcs be collected at and how you manage them!

quote:

---

>--Original Message--
>We've had an experience in the past, where the disks

---

seemed to get overused

quote:

---

>and reported an error similar to (unable to allocate

---

space). Unfortunately,

quote:

---

>I cannot
>remember the specifics. We've had a problem where the

---

auto-extend conflicts

quote:

---

>with
>an insert.
>
>With regard to the round robin filling, sql server will

---

fill the files using

quote:

---

>a proporitional
>algorithm. Therefore if you fill one file then add

---

another, the 2nd file

quote:

---

>will get filled. If
>you create 2 files at the same time, then you will

---

observe that each file is

quote:

---

>filled with the
>same amount of data each time. In short a 20GB insert

---

will put 10GB in each

quote:

---

>file.
>HTH
>"Mike" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...
a[QUOTE]
our[QUOTE]
that[QUOTE]
got[QUOTE]
it[QUOTE]
it[QUOTE]
this'[QUOTE]
>
>.
>

---

sql

Interactive sort doesn't work for multiple sub-reports

I have a report, which contains two nearly identical sub reports. In the IDE, when I test the containing report, each of the sub-reports works fine, in particular, the interactive sort feature works for both. When I deploy the reports to the test server (desktop running Windows XP, SQL Server 2005 Developer’s Edition, and SQL Reporting Services 2005, reports are served as web pages), the interactive sort feature seems to work only for one of the sub-reports. The other sub-report causes a postback, and refreshes, but does not return sorted. It appears as though whichever sub-report I attempt to sort first, sorts fine. When I attempt to sort the other sub-report, it does not sort.

Any ideas?

May not help, but when I have gotten these types of errors before, often deleting the primary and sub-reports from the Report Server and then redeploying all reports will help. I don't know why, but we have to do that often for deployment when there are certian types of changes.

Good luck.

|||Thanks for the reply! Unfortunately, this did not help. Anyone else run into problems with the built-in sorting for sub-reports?|||

Hi Dan -

I was not able to reproduce your problem with mulitple subreports and user sort. Could you please go to http://connect.microsoft.com/ and file a defect report and attach your .rdl files.

Thanks, Jon
SQL Reporting Services Team

|||

i have the same problem. A report with 3 sub-reports, on the third one the sort seem's to be disabled, i can see arrows but if i click on them only a postback occur without sorting and the two arrows stay (normally only one remain into a blank circle after a sort). If i run this sub-report alone, the sorting is working.

If i remove the third sub-report, the same problem happen on the second sub-report. The sorting was working before. Strange bug i think.

I have SQL Server 2005 SP1 with the latest fix after SP1 installed.

Sorry for my poor english... :)

Interactive sort doesn't work for multiple sub-reports

I have a report, which contains two nearly identical sub reports. In the IDE, when I test the containing report, each of the sub-reports works fine, in particular, the interactive sort feature works for both. When I deploy the reports to the test server (desktop running Windows XP, SQL Server 2005 Developer’s Edition, and SQL Reporting Services 2005, reports are served as web pages), the interactive sort feature seems to work only for one of the sub-reports. The other sub-report causes a postback, and refreshes, but does not return sorted. It appears as though whichever sub-report I attempt to sort first, sorts fine. When I attempt to sort the other sub-report, it does not sort.

Any ideas?

May not help, but when I have gotten these types of errors before, often deleting the primary and sub-reports from the Report Server and then redeploying all reports will help. I don't know why, but we have to do that often for deployment when there are certian types of changes.

Good luck.

|||Thanks for the reply! Unfortunately, this did not help. Anyone else run into problems with the built-in sorting for sub-reports?|||

Hi Dan -

I was not able to reproduce your problem with mulitple subreports and user sort. Could you please go to http://connect.microsoft.com/ and file a defect report and attach your .rdl files.

Thanks, Jon
SQL Reporting Services Team

|||

i have the same problem. A report with 3 sub-reports, on the third one the sort seem's to be disabled, i can see arrows but if i click on them only a postback occur without sorting and the two arrows stay (normally only one remain into a blank circle after a sort). If i run this sub-report alone, the sorting is working.

If i remove the third sub-report, the same problem happen on the second sub-report. The sorting was working before. Strange bug i think.

I have SQL Server 2005 SP1 with the latest fix after SP1 installed.

Sorry for my poor english... :)

Integrity with multiple commands

How can I make sure that a couple of commands are either all executed on the database or none of them. For example right now I have an insert, update and delete command. I'm calling each of them with a SqlCommand. So I am afraid that that one of them might be executed, then there's a bad connection and the other two are not. How can I prevent this so that only all commands or nothing is executed on the database?

Write them in a stored procedure~

Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit.

In short, it's something atomic, a lot of database use some mechanism such as rolling back when a sp is terminated unexpectedly, so either none or all of your command in a sp will be executed~

|||

A stored procedure has absolutely nothing to do with it. You want to place your code within a transaction. You can do this manaully by specifying a BEGIN TRANSACTION (your statements here) and then either ROLLBACK TRANSACTION or COMMIT TRANSACTION, *OR* you can use the sqltransaction object to put multiple sqlcommands within the same transaction.

You can of course put the whole thing in a stored procedure as well for convience, but putting things in a sp won't guarantee they will either all commit or rollback.

This would be an example of what you can place within the commandtext of the sqlcommand property:

dim cmd as new sqlcommand("SET @.RetVal=0; BEGIN TRANSACTION; INSERT INTO Table1(col1) VALUES (@.col1); IF @.@.ERROR=0 BEGIN INSERT INTO Table2(idcol,col2) VALUES (SCOPE_IDENTITY(),@.col2) IF @.@.ERROR=0 BEGIN COMMIT TRANSACTION SET @.RetVal=1 END END IF @.RetVal=0 ROLLBACK TRANSACTION")

cmd.Parameters.Add("@.col1",varchar).value=something

cmd.Parameters.Add("@.col2",varchar).value=something

cmd.Parameters.Add("@.retval",int).Direction=output

cmd.executenonquery()

if cmd.Parameters("@.retval").Value=0 then

' It failed

end if

You can also do it this way:

dim cmd as new sqlcommand("SET @.RetVal=0; SET XACT_ABORT ON; BEGIN TRANSACTION; INSERT INTO Table1(col1) VALUES (@.col1); INSERT INTO Table2(idcol,col2) VALUES (SCOPE_IDENTITY(),@.col2); COMMIT TRANSACTION; SET @.RetVal=1")

That should also work as well.

Integrity across multiple database types

Since most use SQL server, I thought I would post the question here.
Is it possible, or is there a product or DBMS that enforces referential integrity across multiple databases and database types? Such as SQL Server, Oracle, etc...
Thanks,
ZathDRI(declarative referential integrity) is Peter Chen ERD 1976 enable data delete and update in multiple tables at the same time but you cannot set it up across two databases but if you plan very well you can use linked server to access separate DRI(declarative referential integrity) rules setup in two databases. CASCADE ON DELETE, CASCADE ON UPDATE, ON DELETE NO ACTION and ON UPDATE NO ACTION the later will not allow delete or update. Run a search for all in the BOL(books online). Hope this helps.|||Thanks, just the start I needed.
Zath

Integration Services

When I try to run Data Flow to get multiple mdb files getting this error
any thoughts '
TITLE: Package Validation Error
--
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection m
ethod
call to the connection manager "Northwind_1" failed with error code
0xC0202009.
Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) fa
iled
validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed
validation.
Error at Data Flow Task: There were errors during task validation.
Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error
has
occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
Manager]
Data source name not found and no default driver specified".
(Microsoft.DataTransformationServices.VsIntegration)Gopinath,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
> When I try to run Data Flow to get multiple mdb files getting this error
> any thoughts '
>
> TITLE: Package Validation Error
> --
> Package Validation Error
> --
> ADDITIONAL INFORMATION:
> Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection
method
> call to the connection manager "Northwind_1" failed with error code
> 0xC0202009.
> Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1)
> failed
> validation and returned error code 0xC020801C.
> Error at Data Flow Task [DTS.Pipeline]: One or more component failed
> validation.
> Error at Data Flow Task: There were errors during task validation.
> Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB err
or
> has
> occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> ODBC
> Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driv
er
> Manager]
> Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegration)
>|||Hi Jerry,
Please check the URL its not have any details about my question, Am I
missing anything.
Thanks
Gopi
"Jerry Spivey" wrote:

> Gopinath,
> Please see:
> http://www.aspfaq.com/sql2005/show.asp?id=1
> HTH
> Jerry
> "Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
> news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
>
>|||If the SSIS package doesn't validate then a likely cause is that you
have configured something incorrectly.
The first thing I would check is the OLE DB Connection Manager that
you are using.
Go over each step of the configuration and check that you set it up
correctly.
If you don't see a solution by that simple expedient I suggest that
you go to
http://forums.microsoft.com/msdn/de...ForumGroupID=19 and post
your question on the Integration Services forum.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 08:00:05 -0700, "Gopinath"
<Gopinath@.discussions.microsoft.com> wrote:

>When I try to run Data Flow to get multiple mdb files getting this error
>any thoughts '
>
>TITLE: Package Validation Error
>--
>Package Validation Error
>--
>ADDITIONAL INFORMATION:
>Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection
method
>call to the connection manager "Northwind_1" failed with error code
>0xC0202009.
>Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) f
ailed
>validation and returned error code 0xC020801C.
>Error at Data Flow Task [DTS.Pipeline]: One or more component failed
>validation.
>Error at Data Flow Task: There were errors during task validation.
>Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB erro
r has
>occurred. Error code: 0x80004005.
>An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
>Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Drive
r Manager]
>Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegration)|||Gopi,
The URL is basically indicating the *best* community to answer your SQL
Server 2005 questions. SQL Server 2005 has not yet been released and is
still in BETA. Choosing the best NG to answer your post will likely expite
an answer and will help minimize a duplication of effort when posting to a
2000 and 2005 NG.
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:5CA99ED7-07C4-48A2-883A-046CD61F007E@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Please check the URL its not have any details about my question, Am I
> missing anything.
> Thanks
> Gopi
>
> "Jerry Spivey" wrote:
>

Integration Services

When I try to run Data Flow to get multiple mdb files getting this error
any thoughts '
TITLE: Package Validation Error
--
Package Validation Error
--
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
call to the connection manager "Northwind_1" failed with error code
0xC0202009.
Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) failed
validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed
validation.
Error at Data Flow Task: There were errors during task validation.
Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error has
occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified".
(Microsoft.DataTransformationServices.VsIntegration)Gopinath,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
> When I try to run Data Flow to get multiple mdb files getting this error
> any thoughts '
>
> TITLE: Package Validation Error
> --
> Package Validation Error
> --
> ADDITIONAL INFORMATION:
> Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
> call to the connection manager "Northwind_1" failed with error code
> 0xC0202009.
> Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1)
> failed
> validation and returned error code 0xC020801C.
> Error at Data Flow Task [DTS.Pipeline]: One or more component failed
> validation.
> Error at Data Flow Task: There were errors during task validation.
> Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error
> has
> occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> ODBC
> Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> Manager]
> Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegration)
>|||Hi Jerry,
Please check the URL its not have any details about my question, Am I
missing anything.
Thanks
Gopi
"Jerry Spivey" wrote:
> Gopinath,
> Please see:
> http://www.aspfaq.com/sql2005/show.asp?id=1
> HTH
> Jerry
> "Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
> news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
> > When I try to run Data Flow to get multiple mdb files getting this error
> >
> > any thoughts '
> >
> >
> > TITLE: Package Validation Error
> > --
> >
> > Package Validation Error
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
> > call to the connection manager "Northwind_1" failed with error code
> > 0xC0202009.
> >
> > Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1)
> > failed
> > validation and returned error code 0xC020801C.
> >
> > Error at Data Flow Task [DTS.Pipeline]: One or more component failed
> > validation.
> >
> > Error at Data Flow Task: There were errors during task validation.
> >
> > Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error
> > has
> > occurred. Error code: 0x80004005.
> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> > ODBC
> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> > Manager]
> > Data source name not found and no default driver specified".
> >
> > (Microsoft.DataTransformationServices.VsIntegration)
> >
> >
>
>|||If the SSIS package doesn't validate then a likely cause is that you
have configured something incorrectly.
The first thing I would check is the OLE DB Connection Manager that
you are using.
Go over each step of the configuration and check that you set it up
correctly.
If you don't see a solution by that simple expedient I suggest that
you go to
http://forums.microsoft.com/msdn/default.aspx?ForumGroupID=19 and post
your question on the Integration Services forum.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 08:00:05 -0700, "Gopinath"
<Gopinath@.discussions.microsoft.com> wrote:
>When I try to run Data Flow to get multiple mdb files getting this error
>any thoughts '
>
>TITLE: Package Validation Error
>--
>Package Validation Error
>--
>ADDITIONAL INFORMATION:
>Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
>call to the connection manager "Northwind_1" failed with error code
>0xC0202009.
>Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) failed
>validation and returned error code 0xC020801C.
>Error at Data Flow Task [DTS.Pipeline]: One or more component failed
>validation.
>Error at Data Flow Task: There were errors during task validation.
>Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error has
>occurred. Error code: 0x80004005.
>An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
>Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
>Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegration)|||Gopi,
The URL is basically indicating the *best* community to answer your SQL
Server 2005 questions. SQL Server 2005 has not yet been released and is
still in BETA. Choosing the best NG to answer your post will likely expite
an answer and will help minimize a duplication of effort when posting to a
2000 and 2005 NG.
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:5CA99ED7-07C4-48A2-883A-046CD61F007E@.microsoft.com...
> Hi Jerry,
> Please check the URL its not have any details about my question, Am I
> missing anything.
> Thanks
> Gopi
>
> "Jerry Spivey" wrote:
>> Gopinath,
>> Please see:
>> http://www.aspfaq.com/sql2005/show.asp?id=1
>> HTH
>> Jerry
>> "Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
>> news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
>> > When I try to run Data Flow to get multiple mdb files getting this
>> > error
>> >
>> > any thoughts '
>> >
>> >
>> > TITLE: Package Validation Error
>> > --
>> >
>> > Package Validation Error
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection
>> > method
>> > call to the connection manager "Northwind_1" failed with error code
>> > 0xC0202009.
>> >
>> > Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1)
>> > failed
>> > validation and returned error code 0xC020801C.
>> >
>> > Error at Data Flow Task [DTS.Pipeline]: One or more component failed
>> > validation.
>> >
>> > Error at Data Flow Task: There were errors during task validation.
>> >
>> > Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB
>> > error
>> > has
>> > occurred. Error code: 0x80004005.
>> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
>> > ODBC
>> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
>> > Manager]
>> > Data source name not found and no default driver specified".
>> >
>> > (Microsoft.DataTransformationServices.VsIntegration)
>> >
>> >
>>

Integration Services

When I try to run Data Flow to get multiple mdb files getting this error
any thoughts ?
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
call to the connection manager "Northwind_1" failed with error code
0xC0202009.
Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) failed
validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed
validation.
Error at Data Flow Task: There were errors during task validation.
Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error has
occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified".
(Microsoft.DataTransformationServices.VsIntegratio n)
Gopinath,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
> When I try to run Data Flow to get multiple mdb files getting this error
> any thoughts ?
>
> TITLE: Package Validation Error
> --
> Package Validation Error
> --
> ADDITIONAL INFORMATION:
> Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
> call to the connection manager "Northwind_1" failed with error code
> 0xC0202009.
> Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1)
> failed
> validation and returned error code 0xC020801C.
> Error at Data Flow Task [DTS.Pipeline]: One or more component failed
> validation.
> Error at Data Flow Task: There were errors during task validation.
> Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error
> has
> occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> ODBC
> Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> Manager]
> Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegratio n)
>
|||Hi Jerry,
Please check the URL its not have any details about my question, Am I
missing anything.
Thanks
Gopi
"Jerry Spivey" wrote:

> Gopinath,
> Please see:
> http://www.aspfaq.com/sql2005/show.asp?id=1
> HTH
> Jerry
> "Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
> news:628161DE-68B2-47F5-9420-897A93BB7D32@.microsoft.com...
>
>
|||If the SSIS package doesn't validate then a likely cause is that you
have configured something incorrectly.
The first thing I would check is the OLE DB Connection Manager that
you are using.
Go over each step of the configuration and check that you set it up
correctly.
If you don't see a solution by that simple expedient I suggest that
you go to
http://forums.microsoft.com/msdn/def...orumGroupID=19 and post
your question on the Integration Services forum.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 08:00:05 -0700, "Gopinath"
<Gopinath@.discussions.microsoft.com> wrote:

>When I try to run Data Flow to get multiple mdb files getting this error
>any thoughts ?
>
>TITLE: Package Validation Error
>--
>Package Validation Error
>--
>ADDITIONAL INFORMATION:
>Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method
>call to the connection manager "Northwind_1" failed with error code
>0xC0202009.
>Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Source" (1) failed
>validation and returned error code 0xC020801C.
>Error at Data Flow Task [DTS.Pipeline]: One or more component failed
>validation.
>Error at Data Flow Task: There were errors during task validation.
>Error at ForEachLoop [Connection manager "Northwind_1"]: An OLE DB error has
>occurred. Error code: 0x80004005.
>An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
>Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
>Data source name not found and no default driver specified".
> (Microsoft.DataTransformationServices.VsIntegratio n)
|||Gopi,
The URL is basically indicating the *best* community to answer your SQL
Server 2005 questions. SQL Server 2005 has not yet been released and is
still in BETA. Choosing the best NG to answer your post will likely expite
an answer and will help minimize a duplication of effort when posting to a
2000 and 2005 NG.
HTH
Jerry
"Gopinath" <Gopinath@.discussions.microsoft.com> wrote in message
news:5CA99ED7-07C4-48A2-883A-046CD61F007E@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Please check the URL its not have any details about my question, Am I
> missing anything.
> Thanks
> Gopi
>
> "Jerry Spivey" wrote: