On error i want to send error description as mail

I have create a SSIS package, for data export import process, but if my task get failed then i have to send a mail with proper error description as the SSIS generate in output window.

Can any one sugget me to, how can i store that error desciption in my variable.?

Thanks in advance.

You can create a table with the following fields packageid,packagename,taskname,errormessage.

And later write a stored procedure which will insert the data into this table from the package variables. On event handler tab call this stored procedure and pass the system variables "system::executioninstanceguid","system::packagename","system::sourcename","system::errordescription".

Once this is done you can read values from this particular table and assign it to a variable and later on call it in the send mail task.Hope this will help you.

|||

Use OnError Event in Event Handler and system variables such as [System::ErrorCode] and [System::ErrorDescription]

http://msdn2.microsoft.com/en-us/library/ms139744.aspx

|||

As you define above i'm doing such a way

dts.variable("User::vName").value = dts.variable("System::ErrorDescription")

but it is going me error in my script task.

:-(

please provide more specific detail or information.

|||

Hi..

1. In OnError Event Handler, Add a Script Task..

2. Define ReadWriteVariables value in ScriptTask Properties like this

ReadWriteVariables | User::vName,System::ErrorDescription

3. In VSA, edit your script like this..

Dts.Variables("vName").Value = Dts.Variables("ErrorDescription").Value.ToString

HTH

ADConsulting / SQLLeader.com / Daeseong Han

|||

Thanks for you needful help.

:-)

sql

On demand SSIS Agent Job Updates

Afternoon all,

This should be brief, hopefully. Every morning all our SSIS updates run on a schedule(s) by SSMS Agent Jobs. Most of the time this is acceptable, however, during the month end accounting process it would be desirable for accounts to be able to run one of the jobs on demand.

Is there a way that this can be done without having to install and use SQL Server Management Studio on account machines? The potential damage that can be done, even inadvertently, with this utility is too great to even consider giving them this package.

Thanks in advance,

Paul

sp_start_job - Write a small program to call that procedure to kick off the sql agent job.

OLEDB command in SSIS

I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.The OLE DB command executes for every row in the data flow. Are you sure you're stating the correct component?

You should use an OLE DB Source component hooked to an OLE DB Destination component to move your data.|||I want to delete some data while transfering from one to another i.e i want to do cleaning.|||If you want this to be fast, try writing the keys for the rows to delete to a temporary table, then use an Execute SQL task to issue a batch DELETE statement. Since the OLEDB Command transform executes once for each row that passes through it, it will be slower than doing a batch.

OLE DB Source Editor

hi,

I am using SSIS to extract data from sql server and import into MDB file. In
the process, under data flow task, I have used OLE DB Source Editor as source. Here
i have choosen SQL Command as mode of data population. In the box below i
have typed the following statements.

"Exec Site_Address"

I have used many temperory tables in this procedure.
When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i
click the button preview i can see the data. But when i click the
Columns.... i dont see anything there. I am unable to see any columns there.
This is getting to my nerves because, when i use OLE DB as Destination i am
unable to map the columns and i get an error.

I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ?

Please help me to find a solution.

I will also paste the procedure code that i have used.

Create procedure Site_Address

as

begin

create table #Data_For_Site_Address_Table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(20),

CreatedBy varchar(20)

)

-- tblcrdsiteaddress

insert into #Data_For_Site_Address_Table

select distinct * from

(select

(select top 1 fsu.ser_num

from fs_unit fsu

where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num

order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,

CASE

WHEN ca.country like 'Luxembourg' THEN 'LU'

WHEN ca.country like 'Deutschland' THEN 'DE'

WHEN ca.country like 'Austria' THEN 'AT'

WHEN ca.country like 'Czech Republic' THEN 'CZ'

WHEN ca.country like 'Denmark' THEN 'DK'

WHEN ca.country like 'CHINA' THEN 'CN'

WHEN ca.country like 'CROATIA' THEN 'HR'

WHEN ca.country like 'Egypt' THEN 'EG'

WHEN ca.country like 'Germany' THEN 'DE'

WHEN ca.country like 'Hungary' THEN 'HU'

WHEN ca.country like 'Jordan' THEN 'JO'

WHEN ca.country like 'Korea, Republic Of' THEN 'KR'

WHEN ca.country like 'Poland' THEN 'PL'

WHEN ca.country like 'Switzerland' THEN 'CH'

WHEN ca.country like 'United Kingdom' THEN 'GB'

ELSE '- N/A -' END AS CountryID, CA.CreatedBy

from custaddr ca

) al

where unitid is not null

Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,

TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]

