Having problems with variables

I have a Execute Process Task that pretty much executes a batch file that downloads a file that has a dynamic file name (with datetime stamp). Now I would like to load this file to a Flat File Source task in the Data Flow Task section automatically. So, creating a file manager or something on the fly. Is something like this possible with SSIS? Or am I simply hitting the wall here?

Thank you.

Why can you not just drop a data-flow into your package and make sure it executes after the Execute process task?

-Jamie

Having problems using Breakpoint in Script Task.

I am still pretty new to SSIS, SQL, and DOT NET. I came from the UNIX world. I have a SSIS package. On the Control Flow one of the Items I have is a Script Task. I was able to successfully set breakpoints in the Script Task and they worked fine.I could step through the script and check values in variables.Life was good.

Now something happened.I set the breakpoint, from the menu I select “start with debugging” and I get the following window:

-- -

Visual Studio Just-In-Time Debugger

An unhandled exception (‘System.Runtime.InteropServices.COMException’) occurred in DTAttach.exe [3380].

Possible Debuggers:

New instance of Microsoft CLR Debugger 2003

New instance of Visual Studio .NET 2003

New instance of Visual Studio 2005

[_] set the currently selected debugger as the default

[_] Manually choose the debugging engines

Do you want to debug using the selected debugger?

--

I have tried selecting Yes, but that doesn’t work.If I delete all breakpoints the package runs fine.I greatly appreciate your help.

You are not alone.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1846879&SiteID=1|||Thanks Phil. I found a work around for my problems and posted it on the thread you provided the link to above.

Handling variables

I have a script task that has a ReadWriteVariable = FilePath. I am doing the following below to calculate the FilePath variable. Instead of the output being C:\Rapagator\Downloaded\RETS_Search_ResidentialProperty_20060403094343.txt, it just shows RETS_Search_ResidentialProperty_20060403094343.txt. Any ideas how to resolve this problem?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Public GetFilePath As String = ""

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
GetFilePath = "C:\Rapagator\Downloaded" + Row.FileName
End Sub
Public Overrides Sub PostExecute()
Variables.FilePath = GetFilePath
MyBase.PostExecute()
End Sub

End Class

How do you know the variable value is only the filename part you state? You cannot debug inside the Script Component, so perhaps add a MessageBox or log an event to write out the values you have just to be sure. Perhaps set a breakpoint as well and add the variable to the watch window. You do know that the Variables window only shows design-time values during runtime, even when completed or stopped on a breakpoint. Use the watch window to get real values. This does seem rather strange, are you only expecting one row to flow through this component; otherwise you will only store the last row's value in the variable. Is that what you want?

Does the filename start with a "\" ? If not the path looks invalid - C:\Rapagator\DownloadedMYFILE. Top tip, use System.IO.Path.Combine(x, y) as that will sort out path qualifiers for you.

Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D

Handling errors in DTS packages

HI!!

I've been working for an year or so with DTS, but it still makes me mad with it's cryptic error messages!!!!

"The task reported failure on execution" is one of the "funny" error messages I retrieve. I've tried with the log option, but error messages stored there are as cryptic as the one shown on the screen!!!!!

Timothy Peterson in "MS SQL Server 2000 DTS" provides code chunks that can be used to "decode" numerical error messages into something readable and understandable, but I really don't realize where should I put that code :( It seems to work only if you are executing packages via Visual Basic, and not using the MMC

That's it, I really do need help with this!!!!!!! I beleive that there's someone out there that had faced and solved this problem !

Thanks in advance
lorenaWhy do you have to use DTS?|||I'm working on an ETL process for a Data Warehouse
Oracle "holds" the source databases and the DW is implemented on SQL Server 2000.


Originally posted by Brett Kaiser
Why do you have to use DTS?

handles and threads

