Problem with logging/System::ErrorDescription variable

Hi,

I've come up against a problem in my error handling.

I have a package-level OnError handler which is a SQL Execute task. The SQL execute task constructs a SqlStatementSource expression to insert the error details into a table. This works fine and I've tested it by introducing various errors in the package.

I have another SQL Execute task, which executes a SQL script from a file connection. This is a very long script (100s of lines) and works fine. I introduced a deliberate error into it and got the following errors:

Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters.
Error: Reading the variable "System::ErrorDescription" failed with error code 0xC0047100.
Error: The expression <expression> on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

The problem is obviously the length of the script, since SSIS attempts to copy all of it into the ErrorDescription, but this seems to occur before I can do anything about it. I've tried putting a SUBSTRING function around the ErrorDescription variable without success. If I introduce an error into a shorter script the error handling works fine.

Is this a bug in SSIS? Is there any sort of workround that anybody knows of?

thanks
- Jerzy

It sounds like it could be a bug. It needs validating by someone from the SSIS dev team but unfortunately they don't seem to appear on here as much as they used to.

Try logging it at the feedback center with repro steps: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||Thanks. I've done that. Bug ID FDBK45894

- Jerzy
|||Sounds a bit funny, but there is a known limit of 4000 characters on expressions, so for long errors you may hit this. The other issue you can hit is that the message may contain single or double quotes. Best case, it fails, worst case you have a SQL injection attack. For this reason alone I'd say just use a stored procedure, and the built in parameter support. The ErrorDescription variable should map to a procedure parameter, which should solve both the size limit (if that is your issue), and prevent injection.

Problem with Link server from sql 2005 to sql 2005 - Openquery doesnt works

I have created a linked server on which following query works fine.

EXECUTE ('SELECT TOP 10 * FROM dummyOBJECTS') AT [REMOTE]

but the same statement executed with openquery

select * from openquery([remote],'select top 10 * from dummyObjects') returns following error.

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "remote" supplied inconsistent metadata for a column. The column "dummyObjectID" (compile-time ordinal 1) of object "select top 10 * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Hi ck!

Could you remove all columns except dummyObjectID and run this again?

If this reproes, could you reply with a sequence of "CREATE TABLE" and "INSERT" statements that will allow me to repro this on my machine?

Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem

Problem with executing sql-query

When I execute this query, I get the error: 'The colum prefix 'ClassifiedAd does not match with a table'. But the table does exist. Does anyone know what's wrong?

select ca.ItemID,
ca.ModuleID,
'CreatedByUser' = u1.FirstName + ' ' + u1.LastName,
ClassifiedAd.CreatedDate,
'UpdatedByUser' = u2.FirstName + ' ' + u2.LastName,
ca.UpdatedDate,
ca.AdTitle,
ca.AdText,
ca.AdHasImage,
ca.AdPOCName,
ca.AdPOCEmail,
ca.AdPOCPhone
from ClassifiedAd ca
left outer join Users u1 on ca.CreatedByUser = u1.UserID
left outer join Users u2 on ca.UpdatedByUser = u2.UserID
where ca.ItemID = 1
and ca.ModuleId = 1No it doesn't you've renamed it as "ca"

problem with executing an sqlcommand

when i push a button my datagrid doesn't show up. please help this is my code. I only try to execute one of the sql command and when i push the button it just redirects me to my homepage.

Private Sub btnbid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbid.Click

Dim conn As New SqlConnection((Application("SQL_Connection_String")))

conn.Open()

Dim cmdbid As New SqlCommand("insert into Bids (CustID,ItemCode,BidAmount) values (@.CustID,@.ItemCode,@.BidAmount)", conn)

cmdbid.Parameters.Add("@.CustID", SqlDbType.Int, 4)
cmdbid.Parameters("@.CustID").Value = Session("CustID")
cmdbid.Parameters.Add("@.ItemCode", SqlDbType.Int, 4)
cmdbid.Parameters("@.ItemCode").Value = CInt(Request.QueryString("Id"))
cmdbid.Parameters.Add("@.BidAmount", SqlDbType.Decimal, 9)
cmdbid.Parameters("@.BidAmount").Value = CDec(txtbidamount.Text)