From #Data_For_Site_Address_Table TT

END

Thanks.

Rgds,
Meher Krishna.V

I just did a quick search in the forum and found a couple threads dealing with SP being used inside of an OLE DB Source:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ole+db+source+procedure&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

This, particularly, seems to talk about same issue:

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

I hope it helps

|||

Hopefully this should explain the problem:

Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)

-Jamie

|||

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

|||

Rafael Salas wrote:

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

Well it wasn't at the above link cos I only wrote it about an hour ago

-Jamie

|||So, probably I dreamed about it. |||

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

|||

meher666 wrote:

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

No, in some circumstances I just don't think it can be done. Where did I suggest SET FMTONLY?

-Jamie

|||

okieee.... i am sorry again.... i thought it was you suggested using that statement. It was somebody else.

Thanks for your help.

OLE DB Source and OLE DB Destination components: how to do without the datasourc

Hello all,

I'm implementing an application that builds SSIS package. I've faced the problem I don't know what to do about it.

In a simple case, I build a dataflow which has OLE DB Source and OLE DB Destination component. The problem is, that at the moment I build the dataflow I don't have source and target databases, i.e. datasources. A source database is accessed and a target database is created later, and after that the package is run.

In this situation connection strings for source and destination sources are populated via variables, but what to do about columns in the source/destination which at the moment the package is created cannot be retrieved from data sources. When designing, they are taken from respective datasources, custom columns, not from a datasource, cannot be added.

I don't know how to populate OLE DB Source and OLE DB Destination components with columns, not from datasources, but with ones from not existing databases yet, which will exist when the package is executed. In what way can I do that?

Thanks in advance,

sash pSorry if my question is easy, but I'm so new to SSIS programming. I've just looked around and noticed "OpenRowset Using FastLoad From Variable" option for the OLE DB Source/Destination. Of course at the design time I need to specify datasources but I can specify a table name in a variable. At a run time, when programmatically building a package, I only need variables for connection strings and source/destination tables. And then that will work. Correct me please if I'm wrong.

sash p

OLE DB Provider - Saving SQL UserName and Password error

I have created an SSIS package, which uses an IBM OLE DB Provider for DB2 to access some data. In this case it is not possible to use

Windows NT Integrated Security, so therefore I stored the username and password in the connection.

It works fine for the current user, but if I log on to Windows with another user and try to change the OLE DB connection I recive the following error:

Error 1 Error loading BaliAdslUge.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "The key is not valid" (in danish "N?glen er ikke gyldig for anvendelse i den angivne tilstand.").

You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:\_data\projects\baliadsl\integrationservices\BaliAdslUge.dtsx 1 1

Error 2 Validation error. BaliAdsl: OLE DB Source [3075]: The AcquireConnection method call to the connection manager "Pmarco.f00337" failed with error code 0xC0202009. BaliAdslUge.dtsx 0 0

Best regards,

Ejnar Jakobsgaard

TDC

Ejnar,

By default we encrypt sensitive information with the user key. You need to change this to encrypt with password if you want multiple users to be able to decrypt it.

Thanks,|||Hi Matt,

Could you provide some further information on this issue?
How do I encrypted with password, so that several users can use this connection?

What is your advice, if I cannot use Thrusted connection and the user developing the package and running them - Is not the same user?

Best regards,

Ejnar|||Hi Matt,

Thanks :-)

In found some info. in online help at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d4b073c4-4238-41fc-a258-4e114216e185.htm

Regards,

Jakobsgaard

OLE DB for DB2 Built on Dev run on Standard

I have built a simple package using the Microsoft OLE DB provider for DB2 with SSIS Developer. If I schedule this on a Standard SQL2005 machine the package will not run. The error is blank, just says it had an error. Can I not run this package because the provider is not installed on this machine? If so is there a work around for this?you probably have to run your package on the box that has DB2OLEDB physically installed.|||

