Package wont run in a scheduled job

Hello,

I have put together a simple SSIS package that runs fine both via BIDS and via Mgt Studio.

The problem I have is that I can't get a scheduled job to run it. My scheduled job has only 1 step - and that step runs the package.

When I right click in Mgt Studio (SQL Agent) and choose "Start Job" I get this error :

Executed as user: NMR044BRASQL886\SYSTEM. ....3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:10:56 Error: 2007-01-09 14:10:56.50 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-01-09 14:10:57.04 Code: 0xC0202009 Source: B2B_Sales_Territory_Send_Back Connection manager "b2b_datamart" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2007-01-09 14:10:57.04 Code: 0xC020801C... The package execution fa... The step failed.

Can anyone help please?
I'm new to SSIS so clear+simple answers appreciated.

Thanks in advance
Mgale1

Check the ProtectionLevel proeprty of your package. My guess is that it is set to EncryptSensitiveWithUserKey. Is that correct?

This means that all passwords and such are encrypted with the username of the person that built the package hence when you run it as another user (which you are attempting to do here), it doesn't work. Read in BOL about the ProtectionLevel property to get a full understanding and then reply here with any problems.

-Jamie

|||

Hi Mgale1,

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 is most likely 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 makes the most sense 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, and 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

|||

You may want to check this KB article:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we (the forum) could come up with a synopsis on package encryption.

|||

I always use ProtectionLevel=DontSaveSensitive. Without exception.

This actually forces you to use configurations - and that is no bad thing in my opinion.

-Jamie

package with a custom component (log provider) runs in BIDS, but doesn't run where deployed.

Hi,

I have a package with a custom log provider, which runs in BIDS. However when I deploy the package onto SQL Server and run it on the deployed machine, if fails:

"failed to decrypt protected XML node DTS:Password...key not valid for use in specified state..."

Now this is definately to do with the custom log, as if I take it out & redeploy, I can run it on the deplyed server + also run it within a job. I have entered the custom log provider library (+ other required DLLs) in the GAC on the deployed machine, but I'm clearly missing something.

Any ideas pls? I'm really stuck.

Many thanks in advance,

Tamim.

Please search this forum...

You'll need to set the package protection level to "DontSaveSensitive" and then issue a password via the /SET command line switch. OR, you could try to promote to the server using SQL Server users/role level security, maybe....|||

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

|||

Tamim Sadikali wrote:

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

What do you mean that didn't work? That error message states that you are using indicates to me that the protection level is set to EncryptSensitiveWithUserKey, which means that ONLY the user who built the package can execute it.

Package Runs in 1 minutes in Visual Studio but takes 5+ (sometimes hanges) as Job

I have an SSIS package that when run from Visual Studio takes 1 minute or less to complete. When I schedule this package to run as a SQL Server job it takes 5+ and sometimes hangs complaining about buffers.

The server is a 4 way 3ghz Xeon (dual core) with 8GB ram and this was the only package running.

When I look in the log I see that the package is running and processing data, although very very very very very slowly.

Has anyone else experienced this?

Thanks for your help

Marcus

You need to identify what the differences are between the two executions. It could be network latency, utilisation of the box by other processes, the user that you're running it as....anything.

What happens when you execute the package on the Xeon using dtexec.exe rather than thru SQL Server Agent. Do you get the same?

-Jamie

|||

I don't klnow what is going on with my system. The above package has ran find since I posted this message. I'm now having issues with stored procedure that is being ran from a job.

I can run the SP from Management Studio and it completes. The CPU does spike above 90% but it completes.

When I execute the same SP from job I get the following error and ALL connections to the database are killed.

Msg 64, Sev 16, State 1: TCP Provider: The specified network name is no longer available. [SQLSTATE 08S01]
Msg 64, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]
Msg 10004, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]

I have taken the SQL out of the SP and tried to execute it directly from within the job and it still fails wth the same issue.

I have no idea what is going on or where to start looking.

Here is a copy of the SP that I'm trying to run from a job.... Help... I'm glad it's a Friday

CREATE PROCEDURE [dbo].[sproc_LoadData_FactMessageOpen] AS

SET NOCOUNT ON
TRUNCATE TABLE [dbo].[FactMessageOpen]

