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.

Having problem to use the timestamp of the last successful job run

Hi,
I am using the MS sql DTS package to import some log data
from openenterprise server to the MS Sql server.
I am trying use the timestamps values of the MS SQL job
which ran successfully last time.
I was able to create an DTS pachage which imports the last
24 hours log data from a table of a database engine
name "polyhedra" which is used in a SCADA system to a MS
SQL sever table.
I made a schedule which imports the log data for the last
24 hours. My query statement look like as follows:
Select ID, name, timestamp, realvalue, delete
from realsamples
where timestamp > now()-hours(24)
But instead of hours(24), I need to use the timestamp when
my job last ran successfully. Because incase the any of
the server fails to run for more than 24 hours I will not
get the data older than last 24 hours.
Please help me to solve the problem.
Thank you in advance.You can get information about jobs (including the times when the job started
and if it completed succesfully) using the sp_help_jobhistory stored
procedure. See Books Online for the full syntax and details.
Jacco Schalkwijk
SQL Server MVP
"M Sikder" <anonymous@.discussions.microsoft.com> wrote in message
news:01a101c3bc3d$ed71f000$a301280a@.phx.gbl...
> Hi,
> I am using the MS sql DTS package to import some log data
> from openenterprise server to the MS Sql server.
> I am trying use the timestamps values of the MS SQL job
> which ran successfully last time.
> I was able to create an DTS pachage which imports the last
> 24 hours log data from a table of a database engine
> name "polyhedra" which is used in a SCADA system to a MS
> SQL sever table.
> I made a schedule which imports the log data for the last
> 24 hours. My query statement look like as follows:
> Select ID, name, timestamp, realvalue, delete
> from realsamples
> where timestamp > now()-hours(24)
> But instead of hours(24), I need to use the timestamp when
> my job last ran successfully. Because incase the any of
> the server fails to run for more than 24 hours I will not
> get the data older than last 24 hours.
> Please help me to solve the problem.
> Thank you in advance.

Havent been able to execute a package on the server

Hello, I created a package on my machine, it deletes some files, then delete some rows, then copy files from a destination to a source and then process all those files and insert rows in a table, really simple.

When I click execute in VS 2005 it executes perfectly. (it took about 45 seconds because there are many files)

I connected to integration services and imported the package then I did the two following things.

1-Right click run package and it executes normally but it took less than 1 second, and when I saw the destination folder there were no files in there so it didnt do anything)

2. I created a job and on the first step I put to execute that package. I then executed the job and the same thing happens, it executed without errors but it took less than 1 send and when I saw the destination folder there were no files in there so it didnt do anything).

I noticed that the Integration services project has a property for creating a deployment utility, I changed this property to true but I dont know how to make the deployment utility.

Maybe the problem was that when I Imported the package,, the package was on another machine on my LAN?

Can the package on the server even see the files? The directories have to be the same, and the user account for the SQL Server service must have rights to that directory as well.

When you execute it on your machine, it's using your user account and currently accessible folders. When the package is promoted to the server, you will be using a different account.|||enable package logging to get more details of the execution. The package may be failing...|||

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

|||

Luis Esteban Valencia Mu?oz wrote:

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Try using SQL Server Agent CmdExec job step sub-system so that Dtexec can be employed. This will cause more detailed error messages to be stored in the job history. It is also useful to implement the job step log.|||

Hi Luis,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey may be flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that is simplest to implement is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well. But this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

Have DTS Package prompt for a file name

I have a user in the IT department that wants a process to take his text file and import it into a SQL Server table. Simple enough with a DTS package.

The rub is that he want's to execute the package and have it prompt him at that point for where the file resides. I tried to get him to go into the DTS pacakge and update the connection, but he doesn't want to do it that way. I suggested renaming the file to a common name to be used each time the application runs, and he wasn't interested in that solution either.

Any help you can give would be greatly appreciated. Thanks!

Have a look at this:

http://www.sqldts.com/default.aspx?226

Has anyone tried...

I currently have a DTS package that takes a text file source and transforms the data into a table. The transformation does a lookup on a product_code column (char(8)) to transform it to the correct product_id (int) for our system.

I've recently setup a view that matches the file layout and has an instead of trigger that inserts into the target table. It does this by joining the inserted table to a translation table to get the correct product_id for the insert. Instead of a DTS package I created a proc that BULK INSERTS the file into the view.