Dim cmdhighbid As New SqlCommand("update items set Highestbid=@.BidAmount,HighestBidder=@.bidder where ItemCode=@.ItemCode and Highestbid<@.BidAmount", conn)

cmdhighbid.Parameters.Add("@.BidAmount", SqlDbType.Decimal, 9)
cmdhighbid.Parameters("@.BidAmount").Value = CDec(txtbidamount.Text)
cmdhighbid.Parameters.Add("@.bidder", SqlDbType.Int, 4)
cmdhighbid.Parameters("@.bidder").Value = Session("CustID")
cmdhighbid.Parameters.Add("@.ItemCode", SqlDbType.Int, 4)
cmdhighbid.Parameters("@.ItemCode").Value = Request.QueryString("Id")

Try
cmdbid.ExecuteNonQuery()

lblbidstatus.Text = "Bid Inserted Successfully!! Good Luck!!!"
Catch ex As Exception

lblbidstatus.Text = ex.Message
End Try

btnbid.Enabled = False
conn.Close()
End Subare you binding the datagrid within if not ispostback loop in page_load.. ?

hth|||i don't have anything in the page load only the bindgrid() proc.|||because you are prbly losing the datagrid during postbck. try this

sub page_load(...)
if not ispostback() then
 bindgrid()
end if
end sub

hth|||no that's not the problem. when i push the button it redirects me to the homepage with no reason and does not do any changes i do with the sql command|||i dont see anything in your code to redirect to another page. are you sure you are calling the right event ?

Problem with executing a SQL Server DTS Package from ASP

Could someone help. I am new at writing scripts to execute a DTS package using ASP. After I run my scripts, I get the following error message :

Step [DTSStep_DTSDataPumpTask_1] failed
Task "Import FITA Calendar"

Package [Import Fita Calendar] failed

Here is what my scripts look like :

<%
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sMessage, bStatus

Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "myserver","sa","$12eww",DTSSQLStgFlag_Default,"","","","Import FITA calendar"
oPkg.Execute()

bStatus = True

