getting the list of connection strings to display in the SqlDataSource Wizard

I've added an SqlDataSource control to my web page and selected "Configure Data Source" on it. This brings up a "Choose Your Data Connection" wizard, and it asks you to select from a dropdown list of presumably pre-existing connection strings in my web.config file. However none of my connection strings will display in that dropdown list. The only thing i can do is hit the "Create New Connection" button and this adds yet another connection string to my web.config file.

I've already ran through the wizard once, created a new connection string, but when i run through the wizard again, even the new connection string - the one created by the last wizard will not appear in the dropdown list.

Any suggestions?

Jason

Hi Jason,

Are the connection strings actually appearing in the web.config file? Are they actually working - that is, if you call one of the connection strings, does it actually do what you expect? I can't offer any actual solutions to this one, but have you tried creating a new web.config file and checking if this fixes the problem? Personally, I would set up a new application just to test this, and if it works OK in the new application then perhaps something in your current application has caused the issue.

Hope this helps.

Paul

|||

Hi Paul - yes i've tried your suggestions before but nothing works. In fact, even after already running "Choose Your Data Connection" wizard (and selecting the new datasource option) to let it generate a datasource for me, and then running the wizard again to see if the previously created datasource will appear, and it doesn't.

Not a big deal i guess but a bit of a pain. I wonder if its a bug...

Jason

|||

Hi Jason,

Please be sure that you have saved the string (a checkbox for you to select) the first time you use it.

Thanks.

sql

getting source records table name?

Hello,
I need some help on getting the name of a table, or even creating an id
of some kind, that relates to where a record was pulled from. Ive
included a *simplified* example so you can see what Im talking about
say I have 3 tables, and a query that returns 2 rows, each row made up
of 1 column from table1 and 1 column from either table2 or table3.
There is no unique identifier I can use in table2 or table3.
Ive played with using UNION but it doesnt suit a complex query like
the actual query Im using. Maybe theres some stored procedure, or
another hack to solve this?
Any help is appreciated,
Craig.
table1
--
table1_id
1
2
table2
--
table2_id, table2.table1_id
1,1
2,NULL
table3
--
table3_id, table3.table1_id
1,NULL
2,1
query
--
select table1.table1_id, X
from table1
left join table2
on table1.table1_id = table2.table1_id
left join table3
on table1.table1_id = table3.table3_id
result
--
table1_id
1, X
1, X
(where X displays the source table name)
"The only way to get rid of temptation is to yield to it...""Craig H." <spam@.thehurley.com> wrote in message
news:OAnj6GEIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> query
> --
> select table1.table1_id, X
> from table1
> left join table2
> on table1.table1_id = table2.table1_id
> left join table3
> on table1.table1_id = table3.table3_id
> result
> --
> table1_id
> 1, X
> 1, X
Craig,
I'm -- the value in column 1, in your query, will ALWAYS come
from table1. Is your real query different in some way? Please post the
actual code you're trying to work with.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||On 03/03/2005 14:47, Adam Machanic wrote:
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."

