One more Beginner Problem

I need to restore multiple tables in a sequence. I am writing separate data flow for each table restore.

If there is any error in any of the table restore data flow, I want to write that error in a log file.

I am writing a script component in every data flow, which will get error code & error description, that i am writing in the log fie.

Is there a way to create a public reusable error logging script file, which we can call from every data flow and log only errors which we want?

Also want to confirm if the way i am doing is correct way or is there any easier/better way to achieve this?

It goes something like this:

Try
'create sqlcommand specifying connection and query
'create reader

reader = sqlcommand.ExecuteReader() <== this throws an "Object reference not set to an instance of an object" exception

'... other stuff here...
Catch ex as Exception
Row.DirectToErrorOutput()
Finally
'dispose sqlcommand and reader here...
End Try

Despite the Catch block, the Script Component still fails, and doesn't get redirected to the ErrorOutput path.

Any ideas on how to solve this?sql

One Date 5 Queries

How can I use one date range (begin / end) for 5 separate queries? I can't
combine the queries into one because I get a timeout. I'm writing queries
against 5 different tables and the results need to be combined.
Any help is appreciated...Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1DDL, sample data, desired output
http://www.aspfaq.com/5006
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6205a8242857e@.uwe...
> How can I use one date range (begin / end) for 5 separate queries? I can't
> combine the queries into one because I get a timeout. I'm writing queries
> against 5 different tables and the results need to be combined.
> Any help is appreciated...Thanks
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Due to security I can't send that info out. I am only trying to figure out
how to use a single point of reference for my date range to support multipl
e
views. I create viewA to add up the sums and I currently hard code the date
range in. I then write viewB to do calculations referencing viewA for the
sums.
I have combined viewA & viewb into viewC and it provides me what I need
(Derivedtbl). I can pass a date range from a Web form to substitue the hard
coding of the date range. I need to link 5 derived tables just like the
aforementioned example (viewA + viewB + ViewC) into one combined derived
table, but I don't know how or if its even possible to pass the date range
from the Web form to all the sub views?
I am probably making this too hard so your expertise is certainly valued.
Chamark wrote:
>How can I use one date range (begin / end) for 5 separate queries? I can't
>combine the queries into one because I get a timeout. I'm writing queries
>against 5 different tables and the results need to be combined.
>Any help is appreciated...Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> Due to security I can't send that info out.
So you can't come up with a frivolous and generic structure of what you want
to do? Ok...|||You can't send DDL, and a small set of illustrative data? We don't need
sensitive data that needs to be protected-only the TYPE of data that you are
dealing with. Probalby not the entire table either, PK, FK, Date fields, and
data to be summed. Seems like not a big issue if it provides you the
opportunity to gain the assistance and expertise you desire.
It sounds like you just may need one View or query - not multiple views.
I do believe that a web form could easily pass date values (start and end)
into a Stored Procedure. And then the Stored Procedure could easily pass the
parameters into several queries if necessary.
There may be many ways to solve your delimma, but we can't even start to
help unless we know what we're working with.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:62060b87788fa@.uwe...
> Due to security I can't send that info out. I am only trying to figure out
> how to use a single point of reference for my date range to support
> multiple
> views. I create viewA to add up the sums and I currently hard code the
> date
> range in. I then write viewB to do calculations referencing viewA for the
> sums.
> I have combined viewA & viewb into viewC and it provides me what I need
> (Derivedtbl). I can pass a date range from a Web form to substitue the
> hard
> coding of the date range. I need to link 5 derived tables just like the
> aforementioned example (viewA + viewB + ViewC) into one combined derived
> table, but I don't know how or if its even possible to pass the date range
> from the Web form to all the sub views?
> I am probably making this too hard so your expertise is certainly valued.
>
> Chamark wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hopefully this will help. How would I send you partial table(s) data?
CREATE TABLE [CSI] (
[Part Surv Id] [float] NULL ,
[Seq C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod Id C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL ,
[#] [float] NULL ,
[Resp Val Id] [float] NULL ,
[Adjusted Weight] [float] NULL ,
[ExtSatWt] [float] NULL ,
[VerSatWt] [float] NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Surv Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [National Call Stats] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep Ssn] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NchQty] [float] NULL ,
[SchdOpenSecsQty] [float] NULL ,
[LogOnSecsQty] [float] NULL ,
[InAdherenceSecsQty] [float] NULL ,
[OutOfAdherenceSecsQty] [float] NULL ,
[HoldSecsQty] [float] NULL ,
[TotalHandleTime] [float] NULL ,
[TalkHoldAvailable] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Call Scores] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [float] NULL
) ON [PRIMARY]
CREATE TABLE [CSI-Disconnect] (
[Date] [smalldatetime] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Call Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [National AR] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN Count] [float] NULL ,
[RTF] [money] NULL ,
[RTC] [money] NULL ,
[CashedOut] [money] NULL
) ON [PRIMARY]
GO
REATE TABLE [OPA] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPA Code] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Opa Seconds Qty] [float] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Arnie Rowland wrote:
>You can't send DDL, and a small set of illustrative data? We don't need
>sensitive data that needs to be protected-only the TYPE of data that you ar
e
>dealing with. Probalby not the entire table either, PK, FK, Date fields, an
d
>data to be summed. Seems like not a big issue if it provides you the
>opportunity to gain the assistance and expertise you desire.
>It sounds like you just may need one View or query - not multiple views.
>I do believe that a web form could easily pass date values (start and end)
>into a Stored Procedure. And then the Stored Procedure could easily pass th
e
>parameters into several queries if necessary.
>There may be many ways to solve your delimma, but we can't even start to
>help unless we know what we're working with.
>
>[quoted text clipped - 19 lines]
Message posted via http://www.webservertalk.com

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 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