OLEDB VS SQLServer destination

Hi All,

We want to take advantage of the performance benefit provided by SQL server destination in our packages. We are using a configuration variable to specify whether the SQL Server is remote or local to the packages. We are using a conditional split to redirect the process to either SQL Server destination or OLEDB destination based on the value of the variable. Is there any performance benefit in doing such a thing as it seems that the connection is made in both the paths during the runtime instead of in one particular path alone.

Thanks in advance

Kumbs

It may be opening the connection, but the data is sent to the SQL Server Destination, right? So you should get the benefits. If you really don't want to make the second connection, create two data flows, one with the OLEDB Dest., one with the SQL Server Dest.. In the control flow, put an expression of the constraints leading to the data flow to pick which one to execute.

OLEDB Source to Flat File

Hi,

I'm using an OLEDBSource to select some data and then putting to in a Flat File destination.

However, when I look at the data in the OLEDBSource, they′re like this:

1. id

2. name

3. address

...but in the flatfile it comes out in the wrong order.

How can I fix this?

Thank you so much.

Create the columns in the flat file connection manager in the order you need them to be in.

OleDb Source Error - Connecting to Oracle 9i

Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:

Warning at "guid code": Cannot retreive the column code page info from the oledb provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale Id will be used.

Could anyone please help me trouble shoot this problem.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

Hi,

Click on the OLE DB Source component and check for the properties, you have to keep the property "Alwaysusedefaultcodepage" to true.

Hope this will help you.

Regards,

Raju

|||

Hi Raju,
Thank you very much for your help.
Well, I was not aware of this option and it helped me a lot to finish my work.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

OLEDB performance and Script task

Hi enquiring whether is it more efficient to use OLEDB destination to update and extract all records from table A to tabl e B or use script task? And whether OleDB destination perform row by row update? Finally will data integrity be affected if i run delete, update and insert operations in the same data flow but extracting different sets of data each time from the 2 tables?

Unfortunately the answers to your first question is, it depends... Try testing them out for yourself as the speeds will vary by system and by the transforms that you are trying to push the data through.

OleDB destination will NOT preform a row by row UPDATE. The destination transformation is for inserting new rows, to do the update you will use the OLE DB Command Transformation.

Your data integrity should be fine for doing delete update and insert operations in the same data flow. Of course, this will vary depending on what transformations you do, when, how you get your data, etc... Try watching the Kimball video which is linked to on the front page for a good overview of the various ways of handling update / insert. Also, look at the thread for "check to see if a row exists, if so update, else insert" (also stickied on the front page).

|||

Thanks for the quick reply, but using OLE DB Command from what i understand is too expensive, so comparing OLE DB Command and Script task, script task would be a better choice in this aspect, can i say that at first glance?

|||If you have a large number of updates, you should write those updates to a temporary table and then once the data flow is done, issue an Execute SQL task to perform a batch update. That's the *best* option.|||

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

|||

garynkill23 wrote:

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

Yes

OLEDB Destination question

Situation

I have a package with an execute SQL task that truncates the destination table as the first step in the control flow and a data flow task that reads data from a flat file and loads a sql server table.

Once in a while the package bombs because it cannot get access to the flat file. The end result is that the table is empty because the truncate runs first. Obviously, I need to address the file contention, but I was wondering how to address this issue in general since anything that causes the data flow to blow up would leave the table empty.

I would rather have the table with day old data than empty, since it is not mission critical and the users can at least look at yesterday's data as opposed to nothing.

Question

Is there a way to specify a "load replace" on the OLEDB destination? I haven't seen one and I guess it makes sense because the data flow task transformations run row by row.

The only solution that I have come up with is to have the following on the control flow:

1) data flow task which reads flat file and loads a temp table

2) execute sql task to truncate the "real" destination

3) data flow task to move data from temp table to real table.

Anyone else come up with a better way to handle this?

Thanks!!

I guess from my perspective, I'd never truncate a table as the "first" step of an import process. You're guaranteed to have upset users when an issue that's completely out of your control (meaning, the missing flat file) asserts itself.