INSERT INTO [dbo].[FactMessageOpen] (

[DateID],[TimeID],[MailingID],[ProfileID],[MessageFormatID],[TransactionDateTime])
SELECT
(SELECT DateID FROM dbo.DimDate WHERE DateAK = CAST(CONVERT(varchar(10),X.TransactionDateTime,101) AS smalldatetime)) AS DateID,
(SELECT TimeID FROM DimTime WHERE Hour12 = LTRIM(SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))-2,2)) AND Minute = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+1,2) AND NoonFlag = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+3,2)) AS TimeID,
(SELECT MailingID FROM dbo.DimMailing WHERE MailingAK = X.MailingAK) AS MailingID,
(SELECT ProfileID FROM dbo.DimProfile WHERE ProfileAK = X.ProfileAK) AS ProfileID,
(SELECT MessageFormatID FROM dbo.DimMessageFormat WHERE MessageFormatAK IS NULL) AS MessageFormatID,
X.TransactionDateTime
FROM
(
SELECT
ER.TEMPLATE_ID AS MailingAK,
ER.EMAIL_ADDR_ID AS ProfileAK,
MIN(ER.ACTION_DT) AS TransactionDateTime
FROM
TEST.MDB.EMAIL_RESPONSE ER
JOIN
TEST.MDB.ACTION_SUB_ACTION_LOOKUP AL
ON
ER.ACT_SUB_ID = AL.ACT_SUB_ID
WHERE
ACTION_CD = 'G'
GROUP BY
ER.TEMPLATE_ID,
ER.EMAIL_ADDR_ID
) AS X
ORDER BY
DateID,
TimeID

|||Please share your connection strings... Could it be that the user running the job (the user assigned to the SQL Server service account) doesn't have access to the remote server/database?|||90% cpu utilization seems like too much. I'm getting the impression that you haven't examined the query execution in SQL Server Profiler. Correct?|||

Hi

I actually ran the query through SQL Profiler and it didn't come back with any recommendations. I alos looked at the explain plan and every node is using an index.

does the query look poorley written?

thanks

Package runs fine in Debug.. fails outside of debug

I created a package that runs fine while in debug... but when I run outside of debug (just choosing Debug.. Start without debugging) I get an error of:

Message: The task "Formulate SQL Query and Destination" cannot run on this edition of Integration Services. It requires a higher level edition.

I am on SP1 on my workstation and the only SSIS server I have. I am not doing anything fancy in my script.. I have created other packages and ran outside of debug mode without issue. I did uninstall SQL2005 and SSIS from my workstation as it was eating up too many resources.

The script I am running is (removed some of the SQL etc to shorten it):

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

' Create the SQL

Dim SQL As String

SQL += "SELECT "

SQL += " -- Lots of stuff"

SQL += "FROM "

SQL += CStr(Dts.Variables("gvSourceTableName").Value) & " "

SQL += "WHERE "

SQL += " stuff = '" & CStr(Dts.Variables("gvCurrentSymbol").Value) & "' "

SQL += "ORDER BY date"

Dts.Variables("lvSQLQuery").Value = SQL

' MsgBox(CStr(Dts.Variables("lvSQLQuery").Value))

' Create the filename

Dim Dir As String

Dim FileName As String

Dim FullFileName As String

Dir = CStr(Dts.Variables("gvDestinationRoot").Value) & "\" & CStr(Dts.Variables("gvRunID").Value) & "\"

FileName = CStr(Dts.Variables("gvCurrentSymbol").Value) & "--.txt"

FullFileName = Dir & FileName

If Not Directory.Exists(Dir) Then

Directory.CreateDirectory(Dir)

End If

Dts.Variables("gvDestinationFile").Value = FullFileName

' MsgBox(CStr(Dts.Variables("gvDestinationFile").Value))

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

FYI.. tried reinstalling 2005 SP1 on my workstation... still receiving the error message.|||This looks familiar. Try going to the SQL Server 2005 installer and install Integration Services on your local machine. The messages I've received that were similiar to this were solved by that. I believe you have the designer installed on your machine, but not the integration services engine. Good luck.|||Thanks, when I uninstalled SQL 2005 I must have took the SSIS stuff with it.. reinstalled SSIS server components and patch and works great now.|||

Chris Honcoop wrote:

Thanks, when I uninstalled SQL 2005 I must have took the SSIS stuff with it.. reinstalled SSIS server components and patch and works great now.

You're welcome. You know, you'd think that a better error message could be raised... maybe something that actually says you don't have SSIS installed... oh well.

Package Runs but as soon as it is scheduled in SQL Server it hangs

Hi

I'm trying to get a cute FTP script running from a package that connects to a FTPS web site.

Regardless of what method I use to execute the script it runs sucessfully in Debug mode, if I import the package into Integration Services from SQL Server Management Studio it runs, however as soon as create a SQL Server job using the stored package it hangs.

I have tried an Active X, VB.Net and an Execute Process item and get the same result everytime.

I'm also getting the same problem now with a java script I'm running from an 'Execute Process' item. Runs fine until I create a job..

Has anyone experienced the same problem? I haven't a clue what is going on and the logs aren't giving me much information.

Thanks for your help