> "Craig H." <spam@.thehurley.com> wrote in message
> news:OAnj6GEIFHA.2656@.TK2MSFTNGP09.phx.gbl...
>
>
> Craig,
> I'm -- the value in column 1, in your query, will ALWAYS come
> from table1. Is your real query different in some way? Please post the
> actual code you're trying to work with.
>
That's correct Adam, but it's the source of column 2 in the result that
I need to obtain (bear in mind that there is nothing to identify what
the table represents in the data contained within table2 & table3). The
actual query looks like:
select le.Name as Client, lec.Name as CompanyType, div.name as Division,
p.Name as PayeeName
from lentity le
inner join contract c
on ((c.contracting_lentity_id = le.lentity_id) and (c.enddate IS NULL or
c.enddate > getdate()))
inner join lentity div
on div.lentity_ID = c.providing_lentity_id
inner join lentityCode lec
on lec.lentityCode = le.lentityCode
inner join payee p
on ((p.contract_id = c.contract_id) and (p.active = 'true'))
inner join paymentrouting pr
on pr.payee_id = p.payee_id
left join aPaymentChannel apc
on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
'true'))
left join bPaymentChannel bpc
on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
'true'))
left join cPaymentChannel cpc
on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
'true'))
left join dPaymentChannel dpc
on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
'true'))
left join ePaymentChannel gpc
on ((epc.paymentrouting_id = pr.paymentrouting_id) and (epc.active =
'true'))
left join fPaymentChannel fpc
on ((fpc.paymentrouting_id = pr.paymentrouting_id) and (fpc.active =
'true'))
order by Client|||"Craig H." <spam@.thehurley.com> wrote in message
news:O%23DbBdEIFHA.2564@.tk2msftngp13.phx.gbl...
> select le.Name as Client, lec.Name as CompanyType, div.name as Division,
> p.Name as PayeeName
Which column are you concerned with?
The first will always come from le, the second from lec, the third from
div, and the fourth from p...
Can you show me exactly what the issue is?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||On 03/03/2005 16:46, Adam Machanic wrote:
> "Craig H." <spam@.thehurley.com> wrote in message
> news:O%23DbBdEIFHA.2564@.tk2msftngp13.phx.gbl...
>
>
> Which column are you concerned with?
> The first will always come from le, the second from lec, the third fro
m
> div, and the fourth from p...
> Can you show me exactly what the issue is?
>
O.K. I didn't explain myself clearly enough. Every time the 1st, 2nd,
3rd and 4th columns are returned in a result, I would also like to
return a column indicating which left join statement caused a match (or
which XPaymentChannel a match was was found in):
left join XPaymentChannel X
on ((X.paymentrouting_id = pr.paymentrouting_id) and (X.active = 'true'))
The reason I'm finding this difficult is because the XPaymentChannel
tables have no column that differentiate it from the other
XPaymentChannel tables.
Is that clearer? Thanks for your time so far Adam.
Craig.
"You can't build a reputation on what you are going to do."|||>> ..each row made up of 1 column from table1 and 1 column from either
table2 or table3.
There is no unique identifier I can use in table2 or table3. <<
This sounds like the design is screwed up and you have either put the
same data in two places, or the table you return is made of mixed
tuples. And two tables without a key is just plain wrong!
Next, you will be using a lot of NULLs and setting flags all over the
place, or using dynamic SQL :)|||Okay... I think you want to add:
CASE
WHEN apc.paymentrouting_id IS NOT NULL THEN 'apc'
WHEN bpc.paymentrouting_id IS NOT NULL THEN 'bpc'
WHEN cpc.paymentrouting_id IS NOT NULL THEN 'cpc'
..
ELSE NULL
END AS PaymentRoutingTbl
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Craig H." <spam@.thehurley.com> wrote in message
news:ui66XaFIFHA.2704@.tk2msftngp13.phx.gbl...
> On 03/03/2005 16:46, Adam Machanic wrote:
from
> O.K. I didn't explain myself clearly enough. Every time the 1st, 2nd,
> 3rd and 4th columns are returned in a result, I would also like to
> return a column indicating which left join statement caused a match (or
> which XPaymentChannel a match was was found in):
> left join XPaymentChannel X
> on ((X.paymentrouting_id = pr.paymentrouting_id) and (X.active = 'true'))
> The reason I'm finding this difficult is because the XPaymentChannel
> tables have no column that differentiate it from the other
> XPaymentChannel tables.
> Is that clearer? Thanks for your time so far Adam.
> Craig.
>
> --
> "You can't build a reputation on what you are going to do."|||On 03/03/2005 17:40, Adam Machanic wrote:
> Okay... I think you want to add:
> CASE
> WHEN apc.paymentrouting_id IS NOT NULL THEN 'apc'
> WHEN bpc.paymentrouting_id IS NOT NULL THEN 'bpc'
> WHEN cpc.paymentrouting_id IS NOT NULL THEN 'cpc'
> ...
> ELSE NULL
> END AS PaymentRoutingTbl
>
Perfect, thank you Adam!
"The only way to get rid of temptation is to yield to it..."

Getting second row in a data source

Hi,

I'm generating a report where 3 photos can be shown on a report if there are 3 photos in our data source.

they are layed out with the main photo on the left, and the other 2 photos on top of each other on the right.

The dataset that is returned from the query has a column called PhotoURL and each photo is a row of the returned dataset

I have inserted three images on the report

The first image I set the value to be =First(Fields!PhotoURL.Value, "Image")

How do you get the second image to have a value of the second row of the dataset, I mean there is no Second function, or movenext that I can see.

The same applies with the third image.

Thanks in advance

Auschucky

Hello Auschucky,

The First function is an aggregation function, which is not suitable for your images-issue.

When the dataset is returning a number of rows, you have to use a list or table on the report to get all rows (images) on the report. However, this doesn't match your report design.