The view approach is running over twice as fast as the DTS package. Is this approach common or has anyone else tried it. Any feedback would be appreciated.I don't think it is uncommon to see that kind of performance gain. DTS is very good at doing complex things, but it can't compete with BULK INSERT or BCP for doing simple things. The trick is to figure out which tool is best for a given job.

-PatP|||I forgot to mention the file contains 9.9 million records with 7 columns. The DTS package runs in 20 minutes vs. 8 for the view. BCP'ing the file directly into a table with no translation takes 3 minutes.|||DTS is very good at doing complex things...
-PatP

I'm still waiting for my burger and fries....

There's is NOTHING that will beat bcp load to staging table and set process t-sql to fix whatever it is you need to fix...especially if the file to be loaded is in native format...|||Try loading a Notes log file into SQL Server doing LDAP lookups for the VPN derived data. DTS can do it nicely using the API, BCP can't get there from here without something that can at least produce a file first, and nothing I've seen will do that very well.

The jobs that BCP can do, it does VERY well. I don't think that anything can be faster than BCP. The jobs that BCP can't do... it can't do.

-PatP

Hardware Setup

I am new to SQL and over the next year we will be implementing a SQL server
for an imaging application and a CRM package and I already have a few other
small packages using MSDE that could take advantage of a SQL server. Is it
best to have one large SQL
server for your network or have many different boxes. I realize that by onl
y having one box you put all of you eggs in one basket although if you have
several you need several different copies of the software and lots of hardwa
re which drives the cost up
. We are a growing 100 person company and we need to prepare for the future
. Is it not advisable to say build a quad processor box with lots of RAM an
d Disk and purchase the Processor version of either Standard or Enterprise a
nd let several apps take ad
vantage of the SQL server? Is it dependent on the apps whether or not you c
ould do this? Any pointers or information would be greatly appreciated....
ThanksMike,
There are pro's and con's to everything and this is no exception but in
general I feel that you will be better off going with one server vs.
several. This will simplify maintenance, lower costs and will be easier to
expand later on. This is especially true if none of the apps that will run
on the box require a huge amount of resources all the time. This way the
different apps can share the larger resource pool (memory, cpu's etc) more
efficiently and take advantage of their individual peaks without sacrificing
performance.
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:371D39BD-DCF1-47B0-9E08-292128DA8EFE@.microsoft.com...
> I am new to SQL and over the next year we will be implementing a SQL
server for an imaging application and a CRM package and I already have a few
other small packages using MSDE that could take advantage of a SQL server.
Is it best to have one large SQL server for your network or have many
different boxes. I realize that by only having one box you put all of you
eggs in one basket although if you have several you need several different
copies of the software and lots of hardware which drives the cost up. We
are a growing 100 person company and we need to prepare for the future. Is
it not advisable to say build a quad processor box with lots of RAM and Disk
and purchase the Processor version of either Standard or Enterprise and let
several apps take advantage of the SQL server? Is it dependent on the apps
whether or not you could do this? Any pointers or information would be
greatly appreciated....
> Thanks

Hardware Setup

I am new to SQL and over the next year we will be implementing a SQL server for an imaging application and a CRM package and I already have a few other small packages using MSDE that could take advantage of a SQL server. Is it best to have one large SQL
server for your network or have many different boxes. I realize that by only having one box you put all of you eggs in one basket although if you have several you need several different copies of the software and lots of hardware which drives the cost up
. We are a growing 100 person company and we need to prepare for the future. Is it not advisable to say build a quad processor box with lots of RAM and Disk and purchase the Processor version of either Standard or Enterprise and let several apps take ad
vantage of the SQL server? Is it dependent on the apps whether or not you could do this? Any pointers or information would be greatly appreciated....
Thanks
Mike,
There are pro's and con's to everything and this is no exception but in
general I feel that you will be better off going with one server vs.
several. This will simplify maintenance, lower costs and will be easier to
expand later on. This is especially true if none of the apps that will run
on the box require a huge amount of resources all the time. This way the
different apps can share the larger resource pool (memory, cpu's etc) more
efficiently and take advantage of their individual peaks without sacrificing
performance.
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:371D39BD-DCF1-47B0-9E08-292128DA8EFE@.microsoft.com...
> I am new to SQL and over the next year we will be implementing a SQL
server for an imaging application and a CRM package and I already have a few
other small packages using MSDE that could take advantage of a SQL server.
Is it best to have one large SQL server for your network or have many
different boxes. I realize that by only having one box you put all of you
eggs in one basket although if you have several you need several different
copies of the software and lots of hardware which drives the cost up. We
are a growing 100 person company and we need to prepare for the future. Is
it not advisable to say build a quad processor box with lots of RAM and Disk
and purchase the Processor version of either Standard or Enterprise and let
several apps take advantage of the SQL server? Is it dependent on the apps
whether or not you could do this? Any pointers or information would be
greatly appreciated....
> Thanks

Hardware Setup

I am new to SQL and over the next year we will be implementing a SQL server for an imaging application and a CRM package and I already have a few other small packages using MSDE that could take advantage of a SQL server. Is it best to have one large SQL server for your network or have many different boxes. I realize that by only having one box you put all of you eggs in one basket although if you have several you need several different copies of the software and lots of hardware which drives the cost up. We are a growing 100 person company and we need to prepare for the future. Is it not advisable to say build a quad processor box with lots of RAM and Disk and purchase the Processor version of either Standard or Enterprise and let several apps take advantage of the SQL server? Is it dependent on the apps whether or not you could do this? Any pointers or information would be greatly appreciated...
ThanksMike,
There are pro's and con's to everything and this is no exception but in
general I feel that you will be better off going with one server vs.
several. This will simplify maintenance, lower costs and will be easier to
expand later on. This is especially true if none of the apps that will run
on the box require a huge amount of resources all the time. This way the
different apps can share the larger resource pool (memory, cpu's etc) more
efficiently and take advantage of their individual peaks without sacrificing
performance.
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:371D39BD-DCF1-47B0-9E08-292128DA8EFE@.microsoft.com...
> I am new to SQL and over the next year we will be implementing a SQL
server for an imaging application and a CRM package and I already have a few
other small packages using MSDE that could take advantage of a SQL server.
Is it best to have one large SQL server for your network or have many
different boxes. I realize that by only having one box you put all of you
eggs in one basket although if you have several you need several different
copies of the software and lots of hardware which drives the cost up. We
are a growing 100 person company and we need to prepare for the future. Is
it not advisable to say build a quad processor box with lots of RAM and Disk
and purchase the Processor version of either Standard or Enterprise and let
several apps take advantage of the SQL server? Is it dependent on the apps
whether or not you could do this? Any pointers or information would be
greatly appreciated....
> Thanks

Handling flat files that do not exist

Hello,

I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.

Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)

I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.

So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?

Thanks!

Lee.

I recall that I have a couple data flows in my package, each within a sequence container and connected them with completion(blue) arrows and even if the first one failed the second one continued on...

|||

That is one solution that I could use. It doesn't seem like it is the "elegant" way to handle it, but it will work.

Thanks for the suggestion.

|||

well

Is dragging red arrow to some alert/send mail task will not serve purpose?

|||

Scoutn wrote:

Hello,

I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.

Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)

I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.

So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?

Thanks!

Lee.

You can use a script task to check whether or not a file exists. This code should help:

File.Exists Method
(http://msdn2.microsoft.com/en-us/library/system.io.file.exists.aspx)

-Jamie

|||

Jamie,

First allow me to say that your blog has been an invaluable resource. I have learned a lot from your articles, thank you!

Utsav, I am not looking to be notified of the error; I just want the task to merrily carry on like nothing took place. I do understand what you meant though, thank you.

Sometimes the flat file will be there and sometimes it won't be. If it isn't, I would still like the task move on to the next with a "Success" result, not just completion. That is why I was thinking of disabling the tasks that do not have corresponding flat files progmatically when I load the package from my application.

I guess I should be asking; when I set Dts.TaskResult = Dts.Results.Success, why does it not actually return success?

(I know there are a couple of ways I can get around this issue, I'm just looking for the easiest without setting the task to continue on "Completion" ;)

Thanks again,
Lee.

|||

Check if this thread has something that can help you.

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

Instead of disabling the task; try to use an expression in the precedence constraint in the control flow.

Rafael Salas

|||Thank you very much. I did search the forum, I guess I was just using the wrong search term.

Perfect!|||

The thread (and the subsequent blog articles) solved my problem. I needed to put each task in a sequence container with their own script task checking for the files; if I used one script task to look for all the files the next task would not run because at least one file would return false.

Thanks again for everyones help!

Handling bad data

I have an SSIS package that takes in a flat file and pushes the data into a table. However, once in a while the file will have some bad data in it (for example, this particular time I have too many delimiters on one line). I want the package to redirect the row to an error table and keep going with the processing for the rest of the data. To do this, I have hooked up an error output from the flat file source to an OLE DB destination and assigned all the rows to Redirect Row on Error in the Error Output section of the flat file source. Unfortunately, it does not work! The flat file receives an error and stops. Is this because something different has to happen when it is a problem with the whole row? Any help would be appreciated, thanks.Make sure you you visit the 'error output' page of the flat file source and change the value of the ERROR column to redirect row...|||Unfortunately, I've done this and it still doesn't work. I double checked and all of the columns in the Error Output section are set to Redirect Row.|||Perhaps you could have a first data flow that treats each line in the file as a single column; the with a script component you parse each row to count the number of delimiters and exclude those that exceeds the expected number. Just a thought...|||

That sounds like it should work! I haven't had a chance to work on it since I've been sidetracked into another project. Thanks for the help, I'm sorry that this took SO long for me to respond.

|||

Here's an example. hopefully it helps:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

Handling bad data

I have an SSIS package that takes in a flat file and pushes the data into a table. However, once in a while the file will have some bad data in it (for example, this particular time I have too many delimiters on one line). I want the package to redirect the row to an error table and keep going with the processing for the rest of the data. To do this, I have hooked up an error output from the flat file source to an OLE DB destination and assigned all the rows to Redirect Row on Error in the Error Output section of the flat file source. Unfortunately, it does not work! The flat file receives an error and stops. Is this because something different has to happen when it is a problem with the whole row? Any help would be appreciated, thanks.Make sure you you visit the 'error output' page of the flat file source and change the value of the ERROR column to redirect row...|||Unfortunately, I've done this and it still doesn't work. I double checked and all of the columns in the Error Output section are set to Redirect Row.|||Perhaps you could have a first data flow that treats each line in the file as a single column; the with a script component you parse each row to count the number of delimiters and exclude those that exceeds the expected number. Just a thought...|||

That sounds like it should work! I haven't had a chance to work on it since I've been sidetracked into another project. Thanks for the help, I'm sorry that this took SO long for me to respond.

|||

Here's an example. hopefully it helps:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

Handling bad data

I have an SSIS package that takes in a flat file and pushes the data into a table. However, once in a while the file will have some bad data in it (for example, this particular time I have too many delimiters on one line). I want the package to redirect the row to an error table and keep going with the processing for the rest of the data. To do this, I have hooked up an error output from the flat file source to an OLE DB destination and assigned all the rows to Redirect Row on Error in the Error Output section of the flat file source. Unfortunately, it does not work! The flat file receives an error and stops. Is this because something different has to happen when it is a problem with the whole row? Any help would be appreciated, thanks.Make sure you you visit the 'error output' page of the flat file source and change the value of the ERROR column to redirect row...|||Unfortunately, I've done this and it still doesn't work. I double checked and all of the columns in the Error Output section are set to Redirect Row.|||Perhaps you could have a first data flow that treats each line in the file as a single column; the with a script component you parse each row to count the number of delimiters and exclude those that exceeds the expected number. Just a thought...|||

That sounds like it should work! I haven't had a chance to work on it since I've been sidetracked into another project. Thanks for the help, I'm sorry that this took SO long for me to respond.

|||

Here's an example. hopefully it helps:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

Halting Execution On Error

I have a simple SSIS package split into two parts; validation and processing. I want to be able to stop execution on any package errors (such as file not found, etc) using the OnError event handler. Is this at all possible?

No, if you want to stop execution when a task fails then make sure there are no OnCompletion or OnFailure precedence constraints leading from it.

As long as you don't have any concurrent execution paths then the package will stop.

-Jamie

|||Ah. Perfect. I've been using expressions and noticed the expression and constraint option but it didn't hit me to use that until you said something.

Thanks Jamie, for helping out a greenfoot here.