I believe that the ole db for DB2 has to be used on enterprise edition (if i read the licesne right from the ssis extension site). I am not sure if the ms one works very well. The IBM one sucked very badly when dealing with decimals. Even they recommended not using it (IBM) as part of the Client access express in their redbooks. If you are not going to fork out the extra few thousand bucks for enterprise, try the ODBC connect from DataDirect. It is a bazillion times faster than the CLAE ODBC, and i could actually get it to work (on db2 400).

Dev edition has all rights/ features as enterprise.

OLE DB Error in SSIS

Hi All,

We are using IBM iseries OLE DB provider and once we make a source connection using the same we get an error saying that the "Default Code Page" property is not defined and a default code page will be used.

Any ideas?

Thanks

Manish

Set AlwaysUseDefaultCodePage=TRUE on the source component.

-Jamie

|||

Thanks Jamie.

Tried it out but it also does not seem to work, though there are no validation warnings, the job runs and give a Pipeline PrimeOutput Error()

|||

That's probably a different error. i.e. Related to something else.

-Jamie

|||

I still feel its an OLE DB error because it starts with an error saying OLE db error with a hexadecimal value.

Any ideas?

|||

Yes, that was my point. i.e. The default code page is not the problem.

I personally have no experience of extracting from these systems.

You may want to try the data access forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

OLE Db Destination task in ASYNC_NETWORK_IO wait state

I have established an SSIS dataflow that should move 20,000 records from a source table to a target table. Only new records should be added to the target table – existing records should be ignored. The problem I am reporting occurs when the target table is initially empty (there are no existing records, so everything should come over). I am using the Slowly Changing Dimension task to limit inserts to new records. The tasks in my data flow are:

OLE DB Source è Slowly Changing Dimension è Derived Column è OLE DB Destination

The problem is that the data flow locks up before completing. This occurs if the Data Access Mode of the OLE DB Destination task is “Table or view – fast load”. If I look at the Activity Monitor in SQL Server Management Studio once it has locked up, I can see two processes in the suspended state. Based on the queries, I can identify one of the processes as performing the lookup for the Slowly Changing Dimension task. The other represents the BULK INSERT associated with the OLE DB Destination task.

The Slowly Changing Dimension task is locked waiting for the OLE DB Destination task as determined by looking at the “Blocked By” column in the Activity Monitor. Its wait state is LCK_M_S. The OLE DB Destination task has a wait type of ASYNC_NETWORK_IO.

QUESTIONS: Why is the BULK INSERT in the OLE DB Destination task waiting? What does ASYNC_NETWORK_IO wait state indicate? How do I prevent this from happening?

I am running the SSIS package in SQL Business Intelligence Studio on a workstation against a SQL Server 2005 server. The same situation is seen when I run the package directly on the SQL Server 2005 systems in SQL Business Intelligence Studio rather than on my workstation.

Turn off the table lock option for the OLE-DB Destination. Leaving this on means that the second buffer of data is blocked, specifically the lookup, as when the first passed to the destination the lock was acquired, and now prevents any more lookups happening.

You could also investigate adding a NOLOCK hit to the lookup SQL statement.

|||I had already tried turning off the table lock option, though I failed to mention it. Adding the NOLOCK option onto the query within the Slowly Changing Dimension task did indeed allow the data flow to complete.

Thank you.

OLE DB Destination fails with illegal instruction

Environment: Server Windows 2003 SP1, VS 2005

I ran into this problem trying to deploy an SSIS package to a development server. I tested by creating a simple SSIS package on the server itself. Two blocks an OLE DB Source block and an OLE DB Destination block. Two tables in the same database, one the source, the other the destination. Connection manager test connection works fine.

Package will execute from the IDE (locally on the development server), source block will read the table subcessfully, but when the destination block executes it fails and will stay yellow in the status screen. during the execution SQLDumper.exe is triggered. Analyzing the dump tells me:

(2b40.2938): Illegal instruction - code c000001d (first/second chance not available)
eax=04008010 ebx=00000004 ecx=00000010 edx=00000000 esi=00000940 edi=00000000
eip=7c82ed54 esp=0422f598 ebp=0422f608 iopl=0 nv up ei ng nz ac po cy
cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000297

An illegal instruction was hit.

This error exists when the destination block is either an OLE DB Destinaltion or a SQL Server Destination block and the SSIS application is executing locally.

Strangely enough if I run this same SSIS application from my development laptop it works fine against either my local datbase instance (on the laptop) or against the development server (App running on laptop, DB on development server).

