Having problems with variables

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

Thank you.

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

-Jamie

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !

Havent been able to execute a package on the server

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

|||

Luis Esteban Valencia Mu?oz wrote:

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

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

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

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

Hi Luis,

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

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

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

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

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

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

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

Hope this helps,

Andy

Handling errors when using ExecuteNonQuery

Let's say that I have a StringCollection named scripts that contains a bunch of DDL statements I want to execute. So I execute the statements using ExecuteNonQuery as follows:

ExecuteNonQuery(scripts)

If one of the DDL statements contained in the StringCollection returns an error, it means that all subsequent statements will not be executed. Even if I use try catch as follows:

try

{

db.ExecuteNonQuery(scripts);

}

catch (Exception ex)

{

Console.WriteLine("Error while recreating objects. See errorlog for more details\r\n");

tw.Write("Error while recreating objects: {0}\r\n", ex.GetBaseException().ToString());

tw.Flush();

}

Is there any way to keep executing the subsequent statements in the StringCollection even if some of the DDL statements return an error?

If you send them one at a time, handle the error that occurs, then send the next script, you could then continue after an error. DDL errors, however, always make me want to stop, because DML usually depends on the DDL being correct.

|||

Thanks for your answer Allen,

I tried what you suggested but I noticed that none of my stored procs kept their settings. For example:

I script my proc as follows:

if (u.Type == "StoredProcedure")

{

ScriptingOptions so = new ScriptingOptions();

StoredProcedure o = (StoredProcedure)svr.GetSmoObject(u);

so.SchemaQualify = true;

so.SchemaQualifyForeignKeysReferences = true;

so.Permissions = true;

so.ExtendedProperties = true;

so.EnforceScriptingOptions = true;

so.NoCollation = true;

foreach (string os in o.Script(so))

{

sc.Add(os.ToString());

}

}

If I try to execute my statement as follows:

foreach (string s in scripts)

{

db.ExecuteNonQuery(s);

}

Then two statementswill be sent to SQL Server and the ANSI_NULL Settings will not necessarily be maintained:

first statement:

SET ANSI_NULLS ON

Second statement:

CREATE PROCEDURE...

The problem being that if the setting for my second connection is SET ANSI_NULLS OFF then my proc will be created with the wrong settings.

Handle Error

Hi everybody, i do
insert into client
select * from client
client table has pk when execute the query the follow error appear
Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
puede insertar una clave duplicada en el objeto 'EMPLEADO'.
Se termin la instruccin.
what can i do to obtain the complete message in bold to insert into a table
Any idea?
P.D. sorry my english is not good
Regards,
JorgeAre you using SQL Server 2005? If so, look up TRY..CATCH in Books Online,
and check out the ERROR_MESSAGE function. If you're in SQL Server 2000, you
might have to handle this on the client.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jorge Aguilar" <j.aguilar@.lpcsite.com> wrote in message
news:uM%239FoIcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi everybody, i do
> insert into client
> select * from client
> client table has pk when execute the query the follow error appear
> Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
> Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
> puede insertar una clave duplicada en el objeto 'EMPLEADO'.
> Se termin la instruccin.
> what can i do to obtain the complete message in bold to insert into a
> table
> Any idea?
> P.D. sorry my english is not good
> Regards,
> Jorge
>|||Jorge Aguilar (j.aguilar@.lpcsite.com) writes:
> Hi everybody, i do
> insert into client
> select * from client
> client table has pk when execute the query the follow error appear
> Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
> Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
> puede insertar una clave duplicada en el objeto 'EMPLEADO'.
> Se termin la instruccin.
> what can i do to obtain the complete message in bold to insert into a
> table
I'm not really sure what you are asking for, but if the question is
how to retrieve the text of the error message from SQL, the answer
depends on the version of SQL Server you are using:
SQL 2000 - you can't.
SQL 2005 you can use TRY CATCH:
BEGIN TRY
INSERT into CLIENT SELECT * FROM client
END TRY
BEGIN CATCH
INSERT errortable (msg, text, proc, line)
SELECT error_number(), error_message(), error_procedure(),
error_line()
END CATCH
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do you have a trigger on the table CLIENT that updates or inserts to the
table EMPLEADO?
Why are you inserting existing rows into client? It appears that you are
creating duplicates of every row, which is almost always a mistake. It will
cause many problems later.
Please explain what you are trying to do (and why) and provide the scripts
to create your tables, keys, and triggers. Specifically, we need the
scripts for CLIENTS and EMPLEADO.
Also, you might try posting to:
microsoft.public.espanol.sqlserver.administracion
The focus of the group is different, but language may not be as much of a
problem there, and this error should be known to them.
"Jorge Aguilar" <j.aguilar@.lpcsite.com> wrote in message
news:uM%239FoIcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi everybody, i do
> insert into client
> select * from client
> client table has pk when execute the query the follow error appear
> Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
> Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
> puede insertar una clave duplicada en el objeto 'EMPLEADO'.
> Se termin la instruccin.
> what can i do to obtain the complete message in bold to insert into a
table
> Any idea?
> P.D. sorry my english is not good
> Regards,
> Jorge
>

halt execution and wait for parameters

Hello,
does anybody know of a way to to have RS wait to execute (on access) until
the parameter fields have been filled?
thanks,
gregI've noticed this was a problem when I had default values for every
parameter. Try to remove default values.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> Hello,
> does anybody know of a way to to have RS wait to execute (on access) until
> the parameter fields have been filled?
> thanks,
> greg|||Thanks for the response. That is the same problem I'm having. I have to leave
defaults (8 fields) but the users change certian fields. thanks again good
to see others are having the same issues.
Greg
"Jason" wrote:
> I've noticed this was a problem when I had default values for every
> parameter. Try to remove default values.
> "greg" <greg@.discussions.microsoft.com> wrote in message
> news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> > Hello,
> > does anybody know of a way to to have RS wait to execute (on access) until
> > the parameter fields have been filled?
> >
> > thanks,
> > greg
>
>