I'm using the following logic in a similar application:

1) In a Script Task, I verify the flat file is available, and contains at least one row of data.

2) If the "flat file verification" succeeds, a "Go_NoGo" boolean variable is set to True; otherwise, it remains False

3) An expression (@.[User::Go_NoGo]==True) on the constraint between the Script Task and the next task in the control flow effectively prevents the truncate / replace operation from running if the variable is False.

The code to verify the existance of the flat file is pretty simple:

Private Function GetFileExists(ByVal ImportFile_Path As String) As Boolean
Dim fi As System.IO.FileInfo

Try

fi = New System.IO.FileInfo(ImportFile_Path)

If Not fi.Exists Then
'No point in continuing - cannot find file:
'Discard FileInfo object:
fi = Nothing
Return False
End If

If fi.Length = 0 Then
fi = Nothing
Return False
End If

'Discard FileInfo object:
fi = Nothing

Return True

Catch ex As Exception

Throw ex

End Try

End Function 'GetFileExists

The FileInfo "Length" property tells you how big the flat file is, so it's a pretty good measure of whether or not the flat file contains data.

Obviously, the two If statement can be combined with an Or. In my application I actually check the count of lines in the flat file, and save it to make sure I import all the rows by comparing the line count with the row count in the destination table:

Private Function GetLineCount(ByVal ImportFile_Path As String) As Long
Dim LineCount As Long
Dim sr As System.IO.StreamReader
Dim str As String
Dim i As Long

Try
sr = New System.IO.StreamReader(ImportFile_Path)

Do
str = sr.ReadLine

If Not str Is Nothing Then
If str.Length > 1 Then
LineCount += 1
End If

str = String.Empty

End If

Loop Until str Is Nothing

'Discard StreamReader object:
sr.Close()
sr = Nothing

Return LineCount

Catch ex As Exception

Throw ex

End Try

End Function

I don't run the GetLineCount function until I know the file is there and is more than zero length.

|||

Interesting approach....I may add something similar to my process.

Thanks for taking the time to provide an answer!

Oledb destination commit interval

Hi

How can commit interval for OLE DB destination be set when the data access mode is not "fast load".

What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.

Thanks,

Vipul

Vipul123 wrote:

Hi

How can commit interval for OLE DB destination be set when the data access mode is not "fast load".

What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.

Thanks,

Vipul

"Commit interval" when you're not using fastload is 1. Each row is an independent insert. If there is a failure in the destination, the row can be redirected, you can ignore it, or the component can fail as determined by the error disposition of the component. If you're using transactions, then the rollback is managed by the server using the transaction log, not the oledb destination. Committing a bulk load batch and committing a transaction are not the same thing.
|||

Vipul123 wrote:

Hi

How can commit interval for OLE DB destination be set when the data access mode is not "fast load".

It can't. Use fast load. And why are you against using fast load?

Vipul123 wrote:

What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.

Thanks,

Vipul

When you are not using fast load, your roll back option is limited to ONE row unless you've enrolled the entire data flow in its own transaction (BEGIN TRANSACTION) or are using DTC. When you are not using fast load, if one row fails, that one row gets rolled back, while the others are left untouched -- including future rows depending on how many errors you've configured the package to accept.

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 Source & Excel Destination

Hi,

My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.

Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.

SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.

xlsQuery

CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO

sqlQuery

Exec Report.MyReport 1

Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.

Please Help

Thanks

Shafiq

If the metadata of the sources changes then you cannot use the same data-flow task. Its as simple (or as difficult) as that.

-Jamie

|||

Is it possible to add a conditional splitter in my For Each Loop container to go to different Data-Flow Tasks based on package variable?

Or is there any thing which can refresh the meta data during runtime?

Thanks

Shafiq

|||

shafiqm wrote:

Is it possible to add a conditional splitter in my For Each Loop container to go to different Data-Flow Tasks based on package variable?

Yes, except they're not called conditional splitters. The correct nomenclature is conditional precedence constraints. Loads of good info here: http://www.sqlis.com/default.aspx?306

shafiqm wrote:

