Problem with Look up

Hi all;

I have created a SSIS package to import data from flat file to database table, which consists of
Flat file Source -> Look up -> OLE DB Destination.
I'm using Look Up in order to avoid duplicate records.
My database table contain around 35,00000 s of rows.
So when I'm running the package the above amount of data goes to Lookup cache, which require a large storage memory.
So I'm not able to execute my package.
I need to run the package in order to update my database.
So is there any solution, in which I can check the duplicate records to get into the database and time consumption to run the package will be less.

Thanks in advance...

Be sure that you are using a SQL statement to only select distinct keys from the table:

select primary_key from table

Then you just simply join the key fields.|||

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

|||

Andy Leonard wrote:

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

Why would this matter? Selecting the primary key of the full table (should be indexed!) should be less work than staging the keys first, and then selecting from there. That sounds like an extra, unnecessary step to me and will, in turn, cost more than my approach.

Am I missing something? Unless of course, you're talking about performing a SQL join against that staging table so as to avoid caching the keys in memory.|||

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

|||

Andy Leonard wrote:

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

Well, yeah, that's the one thing we don't know. I want to be sure the user is selecting *just* the key, and not the whole table.

problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

problem with installing SQL Server 2005 Data Mining Add-Ins

Hi,

I have a problem with installing SQL Server 2005 Data Mining Add-Ins for Office 2007 package.

I downloaded and installed to my laptop and I did not see the analyze tab under Table tools.

Do you have any idea?

I unistall and reinstall the add-in

I uninstall the previous version 2003 and now I have only Office 2007

I have Microsoft Windows XP Professional

Version 5.1.2600 Service Pack 2 Build 2600
and Microsoft Office Excel 2007 12.0.4518.1014 MSO 12.0.4518.1014

I have all program in English but I have Greek regional settings.

Do you have any idea, please help me.

Thank you in advance.

Did you see data mining ribbon in Excel 2007? If yes, you already got Data Mining Addin on your machine. You need to activate a table (not a range) in Excel 2007 to see tne analyze ribbon.

If you can not see data mining ribbon, please doulbe check wheather the Data Mining Addin was accidentally deativated (Right click on the ribbon toolbar, select Customize the Quick Access Toolbar, you can see the Addins. You can load/unload/reactivate addins from there).

Good luck,

Problem with Failure Constraint

Hi,

As part of my SSIS package I have a script task, that set its result based on the value of a package variable. From this task I have two precedence constraints, a sucess constrain and failure constraint that lead to two different tasks.

When the script task ends with a success result, the task that is connected by the success constraint is initiated, but when the script task ends with a failure result, it is marked with red color and the execution of the whole package stops (the next task that is connected by the failure constraint is not initiated).

All tasks are assigned with False to the properties: FailPackageOnFailure & FailParentOnFailure

Please assist.

Hmmmm....strange!

Can you post the contents of the .dtsx file up here so we can repro?

If you could take out all external references (e.g. connection managers, configurations, etc...) it'd be a big help!

Thanks

Jamie

|||Is there a way to attach the file?|||Have you increased MaxErrorCount property? (I don't remember the details of interactions between it and other two properties, but try increasing it).|||

Thanks for you help.

I believe I solved the problem: the task, that was connected to the script task by the failure constraint, could be executed also as an error handling task of another task, therefore all faliure constraints should have LogicalAnd property set to False.

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 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 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 Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony

Problem with DTS schedule

Hi,

I have a little DTS problem here.
This package is programmed in an SQL Server v7.0 box and it executes OK when I run it manually.

The problem starts when I want to schedule the automatic execution of the DTS and in the Job History I got this error: "Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed."

That means that the user owner of the DTS must be a SysAdmin? The current role for the user is "Process Administrator" but not "System Administrator".

As I cannot give the user superpower in the server, do I have to recompile de DTS under other user that is System Administrator?

Regards,

GOne of the archived post refers
Expand Management, right-click SQL Server Agent, and then click Properties. Click the Job System tab.

Under Non-SysAdmin job step proxy account, clear the Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps check box, and click Reset Proxy Account.

Type the user name, password, and domain of the user account to be used by SQL Server Agent when running jobs owned by users who are not system administrators.

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 DTS

I am using a DTS package to import data from primary server to secondary
server to update the changes occured in few tables and both located in a
different location. The problem is that whenever this DTS fails to execute i
will loose even the existing datas in secondary server since it delete the
existing data before inserting, how to overcome this? OR Is there any better
ways to implement this?
Thanks in advanceHi
Well, I'd transfer the OLD data before deleting into a temporary table and
in case of failure ( in order to not loose the data) nove the data back.
What's error do you get when you run the DTS and it failed?
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
>I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
> i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any
> better
> ways to implement this?
> Thanks in advance
>|||It just showing "Job Failed". It happens whenever if there any problem with
internet or network.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Hi
> Well, I'd transfer the OLD data before deleting into a temporary table
and
> in case of failure ( in order to not loose the data) nove the data back.
> What's error do you get when you run the DTS and it failed?
>
>
> "imtiaz" <Imtiaz@.microsoft.com> wrote in message
> news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
execute
the
>|||Ok, so you can specify an OUTPUT file under Advanced Tab in the Step's
definition.It will give the error desciption
You will have to introduce some logic behind like if the job's step is
failed go to the next step and do soemthing
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:uFH027KfGHA.2456@.TK2MSFTNGP04.phx.gbl...
> It just showing "Job Failed". It happens whenever if there any problem
> with
> internet or network.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> and
> execute
> the
>|||Can you put Delete and Insert into a single transaction? If one step fails,
just roll back the whole transaction.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>|||Try www.sqlscripter.com to transfer your data.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>

Problem with DTS

Hi,
I was wondering if anyone has ever had this problem before.
I have a whole host of DTS packages that are executed from one DTS package.
The DTS packages essentially get data from a Sybase database and puts the
data into a text file, runs a simple Stored proc which etc, nothing really
complicated.
Problem is lately when I run the DTS package which is called from a Batch
file which is executed by scheduler, random packages seem to go into a
continuous loop and the package continues to insert data into the text file
until such time as my server runs out of space.
Any ideas anyone.
Thanks
KNever seen that problem.
Do you have the tasks set to "Close connection on completion"
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kathy Bezuidenhout" <Kathy.Bezuidenhout@.nospam.aig.com> wrote in message
news:ab2cnTWLr4q05gqiU-KYjQ@.is.co.za...
> Hi,
> I was wondering if anyone has ever had this problem before.
> I have a whole host of DTS packages that are executed from one DTS
package.
> The DTS packages essentially get data from a Sybase database and puts the
> data into a text file, runs a simple Stored proc which etc, nothing
really
> complicated.
> Problem is lately when I run the DTS package which is called from a Batch
> file which is executed by scheduler, random packages seem to go into a
> continuous loop and the package continues to insert data into the text
file
> until such time as my server runs out of space.
> Any ideas anyone.
> Thanks
> K
>

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