One More Question On Running Parallel Queries

An Execute SQL task takes 1 min to run a statement "insert into Mytable select * from view_using_joins"

Output: 10,225 rows affected.

But a Dataflow task configured to fetch data from the same view_using_joins into MyTable takes hours to do the same.

Could you please explain why is it so ?

Thanks

Subhash Subramanyam

I am not surprised that the Execute SQL Task is quicker. When you're inserting from one table to another in the same database then SSIS isn't going to outperform the database engine.

I don't know why it is hours quicker. There isn't really enough information here to say. What destination adapter are you using? Is the package running on the same machine as the database? Are you doing transformations on the way?

-Jamie

|||

Hi Jamie,

Thanks for your reply.

1) I am running packages on a different server.

2) Using OLEDB adapters for Source and Destination . Here Database as well as the server are same for Source and Destination

3) No transformations in between

Wonder if we have to do some extra settings to here to achieve the same duration as that of execute sql task ?

Thanks

Subhash Subramanyam

|||

Have you chosen Fast Load on your destination?

Thanks.

|||

Subhash512525 wrote:

Hi Jamie,

Thanks for your reply.

1) I am running packages on a different server.

2) Using OLEDB adapters for Source and Destination . Here Database as well as the server are same for Source and Destination

3) No transformations in between

Wonder if we have to do some extra settings to here to achieve the same duration as that of execute sql task ?

Thanks

Subhash Subramanyam

You're running the package on a different server? I'd suggest that's yur problem right there. The data is going to have to go over the network - obviously this is going to take time.

Also, in your destination are you inserting with Fast Load?

I say again, in this scenario the data-flow isn't going to outperform the Execute SQL Task.

-Jamie

|||

Great Jamie, you figured out.

If you don't mind spending few minutes here, I am coming back to my actual scenario.

I surely expect specific views from experts here for each of the questions here: Phil B, Rafael S, Darren G, Jwelch, JayH, Scott B, Ashwin S, Brian, Bob, Donald F and many others I am still not aware of.

Scenario:

1) My SSIS Packages are run at US server. scheduleld during Nights.

2) Each Package runs 6-8 queries each having Joins Parallelly pulling data from Oracle Database Source (UNIX) in Europe, Total Data extracted do not exceed 5 Million rows)

3) Destination Database is at US.

4) Network Bandwidth (2 Mbps)

Problem is that It almost takes ages to execute these Packages (Ranging from 25 hours to 30 hours)

Questions are:

1) Where should I expect to run the SSIS Packages to give a better performance?

2) How can I perform only incremental load (using Dataflow task) taking into consideration performance aspects? (Any links for this can help)

3) Does the overlap of the Schedules for SSIS packages afffect the performance?

4) Are there any limits on running number of queries in parallell to pull data from oracle source

5) Will it be the best way, If I spool the query results into flat files on a local system where the source (oracle database) runs at Europe and then ftp them to a shared server at US, which I can use it for importing into Destination table

Waiting for your reply,

Many Thanks and Regards

Subhash Subramanyam

|||Thanks Bob, Please give your answers for my below questions if don't mind.|||The more work you can do to prevent keeping the data transmission "pipe" open, the better.

Perform your source query in Europe, export that to a file, compress it, and then FTP it to the US. Then uncompress it, and load it with SSIS.

The idea is to keep your transmissions across "the pond" as short as possible.|||

From your question #2, I'm assuming you are pulling all rows every night. As Phil mentioned, you want to minimize how much data you are actually moving, so I'd definately make this incremental. A common way to implement that is by checking modified dates on the source system via a WHERE clause in your source SELECT statements. Store the range of modified dates that you retreive, and when the package is run the next night, start from the end of the previous range.

If you don't have modified dates in the source system, consider adding them. Alternatives are using triggers to track changes, or using a change data capture tool - I believe Oracle has one, and SQL Server will have one with SQL Server 2008.

|||

One more question:

6) If I have 6-8 queries running in parallel, Whether having a common connection Manager (for an Oracle source) for all performs better or having Distinct Connection Manager performs better ?

Still expecting suggestions and the views of rest of the experts for six questions listed here.

Regards

Subhash Subramanyam

|||

Subhash512525 wrote:

6) If I have 6-8 queries running in parallel, Whether having a common connection Manager (for an Oracle source) for all performs better or having Distinct Connection Manager performs better ?

It depends Using a single one should result in the same performance as having several, assuming you are not using RetainSameConnection on them. Having a single connection manager doesn't mean that SSIS won't open multiple connections to the database. A Connection Manager manages multiple connections to the database, unless you force it to use only a single connection with RetainSameConnection.