one thing to take into account is that the script will most likely run under a different security context when it is running as a job. When you debug the script it's running with your security context, when running as a job it will run under either a proxy account context or the context of the sql agent service.

This change in security context may be causing a dialog box to pop, or you may be experiencing some kind of hidden failure.

I'd suggest having a look at the script and evaluating what security context is needed for each call. After that insert some logging into the script and evaluate what statement is not returning.

Package hangs on parallel "upserts"

I have data flow tasks, one which validates the import file and one which processes the import file if the validation passed. The validation runs and pushes the three row types to three different recordset destinations. When I enter the processing data flow task, I have three parallel trees processing each recordset saved in the previous task. I'm using a script component to generate the rows which are then sorted and merged with the production database to find existing records. Based on this, I split to an OLE DB command (running an UPDATE command) or OLE DB destination (to simply insert the records.)

In this particular case, all records are being updated and nothing is being inserted new. Two of the three trees will complete the sort but hang on the merge, split, and OLE DB command components. The other will do the same but also hang on the split.

In another case, I truncated each destination table before running the package and the package runs fine.

Are toes being stepped on in the data flow task and causing a deadlock?

Update: I removed the sort transformation and sorted the rows before pushing them to the recordsets and I still get the same results.

If you were getting a deadlock then I think SQL Server would capture this and throw an error. That's not what's happening here. Its more likely that a lock is occurring. Use sp_who2 and sp_lock to determine if blocking is occurring.

-Jamie

|||

A common issue when your modifying the same table with multiple paths in your data flow is that you will have a deadlock on the destination table. To resolve:

1) choose "Fast load" and "Don't lock table" in the OLEDB Destination

2) ensure that you don't have a race condition between the paths, i.e. that the updates depends on the inserts _in the same run_ have reached the DB before the updates.

Cheers/Kristian

|||I checked the sp_who2 stored procedure and nothing was in the BlkBy column.
Fast load is being used and I chose "don't lock table" but that didn't fix the issue.
The updates don't depend on the inserts. I merge the input with production on keys and split on NULL values in production.

I ended up splitting the three trees into three data tasks to see if that would fix the issue but I get the same results, only this time I noticed no all rows have left the merge. The merge is a left outer join on keys and in this case all records input are already in the database. So I'm joining 62k input records with the same number of (identical) production records and 9,937 rows leave the merge. Also, when running the package, that task remains yellow.
|||

The solution is pretty simple. Do the inserts and updates in seperate data-flows. You can use raw files to pass data between data-flows.

-Jamie

Package fails when run under SQL Server Agent

Hi,

I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.

I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."

I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\AFAReduction\AFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\\<anotherservername>\PCStatslive\CurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."

Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.

Please let me know why I might get this error, when the package runs OK on its own.

Many thanks,
Keith.

The jobs running with the sql agent usually run with the agent's service account identity (local system or Network service).

This user must have permissions to open your Access DB files (NTFS security etc included).

When you run it from SQL mgmt studio it is running using your own credentials...

Hope this helps.

|||In addition to Zoran's advice, take a look at this KB article which describes common problems with running in Agent, and how to troubleshoot them:
http://support.microsoft.com/kb/918760|||

i have a similar kind of situation As a inidividual package or executing through the command line the package runs fine but when i try to execute it through the Agent it fails.

Executed as user: SEMASTER\Administrator. The package execution failed. The step failed.

Any help

Thanks,

Jasmine

|||

Hi again,

Thanks for your replies. You could've knocked me down with a feather - I came in this morning and the SQL Agent job that runs at 6 AM had succeeded! Tried manually and its all working fine! I've changed nothing, I have the Job History to prove that the last test I did yesterday failed, and everything since 6 AM today has succeeded.

The only thing I can guess is that there was some problem with the Access database itself, which cleared overnight. I'll keep an eye on this.

Thanks again,

