Executing packages in a specific sequence

I am facing some issues while working on SSIS in VS2005,

The Scenario is we have 35 excel files from which we have to import data

to Sql 2005.

We have to execute these packages in a specific sequence so we are not

using For Each Loop Control.

Now the Problem is this that we have 35 executables as an output that is

corresponding to each package, and from each executable we can Change

excel path and Sql Connection String.

The problem is that one has to open each executable to set the path; we

wanted to keep them Configurable like , as if we can have any variable

define in the packages for path, and in the packages we use that variable

and append the XLS file name to it like ..

@.PathVariable+ test.xls

And from the Configuration file at run time we can set the path to this

variable like

@.Path Variable="C: /Windows/ Test Folder/"

And then executable picks the path from there.

Is there any possibility to implement any thing similar, we have tried but

its not working, any help in this regard will be really appreciating.

Its really urgent, I will wait for feedback on this.

Yeah you can do that. just use an expression on the ConnectionStrig property of the connection managers. This should give you some clues:

SSIS Nugget: Dynamically set a logfile name
(http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx)

-Jamie

Executing Large SQL statements

Hello,

I am trying to import a very large amount of data into an SQL database. The data is in a format of SQL statements already - it is all in one large text file consisting of a number of CREATE TABLE X and INSERT INTO X VALUES().

The problem is that the amount of values being inserted into some of the tables is so large, that I am unable to open the .SQL file using query analyzer to run it, because the line-size limit for query analyzer is 64kb, whereas actual line-size in the file is in some cases in excess of 15MB.

I would appreciate any advice on how to get all this data into a managable format. I keep thinking that there simply has to be a way to execute these over-size SQL statements.

Thank you in advance!

-SergeyTry to open the .SQL file in NOTEPAD and scratch into manageble sizes for that statement.

Or use OSQL utility to execute that .SQL file.

executing DTS - DSN error...

Perhaps this has already been answered. If so, I apologize. I'm a
newbie at this stuff. I have a DTS package to import some Visual
Foxpro data. Yes, I said Visual Foxpro. :) The DTS package works
beautifully when I rightclick on the package and choose "execute." And
it works beautifully when I run dtsrun... at the DOS prompt. But it
doesn't work when I try to execute it with code in Query Analyzer.
I've tried exec master..xp_cmdshell 'dtsrun ...' And I've tried
scheduling the package as a Job, disabling the Job and then using
msdb..sp_start_job to run it. Neither of those work.

I get a "DSN not found and no default driver specified."

My DSN stuff looks OK to me, but perhaps some of that is wrong. What
does the DSN have to look like in order for the code in Query Analyzer
to find it? If the DSN is screwed up, why would it work OK when
executed from some places, but not in others? What am I missing?
Any help would be much appreciated. :) thanks...
-emilyAlso, I should clarify. Everything is local. Everything's on my
laptop on my coffee table. No network. SQLServer is local, Foxpro
data is local.|||OK, false alarm, I figured this out myself. Well, I didn't really
figure it out, but I did get it to work. If I set up the FoxPro DSN as
a System DSN, then it works. User DSN doesn't. I have no idea why.
I'll continue to play around with it. Next time I won't post here
until I'm absolutely sure that I've tried absolutely everything. :)|||emily (ehart624@.hotmail.com) writes:
> OK, false alarm, I figured this out myself. Well, I didn't really
> figure it out, but I did get it to work. If I set up the FoxPro DSN as
> a System DSN, then it works. User DSN doesn't. I have no idea why.
> I'll continue to play around with it. Next time I won't post here
> until I'm absolutely sure that I've tried absolutely everything. :)

I have an idea. If SQL Server runs as Local System it is not likely
to find User DSN for your user. Thus it must be a system DSN.

Recall that when you run from QA, it's SQL Server that runs the
package.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

execute ssis and loggin with sqlserver provider cause error

Hi,

I execute my ssis from BI without problem (debug mode), but when I import into SQL server and try to run with logging on sqlserver provider always my package failure.

If option of logging is empty all works fine (No one logging method is active).

There is some permission to set for this option?

I use into SiSS ole db native client connection.

Hope some one can help me.

Alen Italy

This belove is the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "La transazione distribuita è stata
completata. Integrare questa sessione in una nuova transazione o nella
transazione Null.".

How exactly are you running the package, and what security is the connection manager used by the log provider set to use?

|||

I create the package on server log-in with the administrator user anda connect the Sql Server management with a windows autentication...so are the same level user.

With no looggin method works fine...with no one error.

I running my package from SQL server management studio...under the folder STORED PACKAGES > MSDB > MYFOLDER > MyPack and right clic on "run package".

Below the connection string:

DB1
Data Source=SQLTEST;Initial Catalog=NEW_LISTINI;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

DB2
Data Source=SQLTEST;Initial Catalog=NEW_PANGEA;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

EXCELL FILE
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\manny-slave\Fornitori\Listini\Altri\\Ready_4_Import\ePRICE.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;MAXSCANROWS=8;";

I translate in english the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "The distributed transaction has been completed. To integrate this session in one new transaction or the Null transaction"

The comand line -

/DTS "\MSDB\DTS to SSIS\NEW_LISTINI_IMPORTA_XLS" /SERVER SQLTEST /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"SQLTEST.NEW_LISTINI" /SET "\Package.Variables[arrIDsources].Value";5

_ after many test __

I try many combinations of transaction option.

If I set package "required" works with logging on DB server, but If active "required" on two containers alwails falls.

Seems been not another way for loogging into SQLSERVER.

Or SET all transactions of containers to "supported" or set "Required" at packege level...

If somone heve another vision...here I'm to hear.
Alen

Execute package from query analyzer

I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from
>>Access mdb and I have saved it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Local Packages
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>> If you create a job for your package you can :-
>> USE msdb
>> EXEC sp_start_job @.job_name = 'Nightly Backup'
>>
>> If not you can utilise DTSRUN :-
>> To execute a DTS package saved in the SQL Server msdb database, use:
>> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from Access mdb and I have saved
>>it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>>
>

Execute package from query analyzer

I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
"Ryan" <Ryan_Waight@.nospam.hotmail.com>
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a
job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx
.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Lo
cal Packages
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>