A related note - in your scenario, have you tested whether performance is better if you run all queries sequentially or in parallel (by using precedence constraints on the data flow tasks)?

|||

jwelch wrote:

A related note - in your scenario, have you tested whether performance is better if you run all queries sequentially or in parallel (by using precedence constraints on the data flow tasks)?

Jwelch, This seem more practical. I'll test this and let you know..

Thanks

Subhash

One Data Flow Task and Multiple Data Flows

I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately.

The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible)

Thanks

Hi Karunakaran,

I think the best way to accomplish what you're describing, assuming your destination is a database, would be to use a transansaction. You can cause your entire data flow to be placed in a single distributed transaction by setting the "TransactionOption" property on your Data Flow Task to "Required". This will cause the data flow to attempt to enlist each of the connections it uses into a single transaction.

-David

On Failure WorkFlow not getting Exectuted

i created a DTS package in SQL 2000 using the enterprise manager.

I have defined a SQL task to drop some temporary tables on failure of another SQL task. Also the same temporary tables need to be dropped on success of another task. the on success workflow is working fine. but when i add the on failure workflow to the temporary table dropping task, the temp table droppin is not getting excuted at all either for success nor for the failure. please help me out.

So multiple conditions are pointed to the same failure "workflow"?

If so, change the Multiple constraints option on one of the precedence constraints from the default of "Logical AND" (all constraints must evaluluate to true) to "Logical OR" (only one constraint must evaluate to true).|||Logical OR is great feature in SSIS, but I think the question is about DTS 2000, which did not have this option. In DTS 2000 you need to duplicate error-handling tasks. Or move to SSIS :)|||It worked when i duplicated the error-handling tasks..

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

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 Connection Class in Custom Task?

I have OLE DB Connections set up in my connection manager (Native OLE DB\Microsoft OLE DB Provider for SQL Server). I would like to reference and query these connections from a custom task, written in C#. I currently reference it as follows:

using System.Data.OleDb;

...................................

OleDbConnection connection = (OleDbConnection) connections["MyConnection"].AcquireConnection(null);