If the dataset always contains one, two or three images, I suggest to change the select statement to return one row with three url fields. You can attach these fields to your images on the report.

Getting problem in AcquireConnections

Hi All,

I just started looking at the SSIS programming, wanted to created a package having table to table data transfer. My source and destination databases are in Oracle.

I gone throught the code samples and started creating the source component. And the whatever samples i've seen the code i have written (copied) looks correct to me but still getting following error.

The basic questions i have is,

1. Do i need to setup something to start programming in SSIS. I am using 'Microsoft Visual C# Express Edition' for programming.

I have all the dll's in place.

And after compiling the code the exception i got is,

{"Exception from HRESULT: 0xC020801C"} System.Runtime.InteropServices.COMException was caught

Message="Exception from HRESULT: 0xC020801C"

Source="Microsoft.SqlServer.DTSPipelineWrap"

ErrorCode=-1071611876

StackTrace:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)

The Code is as Follows:

public static void CreateSource()

{

Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

Executable e = package.Executables.Add("DTS.Pipeline.1");

Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = e as Microsoft.SqlServer.Dts.Runtime.TaskHost;

MainPipe dataFlow = thMainPipe.InnerObject as MainPipe;

// Add an OLEDB connection manager that is used by the component to the package.

ConnectionManager cm = package.Connections.Add("OLEDB");

cm.Name = "OLEDB ConnectionManager";

cm.ConnectionString = @."Data Source=pqdb9i;User ID=srcDbUserId;Provider=MSDAORA.1;Persist Security Info=False;Integrated Security=SSPI;Auto Translate=False;";

package.DelayValidation = true;

cm.DelayValidation = true;

component = dataFlow.ComponentMetaDataCollection.New();

component.Name = "OLEDBSource";

component.ComponentClassID = "DTSAdapter.OleDbSource.1";

// Get the design time instance of the component.

CManagedComponentWrapper instance = component.Instantiate();

// Initialize the component

instance.ProvideComponentProperties();

// Specify the connection manager.

if (component.RuntimeConnectionCollection.Count > 0)

{

component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;

component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);

}

// Set the custom properties.

//instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);

instance.SetComponentProperty("AccessMode", 2);

instance.SetComponentProperty("SqlCommand", "Select * from srcTable");

// Reinitialize the metadata.

try

{

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

Console.WriteLine(component.InputCollection.Count);

}

///End Code

Thanks in advance

-Yuwaraj

Does Oracle use Windows Authentication in your case? If not, where's the password for the database user?|||Hi

I have the same problem, did u find any solution?

Getting password from another source!

