Load Data using ADO.NET in SSIS

Hi,

How to extract and Load Data using ADO.NET in SSIS.i hope to extract data we have DataReader source .but how to load (Insert) data with ADO.NET ?.and is ADO.Net quicker than OLEDB ?

Thanks

Jegan.T

Where are you loading it to? SqlServer and OleDB Destinations will be the easiest, and most performant since they will do a bulk insert.|||

Sean,

Thanks for the Informations.But still as a part of our analysis .we need to load in to Oracle Database. can i know how to load data using ado.net .

Thanks

Jegan.T

|||

Jegan,

you can use .NET, Ole DB or ODBC providers to load data in to Oracle Databases, and these are all possible via ADO.NET.

Both Microsoft and Oracle, as well as other 3rd party vendors have Ole DB, .NET and ODBC connectors for Oracle databases. It all depends on your requirements on what version of Oracle and what features you'd like in your connection.

We recommend our customers to use Oracle's OleDB provider, and ODBC providers could be a bit slower, because SSIS uses the .NET-ODBC bridge, not native ODBC connections for data source components.

Another option could be using the native ODBC provider inside the ExecSQL Task, load the recordset, and use it in the data flow task, which could be cumbersome to do.

I think your best bet would be to use Oracle's OleDB provider.

|||Deniz Thanks.

Load data from .DAT file

Hi All,

I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".

Any help will be appreciated.

Thanks.

Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=146250&SiteID=1

Some possible solutions are discussed there.

Thanks,
Loonysan

|||

I've tried those solutions but no luck.

There is another .dat file(much bigger) for different table which works fine.......I have setup the same parameters on both files...one works other fails.

|||have you tried to convert the file to another format using excel?|||Try using the OLE DB stage.|||can the ole db destination handle .dat files? i didn't see any mention of that in the documentation.|||

I found the solution. I had a Format file which I fixed it especially for decimal data types. After fixing the format file it ran pretty smooth and fast :)

Thanks everyone who replied.

|||i have the same problem like you, but i don't have solve, why don't you post your solution,
and how to get data from .dat file using by sql statement
thanks for your help
any supporter can help me, can you show me the detail waysql

Load data from .DAT file

Hi All,

I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".

Any help will be appreciated.

Thanks.

Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=146250&SiteID=1

Some possible solutions are discussed there.

Thanks,
Loonysan

|||

I've tried those solutions but no luck.

There is another .dat file(much bigger) for different table which works fine.......I have setup the same parameters on both files...one works other fails.

|||have you tried to convert the file to another format using excel?|||Try using the OLE DB stage.|||can the ole db destination handle .dat files? i didn't see any mention of that in the documentation.|||

I found the solution. I had a Format file which I fixed it especially for decimal data types. After fixing the format file it ran pretty smooth and fast :)

Thanks everyone who replied.

|||i have the same problem like you, but i don't have solve, why don't you post your solution,
and how to get data from .dat file using by sql statement
thanks for your help
any supporter can help me, can you show me the detail way

Load a Dataset to SQL Mobile (Quickly)

I have five small tables that I need to insert to a SQL CE database.

I am using the 2.0 Compact Framework with the 2.0 System.Data.SqlServerCe.

My table definition is dynamic so I never know it's design.

1- If I go Row by Row using an this.ExecuteNonQuery(_global, par); it takes about 26 seconds to insert 5 tables of 330 rows.

2- If a use

StringBuilder sbColumns = new StringBuilder();

foreach (DataColumn dc in table.Columns)

{

if (sbColumns.ToString() != "")

sbColumns.Append(",");

sbColumns.Append(dc.ColumnName);

}

SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT " + sbColumns.ToString() + " FROM " + _tablename, m_con);

SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);

da.MissingMappingAction = MissingMappingAction.Passthrough;

da.InsertCommand = cb.GetInsertCommand();

da.Update(table);

da.Dispose();

it takes about 46 seconds.

How Can write it faster or is this fastest it can go?

Thanks

You can use SqlCeResultset, which will be the fastet option in .NET. see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.aspx

You can find details in this excellent article by Joao: http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11003&atb.perform(details)=&

little help on delete stored procedure

I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table

Please help!

************************************************** ***
My contrat DDL is follows

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

************************************************** ***
My insert stored procedure is follows

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char],
@.contactId [int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @.contactId = contactId
and @.studentId = studentId)


begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
goSyntactically, it should be:

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char] (4),
@.contactId [int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @.contactId = contactId
and @.studentId = s.studentId)
...

But logically, you're also missing a validity of JUST a student id:

declare @.student_check char(4)
select @.student_check = max(studentid) from student where studentid = @.studentid
if @.student_check is null begin
print 'Invalid studentid specified'
return (1)
end

if exists (select 1 from contact
where @.contactId = contactId
and @.studentId = @.studentId) begin
print 'Specified studentid/contractid already exist in the table!'
return (1)
end
...
After then you can go on with the rest of your procedure.

List box page break

I have a report that I am working on that has a list box. The
properties of this list box are set to "insert a page break at the end
of this list". But it doesn't work. Also, I find that I can't put
"fields" in a page footer. I seem to remember that I can put a text
box in the footer referencing the value of a field in the body of the
report(i.e. the text box in the body references a database field and
the footer text box references the body text box value) Any
suggestions? Thanks in advance. RonI found the answer to both items. I had one field that was set to "can
grow..." I unchecked this item and set the field size to accomodate the text
that would appear. This caused my page breaks to work properly. I also found
the format to refer to a field in the report to wit:
ReportItems!textboxname.value
"Ron" wrote:
> I have a report that I am working on that has a list box. The
> properties of this list box are set to "insert a page break at the end
> of this list". But it doesn't work. Also, I find that I can't put
> "fields" in a page footer. I seem to remember that I can put a text
> box in the footer referencing the value of a field in the body of the
> report(i.e. the text box in the body references a database field and
> the footer text box references the body text box value) Any
> suggestions? Thanks in advance. Ron
>