What may be obvious to some (though wasn't to me, as I am new at this), when I run the task, I get an error saying that I cannot make this cast. After perusing the boards, I understand that this is because I am not making a cast to the right connection type. Well, that is where I am lost. What connection type (and corresponding library) do I need to reference? I want to continue to use the "Native OLE DB..." connection.

Thanks!

The OleDb connection manager is for tasks and data flow components that use unmanaged OleDb API.
Since you want managed connection object, use ADO.NET connection manager, select the same provider (Native OLE DB\Microsoft OLE DB Provider for SQL Server).

|||Makes sense. I don't know much about connection managers, but this helps. I'll give it a shot. Thanks!

Ole DB type

Is there a reason why Ole DB is missing interger data type? I am using Execute Sql task with Ole DB Connection Manager and cannot find Interger type. Any ideas?Nevermind. I was unfamiliar with the Ole db types for SSIS. Long = integer.

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 - Data Access Mode - SQL Command

I got a package with data flow task. Within the data flow task I have flat file with Fiscal Calendar defined. I got another data source within the data flow task, which is OLE DB Source. I want to use SQL Command as data access mode. SQL similar to one the below is in there.

-

DECLARE @.startdate DATETIME
DECLARE @.enddate DATETIME
DECLARE @.date DATETIME
DECLARE @.id INT

SET @.startdate = '1993-09-26' --Change these to
SET @.enddate = '2010-09-25' --whatever you want
SET @.id = 1
SET @.date = DATEADD(dd, @.id, @.startdate)

WHILE @.date <= @.enddate
BEGIN
select @.date CalendarDate,
DATEPART(dd, @.date) CalendarDayMonth,
DATEPART(dy, @.date) CalendarDayYear,
DATEPART(dw, @.date) CalendarDayWeek,
DATENAME(dw, @.date) CalendarDayName

SET @.id = @.id + 1
SET @.date = DATEADD(dd, @.id, @.startdate)

END

-

This SQL works fine in SSMS and returns around 6000 rows. But when I plug the same SQL in OLE DB Source it returns only the first record. It is not going through the WHILE loop.

Has anyone came across this?

Thanks

Sutha

Hey,

OLEDB Source connection to what database?

Brian

|||

I am connecting to my warehouse DB but the source is just the SQL, it doesn't need to extract anything from DB, as the SQL should give the result set.

What I should ideally use is "Execute SQL Task", which is in Control Flow Task.

Maybe I could achieve this by putting into a temp table and source it from the temp table. I am going to check it out.

Thanks

Sutha

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 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 Command not updating

I have a data flow task, and in that is an OLE DB Source that upon success, connects to an OLE DB Command. The OLE DB Source runs a sql command from a variable. (I've tested the sql; it parses and returns values.) The OLE DB Command is a very simple update sql statement with one column mapping. When I execute the task, it says it is successful, but when I check the table, nothing has been updated. I have ensured the connection is successful, and I am connecting to the correct db. Any suggestions would be helpful! Thanks!

Use SQL Profiler to capture the SQL statements that are being sent to the database. Odds are there is something related to the parameter mapping that is preventing the command from doing what you need it to.

Another thing to check is to ensure that the commands are being executed against the database that you think they are. If you have multiple connection managers, this can be easy to do.

|||

I found the problem. The variable used in the OLE DB source I thought I had changed to call a new stored procedure. However, its value was not actually changing and it was calling the wrong procedure, returning no values. My next question is why when I change the value in the properties window, does the variable not take the change?

|||Is the variables EvaluateAsExpression property set to true? This would cause that behavior.|||

Lindsay wrote:

I found the problem. The variable used in the OLE DB source I thought I had changed to call a new stored procedure. However, its value was not actually changing and it was calling the wrong procedure, returning no values. My next question is why when I change the value in the properties window, does the variable not take the change?

Did SQL Profiler end up being useful?

|||

Phil, yes the EvaluateAsExpression property was set to True; how embarassing LOL. Thank you!!

|||

Matthew, I've never used SQL Profiler before today, and am not familiar with it. I must need to modify the trace, because it has been running for a few hours now (actually I totally forgot about it)!!

|||

Lindsay wrote:

Matthew, I've never used SQL Profiler before today, and am not familiar with it. I must need to modify the trace, because it has been running for a few hours now (actually I totally forgot about it)!!

Oh no! It's probably best to just stop the trace at this point.

I'm sorry - I didn't mean to make things more complex. If you start the trace right before you run the package, and then stop it right after the package completes, you can then look through a (relatively) small set of queries sent to the server. In t his context, one of them should leap out because it the same query repeating over and over again. There are many ways to filter the data before and after it's recorded, but this is often the quickest and easiest way to see what the client application (in this case, SSIS) is REALLY sending to the server.

OLE DB Command and Destination writing to the same table

Hi,

I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.

The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.

Could this be due to a table-locking issue? Any suggestions?

Thanks
ray

Just to confirm you haven't set the table lock check box on the oledb destination?

|||

You might look at the slowly changing dimension task... I'm not that familiar witrh it.

A quick and easy solution would send one of the outputs, probably the updates to a raw file destination. Then read that raw file in a different data flow task and perform the update there.

Doing a lot of updates in a sqlCommand - row by row - can be time consuming and I usually try to find another way to do it using some kind of set based operation. For example, if I can identify unique properties of from the source data, a date range or a specific field value; I would use that to construct a sqlCommand that would update all of the records at one time.

|||

Here is 2 links to pages that talk about different techniques for doing an "upsert". This is the

first time I have heard that term. I like it!

This is my web site:
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

This is Jamie Thompson's cool

blog:
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks,

Greg Van

Mullem

|||

rlee1003 wrote:

Hi,

I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.

The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.

Could this be due to a table-locking issue? Any suggestions?

Thanks
ray

Yes, this is absolutely a locking (actually a blocking) issue. Execute sp_lock or sp_who2 to confirm it.

You can get around this problem by executing the insert and update operations in different data-flows. Use a raw file to pass data from one data-flow to another. This technique is covered (albeit for use in a differrent context) here:

Splitting order detail and order header information from one file into multiple tables
(http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx)

-Jamie

|||Oho, I get the question now. My previous reply was useful but a little off topic. Are you use the "Fast Load" option in your OLE DB Destination? Turning this off might help.

You could also uses 2 "OLE DB Command" components (running insert and update commands). That's what I'm doing so I know it works quite well.

Thanks,
Greg

OLE DB - ADO .NET

Does anybody know why in some cases you can use both connection types, in others you can't. Like for example, you start a data flow task with a Datareader Source which uses an ADO .NET connection manager, next you want to do a lookup and seems like the only type of connections you can use in a lookup table is the OLE DB.

So OLE DB is placed hard coded in a lookup task, and I thought things changed since the coming of OOP ...

I think ole db is ok

just when i use script component,i use ado.net

|||Your observation is correct -- Lookup currently only supports OLE DB connections. We hope to add ADO.NET connection support in the future.|||Ok