Keith.

Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
ColinMost probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52=A0pm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> > Most probably, its the permissions given to the userid that is used to
> > call the job.
> > either case, What does the history log say? (right click on the job
> > and select "view history")
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 =A0 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error =3D -2147467259 (80004005) =A0 =A0 =A0Err=or
> string: =A0The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source: =A0Microsoft OLE DB
> Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 =A0 =A0Help contex=t: =A00
> Error Detail Records: =A0 =A0 =A0Error: =A0-2147467259 (80004005); Provide=r
> Error: =A0444 (1BC) =A0 =A0 =A0Error string: =A0The driver returned invali=d (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source:
> Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 ==A0 =A0Help
> context: =A00 =A0 =A0 =A0 =A0 Error: =A0-2147467259 (80004005); Provider E=rror: =A00
> (0) =A0 =A0 =A0Error string: =A0Cannot find all files in data path =A0 =A0= =A0Error
> source: =A0Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help
> file: =A0 =A0 ... =A0Process Exit Code 2. =A0The step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and =A0uses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
=2E....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin

Package fails when I use ODBC connection (Fails on SQL Server Agent, OK in Visual Studio)

I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.

Configuration:

SQL Server Agent on a 32Bit server.

The ODBC connection configuration in available on System DSN on this server.

The user of Server Agent have full access (Admin).

Connect Manager Provider: ".Net Providers\Odbc Data Provider"

SQL Server version: 9.0.3042

Error Message:

Executed as user: TEKCON\tcadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.

I created a .bat file with this instruction and It's run well:

dtexec /f "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Integration Services Project\testcom.dtsx"
pause

Why it's not running with SQL Server Agent?

Hi Daniel,

I've not seen this with ODBC sources before but I don't use a lot of ODBC sources. Based on your description of the problem I suspect the privileges on the SQL Agent account. If possible, log into an SSIS development workstation with the SQL Agent account credentials and try the package in Visual Studio. If it executes, then my suspicion is incorrect.

Hope This Helps,
Andy

|||

Thanks Andy,

I tried your suggestion: I logged into the SSIS development workstation with the same user than the SQL Server Agent. It's running with Visual Studio but fail in the SQL Server Agent.

Bye!

Daniel|||What type of step are you using in SQl Server gant job? Did you try using CmdExec type with the same command line you indicated in your previous post?|||

Hi Daniel,

You may want to try the following test: Change the package ProtectionLevel property to something containing the word "Password" (EncryptAllWithPassword, EncryptSensitiveWithPassword) and supply a nice strong password in the PackagePassword property. Save the changes.

Execute the package using the DTExecUI utility. You will have to supply the password to do so. If this succeeds, schedule the package execution using a SQL Server Integration Services Package job step type. Again, you wll need to supply the package password.

If this does not work, please copy the error and paste it in your response.

Hope this helps,
Andy

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manually
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
Thanks
Two possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manuall
y
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
ThanksTwo possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package "MyPackage" has been cancelled.

Hi,

I have scheduled a package "MyPackage" in a SQL Server Agent Job. It runs periodically with success.
Sometimes the package status is shown as "Cancelled" what led me to assume some coincidences.
So I took a look at the eventlog which contains the following message:
Event Type: Information
Event Source: SQLISPackage
Event Category: None
Event ID: 12290
Date: 30.01.2006
Time: 08:00:42
User: NT AUTHORITY\SYSTEM
Computer: MYSERVER
Description: Package "MyPackage" has been cancelled.

I assume that the problem occurs when a user locally logs off from the Server where the SSIS-service is running.

Why does this happen? The Service runs under the local system account and so does the SSIS Job.

Can anybody help here?
THX
FridtjofIf you think it is a user logging off the system, can you reproduce this in a controlled manner? I do not see why this should be the cause. Unfortunately I don't know why this happens either. Have you looked in the SQL Server Agent job history? have you tried adding some logging to the package and see what that shows? The CmdExec job step may give different output to the SSIS job step type, but make sure you set the job step output file.|||I have the same issue. Does anyone have a resolution?|||This is a bug in SSIS, and you've correctly identified the cause - a user logoff may cause this to the packages being executed.

We've recently got a bug report and currently testing a fix for this issue. Expect the fix in SP2 release.

Package "MyPackage" has been cancelled.

Hi,

I have scheduled a package "MyPackage" in a SQL Server Agent Job. It runs periodically with success.
Sometimes the package status is shown as "Cancelled" what led me to assume some coincidences.
So I took a look at the eventlog which contains the following message:
Event Type: Information
Event Source: SQLISPackage
Event Category: None
Event ID: 12290
Date: 30.01.2006
Time: 08:00:42
User: NT AUTHORITY\SYSTEM
Computer: MYSERVER
Description: Package "MyPackage" has been cancelled.

I assume that the problem occurs when a user locally logs off from the Server where the SSIS-service is running.

Why does this happen? The Service runs under the local system account and so does the SSIS Job.

Can anybody help here?
THX
FridtjofIf you think it is a user logging off the system, can you reproduce this in a controlled manner? I do not see why this should be the cause. Unfortunately I don't know why this happens either. Have you looked in the SQL Server Agent job history? have you tried adding some logging to the package and see what that shows? The CmdExec job step may give different output to the SSIS job step type, but make sure you set the job step output file.|||I have the same issue. Does anyone have a resolution?|||This is a bug in SSIS, and you've correctly identified the cause - a user logoff may cause this to the packages being executed.

We've recently got a bug report and currently testing a fix for this issue. Expect the fix in SP2 release.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.