I have scanned the forums and search engines for this type of error, so any assistance would be appreciated.

Thanks...

do you have logging enabled? if so, please post the exact ssis error message.|||

Date 8/7/2006 12:48:57 PM
Log Windows NT (Application)

Source SQLDUMPER
Category (0)
Event 5000
Computer SPT-TEST01

Message
EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.1399.0, P3 434f5df1, P4 dtspipeline.dll, P5 2005.90.1399.0, P6 434f5dbc, P7 0, P8 0004ba38, P9 00000000, P10 NIL.

SSIS itself displays no error information except for the block turning yellow.....

|||

I am getting the same problem:

On a new development server installation Win 2003 R2 and MSDN SQL2005 Developer edition & SP1 (was a clean install) when using DTSWizard or a SSIS package in VS2005 on the server to transfer data onto a sql table (either from another table or flat file) I get a fatal error "Unhandled win 32 exception" - looking in the event log I get

Faulting application dtswizard.exe, version 9.0.2047.0, stamp 443f5b0a, faulting module dtspipeline.dll, version 2005.90.2047.0, stamp 443f5a9c, debug? 0, fault address 0x0004c258.

VS2005 on the server is

Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

On my developer desktop I have VS2005 as

Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

and I can build a project on this which runs ok from the desktop

I do get version difference alerts if I try & load a SSIS project on the server when I initially made it on the desktop

Any ideas?

regards

|||

lwulfers wrote:

Date 8/7/2006 12:48:57 PM
Log Windows NT (Application)

Source SQLDUMPER
Category (0)
Event 5000
Computer SPT-TEST01

Message
EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.1399.0, P3 434f5df1, P4 dtspipeline.dll, P5 2005.90.1399.0, P6 434f5dbc, P7 0, P8 0004ba38, P9 00000000, P10 NIL.

SSIS itself displays no error information except for the block turning yellow.....

again, did you turn on ssis logging?

OLE DB Destination fails with illegal instruction

Environment: Server Windows 2003 SP1, VS 2005

I ran into this problem trying to deploy an SSIS package to a development server. I tested by creating a simple SSIS package on the server itself. Two blocks an OLE DB Source block and an OLE DB Destination block. Two tables in the same database, one the source, the other the destination. Connection manager test connection works fine.

Package will execute from the IDE (locally on the development server), source block will read the table subcessfully, but when the destination block executes it fails and will stay yellow in the status screen. during the execution SQLDumper.exe is triggered. Analyzing the dump tells me:

(2b40.2938): Illegal instruction - code c000001d (first/second chance not available)
eax=04008010 ebx=00000004 ecx=00000010 edx=00000000 esi=00000940 edi=00000000
eip=7c82ed54 esp=0422f598 ebp=0422f608 iopl=0 nv up ei ng nz ac po cy
cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000297

An illegal instruction was hit.

This error exists when the destination block is either an OLE DB Destinaltion or a SQL Server Destination block and the SSIS application is executing locally.

Strangely enough if I run this same SSIS application from my development laptop it works fine against either my local datbase instance (on the laptop) or against the development server (App running on laptop, DB on development server).

I have scanned the forums and search engines for this type of error, so any assistance would be appreciated.

Thanks...

do you have logging enabled? if so, please post the exact ssis error message.|||

Date 8/7/2006 12:48:57 PM
Log Windows NT (Application)

Source SQLDUMPER
Category (0)
Event 5000
Computer SPT-TEST01

Message
EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.1399.0, P3 434f5df1, P4 dtspipeline.dll, P5 2005.90.1399.0, P6 434f5dbc, P7 0, P8 0004ba38, P9 00000000, P10 NIL.

SSIS itself displays no error information except for the block turning yellow.....

|||

I am getting the same problem:

On a new development server installation Win 2003 R2 and MSDN SQL2005 Developer edition & SP1 (was a clean install) when using DTSWizard or a SSIS package in VS2005 on the server to transfer data onto a sql table (either from another table or flat file) I get a fatal error "Unhandled win 32 exception" - looking in the event log I get

Faulting application dtswizard.exe, version 9.0.2047.0, stamp 443f5b0a, faulting module dtspipeline.dll, version 2005.90.2047.0, stamp 443f5a9c, debug? 0, fault address 0x0004c258.

VS2005 on the server is

Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

On my developer desktop I have VS2005 as

Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