Whats the difference between handles and threads ?
Also in the performance tab in Task manager , can someone explain the
meanings of each of those different values captured under totals, commit
charge, kernel memory and physical memory
Thanks
A handle is a reference to something... Sometimes it may be a reference to
a file, or an object..... Perhaps you wish to open a file and the file open
gives you a handle to the file... ( a reference to it) which you store in a
variable, and use the variable to reference the file for reading/writing.
etc.
A thread is an individually schedulable piece of work... A program( or
process) always has at least one thread. A program ( like SQL) may fire off
many threads, which work co-operatively or independently. This is called
multi-threading... When a program has multiple users ( like SQL),
multi-threading allows mulitple users to get work done at the same time...
A similar example might be a single lane road going up a mountain, with an
18 wheeler at the front of the line... No one can make more progress than
the 18 wheeler... If there were multiple lanes on the road ( equivalent to
multiple threads in an application), the others would not be blocked or
slowed down by the 18 wheeler...
Although someone else might do a better job on the task manager part... I'll
give it a stab..
Physical memory is the actual physical memory on the computer... Windows
however can act as if there were more memory available than what is
physically on the box (virtual memory). How much virtual memory is used ( I
beleive) is commit charge... Kernel memory is how much memory the OS is
using.
If you run Task Manager, select help, and on the search tab type "commit
charge". You will be taken to a window that has a short description of the
meanings as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ekX1i35cEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Whats the difference between handles and threads ?
> Also in the performance tab in Task manager , can someone explain the
> meanings of each of those different values captured under totals, commit
> charge, kernel memory and physical memory
> Thanks
>

handles and threads

Whats the difference between handles and threads ?
Also in the performance tab in Task manager , can someone explain the
meanings of each of those different values captured under totals, commit
charge, kernel memory and physical memory
ThanksA handle is a reference to something... Sometimes it may be a reference to
a file, or an object..... Perhaps you wish to open a file and the file open
gives you a handle to the file... ( a reference to it) which you store in a
variable, and use the variable to reference the file for reading/writing.
etc.
A thread is an individually schedulable piece of work... A program( or
process) always has at least one thread. A program ( like SQL) may fire off
many threads, which work co-operatively or independently. This is called
multi-threading... When a program has multiple users ( like SQL),
multi-threading allows mulitple users to get work done at the same time...
A similar example might be a single lane road going up a mountain, with an
18 wheeler at the front of the line... No one can make more progress than
the 18 wheeler... If there were multiple lanes on the road ( equivalent to
multiple threads in an application), the others would not be blocked or
slowed down by the 18 wheeler...
Although someone else might do a better job on the task manager part... I'll
give it a stab..
Physical memory is the actual physical memory on the computer... Windows
however can act as if there were more memory available than what is
physically on the box (virtual memory). How much virtual memory is used ( I
beleive) is commit charge... Kernel memory is how much memory the OS is
using.
If you run Task Manager, select help, and on the search tab type "commit
charge". You will be taken to a window that has a short description of the
meanings as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ekX1i35cEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Whats the difference between handles and threads ?
> Also in the performance tab in Task manager , can someone explain the
> meanings of each of those different values captured under totals, commit
> charge, kernel memory and physical memory
> Thanks
>

handles and threads

Whats the difference between handles and threads ?
Also in the performance tab in Task manager , can someone explain the
meanings of each of those different values captured under totals, commit
charge, kernel memory and physical memory
ThanksA handle is a reference to something... Sometimes it may be a reference to
a file, or an object..... Perhaps you wish to open a file and the file open
gives you a handle to the file... ( a reference to it) which you store in a
variable, and use the variable to reference the file for reading/writing.
etc.
A thread is an individually schedulable piece of work... A program( or
process) always has at least one thread. A program ( like SQL) may fire off
many threads, which work co-operatively or independently. This is called
multi-threading... When a program has multiple users ( like SQL),
multi-threading allows mulitple users to get work done at the same time...
A similar example might be a single lane road going up a mountain, with an
18 wheeler at the front of the line... No one can make more progress than
the 18 wheeler... If there were multiple lanes on the road ( equivalent to
multiple threads in an application), the others would not be blocked or
slowed down by the 18 wheeler...
Although someone else might do a better job on the task manager part... I'll
give it a stab..
Physical memory is the actual physical memory on the computer... Windows
however can act as if there were more memory available than what is
physically on the box (virtual memory). How much virtual memory is used ( I
beleive) is commit charge... Kernel memory is how much memory the OS is
using.
If you run Task Manager, select help, and on the search tab type "commit
charge". You will be taken to a window that has a short description of the
meanings as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ekX1i35cEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Whats the difference between handles and threads ?
> Also in the performance tab in Task manager , can someone explain the
> meanings of each of those different values captured under totals, commit
> charge, kernel memory and physical memory
> Thanks
>