I am using a shared data source to access a Teradata machine but the password
I need to use is generated elsewhere in a component and changed regularly.
Is there a way I can get the shared data source to grab the current password
from the component on the fly?Interesting. Every now and then I come across a question that leaves me
baffled for a while. The guy who implemented this must have been really
influenced by trustworthy computing :-) I am just curious how will the data
source be synchronized with the new password.
Well, you can't change the connection string during runtime which limits (at
least as far as I can tell) your choices to:
1. If that component raises an event or if you have other means to know when
the password is going to be changed you can write a simple app to update the
shared data source definition programmatically.
2. Wrap this data source in a custom data extension.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
news:EF4BC011-4E6B-4E84-B54A-339B27C7DB0D@.microsoft.com...
> I am using a shared data source to access a Teradata machine but the
password
> I need to use is generated elsewhere in a component and changed regularly.
> Is there a way I can get the shared data source to grab the current
password
> from the component on the fly?|||Look at SetDataSourceContents SOAP API.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
news:75326DAC-383F-42E5-A5B9-27D8DBA9F8E5@.microsoft.com...
> I can get the password change to trigger an event no problem so option 1
> looks good but I can't find any syntax on updating the shared data source
> definition programmatically.
> Thanks for your help!
> "Teo Lachev" wrote:
> > Interesting. Every now and then I come across a question that leaves me
> > baffled for a while. The guy who implemented this must have been really
> > influenced by trustworthy computing :-) I am just curious how will the
data
> > source be synchronized with the new password.
> >
> > Well, you can't change the connection string during runtime which limits
(at
> > least as far as I can tell) your choices to:
> >
> > 1. If that component raises an event or if you have other means to know
when
> > the password is going to be changed you can write a simple app to update
the
> > shared data source definition programmatically.
> >
> > 2. Wrap this data source in a custom data extension.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> > news:EF4BC011-4E6B-4E84-B54A-339B27C7DB0D@.microsoft.com...
> > > I am using a shared data source to access a Teradata machine but the
> > password
> > > I need to use is generated elsewhere in a component and changed
regularly.
> > > Is there a way I can get the shared data source to grab the current
> > password
> > > from the component on the fly?
> >
> >
> >|||Thanks for that!
"Teo Lachev" wrote:
> Look at SetDataSourceContents SOAP API.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> news:75326DAC-383F-42E5-A5B9-27D8DBA9F8E5@.microsoft.com...
> > I can get the password change to trigger an event no problem so option 1
> > looks good but I can't find any syntax on updating the shared data source
> > definition programmatically.
> >
> > Thanks for your help!
> >
> > "Teo Lachev" wrote:
> >
> > > Interesting. Every now and then I come across a question that leaves me
> > > baffled for a while. The guy who implemented this must have been really
> > > influenced by trustworthy computing :-) I am just curious how will the
> data
> > > source be synchronized with the new password.
> > >
> > > Well, you can't change the connection string during runtime which limits
> (at
> > > least as far as I can tell) your choices to:
> > >
> > > 1. If that component raises an event or if you have other means to know
> when
> > > the password is going to be changed you can write a simple app to update
> the
> > > shared data source definition programmatically.
> > >
> > > 2. Wrap this data source in a custom data extension.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ----
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ----
> > >
> > > "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> > > news:EF4BC011-4E6B-4E84-B54A-339B27C7DB0D@.microsoft.com...
> > > > I am using a shared data source to access a Teradata machine but the
> > > password
> > > > I need to use is generated elsewhere in a component and changed
> regularly.
> > > > Is there a way I can get the shared data source to grab the current
> > > password
> > > > from the component on the fly?
> > >
> > >
> > >
>
>

Getting only first letter of the column

Hi,

I am using an OLEDB source to run a stored procedure, which returns records from a temp table. The destination table is exactly same as the temp table in the stored procedure. I've some collation settings on both the destination table and temp table, but both are exactly same.

I got Unicode to Non-unicode conversion error first. I dont know why it happened as there is no difference in source and destination table. I solved that issue using the data conversion component. Then I got the truncation error. I set the Ignore on Truncation on error output to get rid of that issue. Then the package executed without any problem. But all the nvarchar and varchar fields in the destination table got populated with only the first letter of data.

Any Idea?

hi

check the limits of the varchar values in the both tables and match them

|||

Hi Ali Naqvi,

Thanks for your reply. Actually the problem was with a dummy SELECT statement that I put on top of the SP for getting the column metadata. Once I made the type casting to proper type and length inside the SELECT query, both unicode conversion error and truncation error are gone. Now the package is running fine without any error.

Getting id values from both source and target rows when duplicating records

Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?

I tried the following, but it doesn't seem to work:

INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @.number

You have to use the following logic, On OUTPUT clause you can’t use other than INSERT & DELETED table values. ID should be available on the columns list (if it is not a identity column).

Code Snippet

declare @.output table (id int, value1 int, value2 int,value3 int)

insert tableone (id,value1, value2, value3)

output inserted.* into @.output

select id,value1, value2, value3 from tableone as source

where id = @.number

select

source.id, outs.id

from

@.output outs join tableone source

on source.id = outs.id

|||

Actually, I don't think that works.

Select source.id, outs.id from ... on source.id = outs.id

Those are always the same?

But it helps.

Getting id values from both source and target rows when duplicating records

Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?

I tried the following, but it doesn't seem to work:

INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @.number

You have to use the following logic, On OUTPUT clause you can’t use other than INSERT & DELETED table values. ID should be available on the columns list (if it is not a identity column).

Code Snippet

declare @.output table (id int, value1 int, value2 int,value3 int)

insert tableone (id,value1, value2, value3)

output inserted.* into @.output

select id,value1, value2, value3 from tableone as source

where id = @.number

select

source.id, outs.id

from

@.output outs join tableone source

on source.id = outs.id

|||

Actually, I don't think that works.

Select source.id, outs.id from ... on source.id = outs.id

Those are always the same?

But it helps.

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't

allows to fire triggers (as I can see). I'm trying to use SSIS to

communicate between two different applications and all logics in my

destination database is based on triggers.

So it failes

WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't

allows to fire triggers (as I can see). I'm trying to use SSIS to