For Each oStep In oPkg.Steps
sMessage = sMessage & "<p> Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sMessage = sMessage & " failed<br>"
bStatus = False
Else
sMessage = sMessage & " succeeded<br>"
End If
sMessage = sMessage & "Task """ & oPkg.Tasks (oStep.TaskName).Description & """</p>"
Next

If bStatus Then
sMessage = sMessage & "<p>Package [" & oPkg.Name & "] succeeded</p>"
Else
sMessage = sMessage & "<p>Package [" & oPkg.Name & "] failed</p>"
End If

Response.Write sMessage
Response.Write "<p>Done</p>"

%>because I do that alot. And you can exec DTS inside SP. So you can place this inside your sp and just exec from asp. Hope that helps.

example

exec master..xp_cmdshell 'dtsrun /Ssql1 /NPublish /E'
This will execute the DTSRun command line utility with the parameters

/S = SQL Server Host Name
/N = DTS Package Name
You can either use /E for "trusted connection" or /U/P to supply username and passwords

Problem with Execute SQL Task

I am having problems creating an "Execute SQL Task" which calls a stored procedure.

I have tested the procedure successfully using parameters that I have hardcoded on the command line (i.e., EXEC procedure_name 1, 2). This works fine, but I'm having problems using variables(i.e., EXEC procedure_name @.VAR1, @.VAR2). I'm using a ConnectionType of OLE DB.

When I parse the Query I get an error message that says "The query failed to parse. Must declare the variable '@.VAR'".

However, I have this variable declared and assigned a value. I have played around the Parameter Mapping pane but I'm not getting anywhere.

Can anyone shed some light on this particular problem and how I go about fixing this?

thanks

John

John,

Place 'EXEC procedure_name ?, ?' as the SQL Statement. Then, under Parameter Mapping, add a variable select User::Var1 (or whatever the name of Var1 is) as the variable name and enter '0' (zero) as the Parameter Name. Add another variable, select User::Var2 as the variable name and enter '1' as the Parameter Name. That should do it.

Also, I've never been able to parse a query with parameters in it.

Eric

|||I could not reproduce your problem. However, I could use "Execute SQL task" to execute a stored procedure by passing a variable as the input parameter.

I had sp_GetDetails in my db that took one input (varchar type). I created a variable called "inputVal" and assigned a value to it. In my "Execute SQL task", I had "Execute sp_GetDetails @.inputValParam" as my SQLStatement. I also created a mapping between inputVal and inputValParam using "Parameter Mapping" in my task. This task works if the connection type is ADO.Net. If I change that to OLE DB, it does not work.|||Thank you so much. This has been very frustrating at best. I don't think some of these transform tasks are that intuitive at all...|||How would you pass a mix of variables and hardcoded values (i.e., @.var1, @.var2, null, null, 2, "test") without using the parameter mapping?|||

Simply use "EXEC procedure_name ?, ?, 1, NULL, 'Yes', ?, ?". The question marks serve as placeholders for the parameters which you are going to map on the 'Parameter Mapping' page. The parameter list is a 0-based array. So your parameter names will be 0, 1, 2, 3, ..., n respectively, and they're placed into the SQL in the order they're named, so the first '?' corresponds to parameter 0, the second '?' corresponds to parameter 1, etc.

So, if I have User::var1 with a value of 'A' mapped to 0, User::var2 = 'B' -> 1, User::var3 = 'Jim' -> 2, User::var4 = 'Bob' -> 3 the SQL sent via the SQL task would be "EXEC procedure_name 'A', 'B', 1, NULL, 'Yes', 'Jim', 'Bob'".

Hope that doesn't confuse things more.

Eric

|||

Kaarthik,

Kirk has a useful post which may help you here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

-Jamie

|||Thanks for the great info. I'll see if I can get this to work for me. Great help again ...

Problem with Execute Process Task

Hi,

I have what should be a very simple process.

I have a FOREACHLOOP container, that is set up to loop through files in a directory:

Enumerator: Foreach File Enumerator

Folder: c:\myfolder

Files: *.txt

Retrieve: Name and extension

Variable mappings: User::file_name, Index 0

So far, so good...

Then, inside the FOREACHLOOP, I have an Execute Process Task, which is simply a .bat file that I am running, and passing the name of the file to:

For example: myBat.bat file_name

In the Execute Process Task, I have:

RequireFullFileName: false

Executable: c:\myFile\myBat.bat

WorkingDirectory: c:\myFile

StandardInputVariable: User::file_name

Everything else is set to the defaults.

However, when I execute my container, I get the following error:

Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "c:\myFile\myBat.bat" " " at "c:\myFile", The process exit code was "1", while the the expected was "0".

Is there anything I am obviously doing wrong here at the package level? When I run the .bat file from the command line, it works fine.

Thanks

Even when I remove the Standard Input Variable, and just put file name as the Argument, I still get an error. This is just running the Execute Process Task, not the loop, for one file only.

|||

Ok, I set the SucessValue = 1, instead of 0, and now it's running ok with the single file name argument.

However, when I change it back to the StandardInputVariable with the User::file_name, it doesn't run the batch file correctly, that is the batch file does not work as expected, which makes me wonder what the User::file_name variable looks like?

I set a breakpoint, but how can I see my watch variable?

Thanks!

|||Type User::file_name into the watch window.|||In the execute process task, if the processes uses command line arguments, as in
some_executable.exe arg1
or
some_batch_file.bat arg2

use the Arguments property.

If the process to execute is properly invoked as below, then it is expecting and will use the standard input file stream, not a command line argument, so use the StandardInputVariable property. Note setting the StandardInputVariable to the "User::file_name" is streaming in the name of the file, NOT the contents of the file (as in the echo "x" case below).

echo "x" | some_executable.exe

If the process expects both command line arguments and standard input, as follows:

echo "x" | some_executable.exe arg1

then use both task properties as well.

Problem With Excel Import in Execute Phase

Hi All

I have a SSIS package running and tested fine on my desktop. However when I deploy the package to my server I get the error given below.

SSIS package "CR Sec Watch List 2.dtsx" starting.

Information: 0x4001100A at Transform Master Files: Starting distributed transaction for this container.

Information: 0x4004300A at Transform Master Files, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Transform Master Files, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Transform Master Files, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Transform Master Files, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

Error: 0xC0208265 at Transform Master Files, Excel Source [1]: Failed to retrieve long data for column "NKoreaPoi".

Error: 0xC020901C at Transform Master Files, Excel Source [1]: There was an error with output "Excel Source Output" (9) on component "Excel Source" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output "Excel Source Output" (9)" failed because error code 0xC0209071 occurred, and the error row disposition on "component "Excel Source" (1)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error: 0xC0047038 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at Transform Master Files, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Transform Master Files, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Transform Master Files, DTS.Pipeline: "component "SQL Server Destination" (20)" wrote 0 rows.

Task failed: Transform Master Files

Information: 0x4001100C at Transform Master Files: Aborting the current distributed transaction.

Warning: 0x80019002 at Process Master Files: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "CR Sec Watch List 2.dtsx" finished: Success

For some reason it tells me that it not able to retrive the long data for a column. The column mentioned in the error contains special characters.

Once again, there seems to be no problem with the package as the package runs fine on my desktop.

Any help or clues as to why this is happening on my server is greatly appreciated.

Regards

Ryan

Jet Engine for Excel guesses data type of a column. Please see threads on TypeGuessRows and IMEX to understand how Jet works, and its limitations, and how to tune it to work for you.

Setting TypeGuessRows to 0 would force Jet to scan all rows in the Excel to guess the type. You may also need to set the Defalut Type to Text.

Using Imex=1 in your Connection string is essential to get mixed types to default to Text.

Other Potential Problems:

Maybe the excel file on your PC contains Long Data, and the one you used on the server does not contain even a single row with Long Data in your NorthKorea column.

Maybe the Jet Engine Settings have different values on your PC and your server.

If the same file is giving errors, even when Jet Engine settings are same, then I would speculate if it is a Locale problem?

HTH

Kar

|||

Thanks Kar.

You recommendation worked. Setting typeguessrows to 0 did the trick.

Regards

Ryan

Problem with dynamic query and statement IN

Hi, try to execute this:

"where a12.year_id = " & Parameters!years.Value &
" and a12.month_of_year in ( " & Parameters!months.Value &" ) " &

but when I run the report raise an error
someone kwon if it's possible to use the IN statement within a dynamic query?

I've setting The parameter month as multivalue

thanksI don′t know where you enter that query, but if you use the query in the command text of the report designer you can simply use the WHERE SomeColumn IN(@.ParameterName)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I enter thsi query in the Edit Expression because I need to use the IIF function!
Within the Edit Expression the @.someVariable is an identifier Unknow, you must use Parametres!someVariable.value

If I use the variable without multivalue option , it works right
"where table.fied =" & Parametres!someVariable.value

If I use the variable with multivalue option , it don't work right

"where table.fied IN ( " & Parametres!someVariable.value & ")"

tanks|||You can use dynamic queries as expressions and use a "IN" statement. The problem, or "bug", is that with dynamic queries the dataset may not get refreshed when something is changed. You can use the field value (Parameters!Some_param.Value) or the query syntax (@.Some_Param).

The way I've worked around this is to actually drop in a straight query with my parameters, refresh the dataset, then run the report for good measure. Once I know it runs I then put in my dynamic query expression and all seems to work.

Try this... and I hope it helps.|||thanks
Now it works
abc_abc

Problem with DTSX

I am trying to execute an DTSX for Exporting data from SQL 2005 to Access Database. From SQL Wizard, the Export process is running fine.

When I tried to execute the same using an Stored Procedure (
XP_CMDShell 'dtexec /F E:\Shared\Export\Export_Fame_Entity.dtsx') it is getting executed for long time.. but never gets completed and thus, not giving any error also.

Help required.........

regards,
Ravi K
Mascon Global Limited

In SQL Server 2005 the xp_cmdshell is disabled in new installations. This may be your problem. The Books Online topic, Surface Area Configuration for Features, rovides more information about enabling xp_cmdshell.

Also it looks like your command line is missing quotes. Try using "E:\Shared\Export\Export_Fame_Entity.dtsx"
I ran a package successfully from the Query window in SQL Server Management Studio using the following syntax
xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'
The SQL Server 2005 Books Online topic, dtexec Utility, provides a wealth of information about running packages by using dtexec.

Marianne
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem with dtsrun

I am trying to execute a DTS Package from within a stored procedure, however the Execute statement hangs while running the debugger.

The sql is EXECUTE @.SP_Return = master.dbo.xp_cmdshell 'DTSRun /E /SBIGO /NPFW Data Load', NO_OUTPUT

The debugger hangs, SQL Query Analyser does not respond after exiting the debugger and MSSQLSERVER needs to be stopped & restarted.

The DTS Packages runs fine from the cmd prompt as can be seen below

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Greg>DTSRun /E /SBIGO /NPFW Data Load
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data] Step
DTSRun OnProgress: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data]
Step; 500 Rows have been transformed or copied.; PercentComple te = 0; ProgressCount = 500
DTSRun OnFinish: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Mon
thly_Load_Data] Step
DTSRun: Package execution complete.

C:\Documents and Settings\Greg>

Any help would be great.

Are debugging procedure in QA?

If so comment the dts package during debug there may be disconnect from DTSRun utility and debugger might be not able to handle situation properly...

Problem With DTS Package - To Delay Process

Hello All SQL Experts.

Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.

In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.

I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?

Please advise. Thank you.

Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.

declare @.MainUpdate datetime
declare @.TempUpdate datetime

select @.MainUpdate=Main_Update_CET from APMEAPV_Compare
select @.TempUpdate=Temp_Update_CET from APMEAPV_Compare
--select @.MainUpdate, @.TempUpdate

if @.MainUpdate<>@.TempUpdate
begin
DECLARE @.commandK varchar(1000)
SET @.commandK='Start Excel.exe "D:\Daily_Status_Report_EDWH\EDWH_Runbook_BTS.xls"'
exec master..xp_cmdshell @.commandK, No_Output

ENDIf you don't have the ability to crate a JOB on the server that will run steps
and create each step as a different DTS or executable command.

You can do a couple of things technically.

Put a step in the DTS to loop a number 36000 = 1 second I think.

Or You could use ACCESS (UGH) to create a virtual DTS and control each step from there You would need to make each step in the DTS a seperate DTS to do this.
opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

opackage.Execute

opackage.UnInitialize

Set opackage = Nothing

I would ask what are you trying to accomplish and is this the best tool to do this.

Hope this helps.|||Hello rbackmann.

Thank you for your advise. I have introduced a count in each step to delay the start and they worked ok for me.

Thank you.

problem with DTS package

have a DTS package which utilizes an "Execute Process
Task". This task calls a batch file on my server. I keep getting this error trying to run the DTS from sqlserver:

"CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1:

Does anyone know what this error mean? TIAWhat is the level of service pack on SQL & OS?|||Possibly the batch job you are calling is producing an error. Try executing your batch job from a command window and inspect its result.

kbk|||Also enable DTS package logging for more information on this behaviour.

problem with dtexec

Hi

I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.

Following is the command I use to run my application

Dtexec /FILE "N:\Temp\LoadFirewallData.dtsx" /CONFIGFILE "N:\Temp\LoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[User::RunID]";41

Regards

Meghana

Are you running DTExecUi and DTExec on the same machine, same use? is the DTExec command line exactly the same as that for DTExecUI (look at the Command Line page in DTExecUI if you have not already)?|||

I ran to similar problems. My package has several variables. When I passed them to DTExec, it does not work and it always takes the values I set when I design the package.

Should I design the package differently? No configurations?

Guangming

|||I am using the exact same command line which shows up in dtexecui. It is not working.|||I'm afraid I cannot see why you should get any problems between the two like this, so I assume it is the package. The only other idea I have is to turn on some detailed logging in the package and see what happens prior to it getting stuck.|||

Following is the error which occurs before it gets hung. The variable is of type string. This error occurs on multi proc machine when executed using dtexec. But the same does not happen when we execute it with dtexecui.

The variable "User::stripfilename" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Also, when executed through dtexec command line, it is not consistent enough to show the same error, sometimes it runs through fine and sometimes it errors and hangs till we kill the job from task manager

It seems like couple of other folks are also facing the similar issue

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

Please let us know if there is any way to resolve this as this is very crucial part of our project.

Regards

Meghana

|||have you tried running the tasks in sequence, rarther than parallel? Have you tried MaxConcurrentExecutables? Seems there is a bug here, but in the meantime one of those should allow you to get going again.|||If it's an issue with variables, and you're not using this in the script, try using

Public Shared Function getVar(ByVal varName As String) As String
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead(varName, vars)
Return vars(varName).Value.ToString()
End Function

rather than referencing the variable in the script properties.

|||

Finally I found the problem:

The way to put the command string is not right after "dtexec /SQL. "

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

problem with dtexec

Hi

I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.

Following is the command I use to run my application

Dtexec /FILE "N:\Temp\LoadFirewallData.dtsx" /CONFIGFILE "N:\Temp\LoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[User::RunID]";41

Regards

Meghana

Are you running DTExecUi and DTExec on the same machine, same use? is the DTExec command line exactly the same as that for DTExecUI (look at the Command Line page in DTExecUI if you have not already)?|||

I ran to similar problems. My package has several variables. When I passed them to DTExec, it does not work and it always takes the values I set when I design the package.

Should I design the package differently? No configurations?

Guangming

|||I am using the exact same command line which shows up in dtexecui. It is not working.|||I'm afraid I cannot see why you should get any problems between the two like this, so I assume it is the package. The only other idea I have is to turn on some detailed logging in the package and see what happens prior to it getting stuck.|||

Following is the error which occurs before it gets hung. The variable is of type string. This error occurs on multi proc machine when executed using dtexec. But the same does not happen when we execute it with dtexecui.

The variable "User::stripfilename" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Also, when executed through dtexec command line, it is not consistent enough to show the same error, sometimes it runs through fine and sometimes it errors and hangs till we kill the job from task manager

It seems like couple of other folks are also facing the similar issue

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

Please let us know if there is any way to resolve this as this is very crucial part of our project.

Regards

Meghana

|||have you tried running the tasks in sequence, rarther than parallel? Have you tried MaxConcurrentExecutables? Seems there is a bug here, but in the meantime one of those should allow you to get going again.|||If it's an issue with variables, and you're not using this in the script, try using

Public Shared Function getVar(ByVal varName As String) As String
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead(varName, vars)
Return vars(varName).Value.ToString()
End Function

rather than referencing the variable in the script properties.

|||

Finally I found the problem:

The way to put the command string is not right after "dtexec /SQL. "

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

problem with drilldown (ReportServer)

hi,

i have a problem with the drilldown functionality of my report:

in the preview pane in VS2005 everything works fine but when I try to execute the report on reportserver the only thing I see is the top level and I'm not able to drilldown into lower levels ...

anyone has an idea?
Idid not implement the drilldown in mdx statement but used grouping & toggle item in VS2005. could that be the reason?

thanks,
rhpasy

Check the supported versions for different web browsers. Static reports is the version supported in older browsers.

Also add the reportserver/manager to the local intranet zone or trusted site(if you use Internet Explorer).

Regards

Thomas Ivarsson

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.codecomments.com ***
Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:

>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help.
I apply the changes that you recommended to me, but this works only if i
use the begin transaction clause. But If I use the begin distributed
transaction, it doesnt work.
Do you know any other issue that i have to consider?
Thanks a lot!!
*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help. At the end, I resolve this problem following
this link.
http://support.microsoft.com/kb/831425/en-us
Regards,
*** Sent via Developersdex http://www.codecomments.com ***

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.developersdex.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:
>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.codecomments.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:

>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot for your help.
I apply the changes that you recommended to me, but this works only if i
use the begin transaction clause. But If I use the begin distributed
transaction, it doesnt work.
Do you know any other issue that i have to consider'
Thanks a lot!!
*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot for your help. At the end, I resolve this problem following
this link.
http://support.microsoft.com/kb/831425/en-us
Regards,
*** Sent via Developersdex http://www.codecomments.com ***