and I can build a project on this which runs ok from the desktop

I do get version difference alerts if I try & load a SSIS project on the server when I initially made it on the desktop

Any ideas?

regards

|||

lwulfers wrote:

Date 8/7/2006 12:48:57 PM
Log Windows NT (Application)

Source SQLDUMPER
Category (0)
Event 5000
Computer SPT-TEST01

Message
EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.1399.0, P3 434f5df1, P4 dtspipeline.dll, P5 2005.90.1399.0, P6 434f5dbc, P7 0, P8 0004ba38, P9 00000000, P10 NIL.

SSIS itself displays no error information except for the block turning yellow.....

again, did you turn on ssis logging?

OLE DB Destination fails with illegal instruction

Environment: Server Windows 2003 SP1, VS 2005

I ran into this problem trying to deploy an SSIS package to a development server. I tested by creating a simple SSIS package on the server itself. Two blocks an OLE DB Source block and an OLE DB Destination block. Two tables in the same database, one the source, the other the destination. Connection manager test connection works fine.

Package will execute from the IDE (locally on the development server), source block will read the table subcessfully, but when the destination block executes it fails and will stay yellow in the status screen. during the execution SQLDumper.exe is triggered. Analyzing the dump tells me:

(2b40.2938): Illegal instruction - code c000001d (first/second chance not available)
eax=04008010 ebx=00000004 ecx=00000010 edx=00000000 esi=00000940 edi=00000000
eip=7c82ed54 esp=0422f598 ebp=0422f608 iopl=0 nv up ei ng nz ac po cy
cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000297

An illegal instruction was hit.

This error exists when the destination block is either an OLE DB Destinaltion or a SQL Server Destination block and the SSIS application is executing locally.

Strangely enough if I run this same SSIS application from my development laptop it works fine against either my local datbase instance (on the laptop) or against the development server (App running on laptop, DB on development server).

I have scanned the forums and search engines for this type of error, so any assistance would be appreciated.

Thanks...

do you have logging enabled? if so, please post the exact ssis error message.|||

Date 8/7/2006 12:48:57 PM
Log Windows NT (Application)

Source SQLDUMPER
Category (0)
Event 5000
Computer SPT-TEST01

Message
EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.1399.0, P3 434f5df1, P4 dtspipeline.dll, P5 2005.90.1399.0, P6 434f5dbc, P7 0, P8 0004ba38, P9 00000000, P10 NIL.

SSIS itself displays no error information except for the block turning yellow.....

|||

I am getting the same problem:

On a new development server installation Win 2003 R2 and MSDN SQL2005 Developer edition & SP1 (was a clean install) when using DTSWizard or a SSIS package in VS2005 on the server to transfer data onto a sql table (either from another table or flat file) I get a fatal error "Unhandled win 32 exception" - looking in the event log I get

Faulting application dtswizard.exe, version 9.0.2047.0, stamp 443f5b0a, faulting module dtspipeline.dll, version 2005.90.2047.0, stamp 443f5a9c, debug? 0, fault address 0x0004c258.

VS2005 on the server is

Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

On my developer desktop I have VS2005 as

Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

and I can build a project on this which runs ok from the desktop

I do get version difference alerts if I try & load a SSIS project on the server when I initially made it on the desktop

Any ideas?

regards

|||

lwulfers wrote:

Date 8/7/2006 12:48:57 PM
Log Windows NT (Application)

Source SQLDUMPER
Category (0)
Event 5000
Computer SPT-TEST01

Message
EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.1399.0, P3 434f5df1, P4 dtspipeline.dll, P5 2005.90.1399.0, P6 434f5dbc, P7 0, P8 0004ba38, P9 00000000, P10 NIL.

SSIS itself displays no error information except for the block turning yellow.....

again, did you turn on ssis logging?

'OLE DB Destination' failed validation error

Hi,

I'm developing a SSIS package and am coming across this problem:

"Error at myTable [DTS.Pipeline]: component 'OLE DB Destination' (156) failed validation and returned error code 0xC020801C.

Here's the situation with my package. It basically consists of a bunch of tasks to build a database and populate the tables from an Excel file.

The first task runs a SQL file to create all my DB tables, etc. The next set of tasks import data from the Excel file into the tables. All tasks have precedence constraints so that the SQL file will build the DB tables first.