Or is there any thing which can refresh the meta data during runtime?

No! Well, actually there is a horrible workaround which involves editing a .dtsx package from another .dtsx package. I have never done it and I certainly never intend to - steer well clear of it.

Using precedence constraints to decide which data-flow to execute is absolutely the right way to go.

HTH

-Jamie

|||

I am going to use the conditional precedence constraints. The next question is do I have to use different OLE DB source / Excel File connection Manager for each data-flow task or they can be changed dynamically.

I was trying to only use one OLE DB and I got error message VS_NEEDSNEWMETADATA

Thanks

|||

You can use the same connection manager across different data-flows and change it dynamically.

You can not use an OLE DB Source component in different data-flows.

-Jamie

|||

It looks like I can't use same Excel File connection Manager as during the design time, If I change the file the mapping of data-flow task then previously defined Data-flow tasks goes wrong and I get the message

Excel desitnation needs VS_NEEDSNEWMETADATA

Note: Each excel file will have different columns depending upon the report

I think same excel file connection manager only work if all the files have the same number of columns

It looks like I am doing an automatic job manually.

Thanks

|||

OK, here's the deal. Once you change the connection manager to point to a file with differrent metadata then of course the data-flow tasks will fail to validate because they are expecting one thing and seeing another (that's what's causing the message you are getting).

Get around this by setting DelayValidation=TRUE on all the data-flows. THis means that they won't get validated until they are executed by which time your connection manager connection string should be set up correctly.

-Jamie

|||

Thanks very much for your prompt response and it help me a lot. One last thing. As I am deleting the excel file and re-creating every time the package runs, Is there way to format the excel file using SSIS

e.g.

Format a column to show 2 decimal places or format as currency

Do any subtotal or run a macro?

I know I did this using ActiveXScript task in SQL 2000, Is there any other way to do this?

Thanks

|||

If Excel has an API (which I assume it does) then I assume you can manipulate it via that API. You would need to ask someone that knows about Excel.

-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 Table/View Drop Down

Is there any way around (or will there be) using the drop-down? It takes several minutes when running against an Oracle Apps database to populate that dropdown with the several hundreds of tables and views.

TIA.

Yes, just write a SQL statement instead. You should be doing this anyway to be honest, selecting from the dropdown is very bad practice.

-Jamie

|||

For an OLE DB Destination? I always write SQL for the sources, but don't quite follow how to do that for a destination.

Thanks for the quick reply!

-Chad

|||

DAMN. You said Destination. Sorry, I should read posts more clearly.

However, you can still do this. Just select 'SQL Command' as your Data Access Mode and your SQL statement just selects all the columns that you want to insert into from the table that you want to insert into.

Ignore what I said about best practice though. its best practice for sources, not destinations.

-Jamie

OLE DB Destination problem - with keywords

Destination table has a column name - Partition that is a key word in the DB2 database. But database would execute the query fine if there is "" around the column name - "Partition". When an OLE DB Destination component is used, it throws an error saying column name is a reserved word and that it cannot be used. When put "" around the column name, it fails in the pre-execute phase thinking that "Partition" is the column name instead of Partition. Any ideas to fix this problem would be greatly appreciated?

What OLE driver are you using for DB2?|||

Phil,

I am using 'IBM OLE DB Provider for DB2'. Any suggestions on what I should do to fix the problem?

|||First, do square brakets work? [Partition]

Second, is your data flow column called Partition, or something else and you're mapping it to a column named Partition? What I'm getting at here, is you can alias the column in the data flow to be something other than "Partition." That might help.

Also, you could try using the Microsoft OLE for DB2 driver.

OLE DB Destination not writing data

I am using OLE DB Destination to write data to a SQL server database. However, nothing is written to the database though there is no error reported. See the following output:

SSIS package "Tbl_Dim_Dates.dtsx" starting.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Tbl_Dim_Dates, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Tbl_Dim_Dates, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Tbl_Dim_Dates, DTS.Pipeline: Execute phase is beginning.

Information: 0x402090DF at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has started.

Information: 0x402090E0 at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has ended.