communicate between two different applications and all logics in my

destination database is based on triggers.

So it failes

WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't

allows to fire triggers (as I can see). I'm trying to use SSIS to

communicate between two different applications and all logics in my

destination database is based on triggers.

So it failes

WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim

Getting error in SQL Server Destination

Hi
I have two different database say, DB1 and DB2 one in server A and another one in Server B.
Simply select all rows from DB1.Table1 in OLEDB Source and map it to another table in DB2.Table2 using OLEDB Destination. Works fine.

But if I select destination as SQL Server Destination, I am getting the following error:

[Insert contact information for facility into ARBilling database [202]] Error: An OLE DB error has occurred. Error code: 0x80040E14 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because DTS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

Please let me know why is it throwing error and how to avoid it?

Thanks
Harini

SQL Server Destination only works if the server is on the same box as you are running SSIS.
I reckon this article: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm should mention that but it doesn't. I've fed this back to MS.

-Jamie|||Thankyou Jamie|||

Hi Jamie

I also has the same problem when I am using SQL 2005 SSIS, this is fellowing error message when I tried to run a ssis package.

[SQL Server Destination 1 [201]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I would like to get some advise from you to resolve this problem.

Tim

|||As before Tim, is the SQL Server that you trying to insert to the same machine on which you are running the package? If not, it won't work.

-Jamie|||

Jamie,
We are having the same problem, despite SQL Server being on the same m/c that we are running the package from. The only difference is that the source is an ORACLE database.

Thx,
Shri

|||

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

|||I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?

If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.
|||

VDBA wrote:

Isn't this a step in the wrong direction? Thus far with DTS we have been able to set SQL destinations targetting instances not on the same server. This really seems like a problem with the SQL destination object and not a requirement to run packages on the SQL Instance. Based on my architecture, I may not want to add the workload onto my SQL Server machine, but use a seperate machine. Can we not do that any more?

Eric

Eric,

Of course you can do that - and no, this is NOT a step in the wrong direction.

The default destination adapter for SQL Server (in fact for any OLE DB compliant database) is the "OLE DB Destination" adapter. The "SQL Server Destination" adapter is provided as an extra if you like - one that does highly performant loading into SQL Server.

The downside of using it is that it has a limitation - you can only use it when the package is run on the same server as the database.

-Jamie

|||

Ryan McCauley wrote:

I'm getting the error as described in the original post, with SQL Server 2005. To get around this, should I be using an OLEDB connection to the SQL Server?
If there's not another way to target a remote SQL Server that's different then the one I'm developing on, then it would seem to be a serious problem in the way of using the SQL Server Destination.

Ryan,

This is not a serious problem. This is just the way it works.

Use the OLE DB Destination Adapter.

-Jamie

|||

I see where I had made the mistake now - I had set up database connection using the Connection Manager as an OLEDB, and was still having problems. Then I sawt that the actual data destination objects on my Data Flow diagram were actually "SQL Server Destination" objects. Changing those to "OLE DB Destination" objects fixed the problem up nicely.

Thanks for the help.

|||

this seems to be a major problem to me and a step backwards we regularly used to import flat files and save a dts to do them on a remote machine using enterprise manager... as long as you specify a directory on disk that exists on the other machine ie c:\something

the package was ok to run on the target machine.. in this way we don't have to get directly on the machine to work with flat files and dts packages... because there are limited remote desktop connections this worked very well.. with the new sql 2005 I cannot even do this because it requires some kind of encryption and will not connect to the 2000 machine to save the dts package... ridiculous.. we are internal behind a firewalll I don't need bloody encryption and need a way to disable this.. I also don't want to go around and start generating certs for all our machines and installing them.

again a major step backwards in my opinion.. worry more about OS security and not the security of saving my bloody dts packages.

|||I've faced with the same problem. I run DTS package on SQL Server 2005 machine. Using OLEDB Destination fixed an issue.|||i am having problems in a simple form, i have (2) 2005 db, one i with data and the other blank, and i am trying to do a objects transfer and i set my control to move tables and procs. not thinking i set it to drop objects first. well i cant get it to go pass this error and complete the objects transfer.. what i am i doing wrong ?|||Jamie, it is a real problem in my case. OLE DB Destination doesn't allows to fire triggers (as I can see). I'm trying to use SSIS to communicate between two different applications and all logics in my destination database is based on triggers.
So it failes
WBR, Vadim