Here's the problem. Everything works perfectly if my database ALREADY exists. The problem comes when the database does not exist. It seems like the SSIS does some sort of schema validation on my dataflow tasks to ensure that everything is ok.

Is there any way to bypass this validation so that my SSIS will run ok without the DB already existing? I know that everything WILL be ok because the ExecuteSQL task will create the structure for me.

I used to work with SQL 2000 DTS, and i never ran into this problem before. Previously, i could run the DTS no problem, however, i just couldn't open the transformations if the DB didn't exist.

Thanks.

Set DelayValidation=True on the Data Flow.

OLE DB DESTINATION and SQL Server Destination

Hey All:

I was totally confused.

When designing the SSIS dataflow part, firstly , i tried SQL Server Destination because my target server is a sql server.

then execute the task with failure.

Then i tried to use OLE DB DESTINATION instead of SQL Server Destination.

This Dataflow worked.

i can not figour out why.

By the way , i used the connection is OLE DB.And i choosed OLE DB source as the datasource cuz i can not find SQL server datasource.

Who can tell me some reasons for this?

Have you searched Books On-Line?

The SQL Server destination requires that you have SQL Server running on the same machine that you are executing the package on. Also, just to have SQL Server running on the machine isn't enough; it has to be your destination. The SQL Server destination is an in-memory operation, essentially.

The OLE DB Destination isn't bound to those constraints.

|||

Using the OLE-DB destination is fine, and is probably the most common choice. Obviously you will then use an OLE-DB connection, selecting the OLE-DB provider for SQL Server. That is all good, don't worry.

You have however and advanced choice with the SQL Server Destination. It has options that make it the faster than OLE-DB, and one way they do this is by using the shared memory connection method. As you might guess from the name it means that the SSIS package pipeline and the SQL Server must be on the same machine. This makes for hard work when you wish to develop against a server running on a different machine to the development tools. For this reason I generally avoid it, unless I am really concerned with insert performance, and more often than not the bottle neck is elsewhere so the SQL Server Destination is over kill anyway.

|||

Many thanks

But the new issue is that dam slow~~

60,000 rows from a static table which server loactes in Germany to the US server costs over 30 minutes.

even worse than <select .. openquery()>

why?

|||

Solved!

i used a txt flatfile as a buffer intermedia.

|||If the "buffer" is between two SSIS tasks or packages, then use a raw file. Raw files are faster to read and write than txt files, as they are the pipeline engine's buffer structures from memory straight onto disk, without any translation or interpretation. See teh Raw File Source & Destination.|||

DarrenSQLIS :

Rock~

OLE DB DataSource w\ stored procedure not populating column metadata

I'm having some issues getting OLE DB Data Sources to work w\ stored procs in SSIS. Here's the situation.

I have an OLE DB Data Source set up to call a stored proc w\ no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.

I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.

Any help would be much appreciated....

I believe that there are known problems around using SPs in OLE DB Source components. Its something to do with requiring that the query that returns the data is the first statement in the proc. This enables SSIS to understand the metadata of the returned result set.

I know this doesn't help you in your case though. I do not know if there is a workaround or not having never faced this problem before myself. I hope what I've said above goes some way to helping though.

-Jamie

|||I think OLE DB Command will execute the Store proc|||Try converting the sp to a multi-statement table-valued function, giving Data Access Mode for your OLE DB source as SQL Statement and specifying the statement SELECT * FROM your_table_valued_function(); the wizard will gussy the SQL up

OLE DB Command Error SSIS

Hi,

when we are using OLEDB Command we are getting the following error?

when we use sql server as the source its working fine .but its throwing error when we try to connect the orcale database .

Error at Data FLOW TASk[OLE DB Command[3863]]: An OLE DB error has occured :0x80040E51.

An OLE DB record is available. Source : "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51

Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

ERrror at Data Flow Task [OLE DB Command [3863]]: Unable to retrieve destination column description from the Parameter of the SQL Command

Thanks & Regards

Jegan.T

This is an OLE DB Error. My first suggestion would be to try Oracle's own driver instead of the Microsoft one.

-Jamie

|||

Hi Jamie,

Do we need to install orcale driver separtely or its comes with SSIS ?

Thanks

Jegan.T

|||

Seperately. It comes with Oracle.

|||

Hi Jamie

Thanks for the suggestion . i'll give a try and get back to you..

Jegan.T