Information: 0x40043008 at Tbl_Dim_Dates, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Tbl_Dim_Dates, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Date extract to file" (924)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Raw File Destination" (2518)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "OLE DB Destination" (2396)" wrote 3652 rows.

SSIS package "Tbl_Dim_Dates.dtsx" finished: Success.

The program '[2708] Tbl_Dim_Dates.dtsx: DTS' has exited with code 0 (0x0).

this might be a bug. try recreating the data flow.|||

You should use SQL Server Profiler to see what is being sent to the database and what happens within SQL Server. We are pretty much just a client of SQL in such cases, and that's a good to place to start looking for the problem.

Donald

|||

Thanks for all your advices.

ghe

ole db destination not inserting data

my package has a data flow task that is attempting to insert data into a sql server table using the ole db destination. the package validates and runs without reporting any errors, but the data was not inserted into the table. my data mappings look fine. how can i see what's happening when the component attempts to insert the data into the table?

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

|||

Jamie Thomson wrote:

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

here's the info from the output windows:

SSIS package "CaseStudy_Load.dtsx" starting.
Information: 0x4004300A at Data Flow Lockbox Detail Data Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Lockbox Detail Data Task, DTS.Pipeline: The output column "PaymentAmount" (294) on output "Merge Join Output" (268) and component "Merge Join Checks and Invoices" (265) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.
Information: 0x4004300C at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The total number of data rows processed for file "c:\casestudy\lockbox\samplelockbox.txt" is 11.
Information: 0x402090DF at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has ended.
Information: 0x40043009 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "OLE DB Destination Error Log" (326)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to Capture BatchItems" (643)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to store variables" (437)" wrote 0 rows.
SSIS package "CaseStudy_Load.dtsx" finished: Success.
The program '[3448] CaseStudy_Load.dtsx: DTS' has exited with code 0 (0x0).

below is the information written to log.txt:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message|||When debugging in BIDS - what is the number of rows passed to destination (it is displayed on the path leading from a previous transform to the destination while running package in BIDS)?

If the number is 0, no data has reached destination - something might be wrong with tranformations. Try adding data viewers to understand what data flows between transforms.|||ok. thanks. i discovered that the data isn't flowing from the flat file source. i'll make a new thread with a more appropriate subject line so that more people will notice my issue.

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 Component

when loading the transformed data into OLE DB destination, there is no options to truncate destination table first. Have to insert a middle step to run script to truncate the destination table first.

I'm very confused. We even has the options of keeping or deleting the data in destination table in SQL2000 DTS package. Why we don't have this option in SQL2005?

That's correct. You should use an Execute SQL task in the control flow before the data flow containing the OLE DB Destination.|||

Phil,

is there any component in SSIS package that can let us run flexible SQL Script again the input dataset just like the input dataset is a table?

|||

Jeff_LIU wrote:

Phil,

is there any component in SSIS package that can let us run flexible SQL Script again the input dataset just like the input dataset is a table?

Well, in the control flow, you have the Execute SQL task. In the data flow, you have the OLE DB Command transformation, but beware with that one as it will execute the contained SQL for every row in the input data source.|||

those two components can only use the input dataset as parameters, but can't update the data in input data source.

What I want to know is if we can run SQLScript to directly update the columns in input dataset

|||

Jeff_LIU wrote:

those two components can only use the input dataset as parameters, but can't update the data in input data source.

What I want to know is if we can run SQLScript to directly update the columns in input dataset

No. As I said in your other thread, you can update the columns in the data flow via a lookup transformation and a derived column transformation.

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 Destination = temp table

Is it possible to write a dtsx so that the destination of an "OLE DB
Destination" Data Flow Destination is a temp table that is created earlier
in the dtsx ?On Mar 13, 7:05 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> Is it possible to write a dtsx so that the destination of an "OLE DB
> Destination" Data Flow Destination is a temp table that is created earlier
> in the dtsx ?
You may want to repost on the DTS/SSIS message groups. They are:
microsoft.public.sqlserver.dts and
microsoft.public.sqlserver.integrationsvcs. Sorry I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer