how to schedule integration services package(SSIS) 2005 using Management studio?or is there any

Have anyone successfully accomplished scheduling integration services package using management studio? or is there any other way to do this?

i am scheduling the package to run from SQL SERVER Management Studio using SQL Server Agent,but it is not working.

Help is appreciated.Moving to SSIS forum from Bug Reports Forum.|||

devi_anitha wrote:

Have anyone successfully accomplished scheduling integration services package using management studio? or is there any other way to do this?

i am scheduling the package to run from SQL SERVER Management Studio using SQL Server Agent,but it is not working.

Help is appreciated.

Please provide more details about the error. In the mean time try seraching this forum

How to Schedule and Run a SSIS package

Hi

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

sql

How to Schedule and Run a SSIS package

Hi

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

How to schedule an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

How to run SSIS package through the command line?

Dear member,

Does any body know how to run SSIS package through the command line?

Thanks

Shamen

You can use DTEXEC from the command line to run packages.|||

Thanks Jwelch...I will try....

How to run SSIS package as a SQL Scheduled Job?

Hello:
I have a SSIS package that will import data from one DB to another. I would
like to set it up as a nightly job. When I did, it faied within couple
seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
that user is the SQL service account that SQL Agent uses.
Message
Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit Copyright
(C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:42 AM
Error: 2007-10-26 11:01:48.23 Code: 0xC0202009 Source: Copy Jbq Opr
Sec Tables from TRSDev to TRSDev0203 Connection manager
"SourceConnectionOLEDB" 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 'TRSNT\dev02-sql01'.". End Error
Error: 2007-10-26 11:01:48.23 Code: 0xC020801C Source: Data Flow
Task Source - GenTRSEmailAddr [1] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
method call to the connection manager "SourceConnectionOLEDB" failed with
error code 0xC0202009. There may be error messages posted before this with
more information on why the ... The package execution fa... The step
failed.
Am I missing something here?
Thank you in advavnce for your responses.
Chai> When I did, it faied within couple
> seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
> that user is the SQL service account that SQL Agent uses.
Did you add that Windows account to the SQL Server you try to access?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chai" <chai@.trs.state.il.us> wrote in message news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
> Hello:
> I have a SSIS package that will import data from one DB to another. I would
> like to set it up as a nightly job. When I did, it faied within couple
> seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
> that user is the SQL service account that SQL Agent uses.
> Message
> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit Copyright
> (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:42 AM
> Error: 2007-10-26 11:01:48.23 Code: 0xC0202009 Source: Copy Jbq Opr
> Sec Tables from TRSDev to TRSDev0203 Connection manager
> "SourceConnectionOLEDB" 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 'TRSNT\dev02-sql01'.". End Error
> Error: 2007-10-26 11:01:48.23 Code: 0xC020801C Source: Data Flow
> Task Source - GenTRSEmailAddr [1] Description: SSIS Error Code
> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> method call to the connection manager "SourceConnectionOLEDB" failed with
> error code 0xC0202009. There may be error messages posted before this with
> more information on why the ... The package execution fa... The step
> failed.
>
> Am I missing something here?
> Thank you in advavnce for your responses.
>
> Chai
>|||Tibor:
Yes, I tried what you suggested and still received the same error.
Chai
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
>> When I did, it faied within couple seconds & the error saying that Login
>> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
>> account that SQL Agent uses.
> Did you add that Windows account to the SQL Server you try to access?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chai" <chai@.trs.state.il.us> wrote in message
> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
>> Hello:
>> I have a SSIS package that will import data from one DB to another. I
>> would like to set it up as a nightly job. When I did, it faied within
>> couple seconds & the error saying that Login failed for user
>> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
>> uses.
>> Message
>> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
>> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
>> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
>> manager "SourceConnectionOLEDB" 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
>> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
>> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
>> Description: SSIS Error Code
>> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> AcquireConnection method call to the connection manager
>> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
>> error messages posted before this with more information on why the ...
>> The package execution fa... The step failed.
>>
>> Am I missing something here?
>> Thank you in advavnce for your responses.
>>
>> Chai|||Can you log in interactively using the TRSNT\dev02-sql01 account and execute your package?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chai" <chai@.trs.state.il.us> wrote in message news:%23JcE06%23FIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Tibor:
> Yes, I tried what you suggested and still received the same error.
>
> Chai
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
>> When I did, it faied within couple seconds & the error saying that Login
>> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
>> account that SQL Agent uses.
>> Did you add that Windows account to the SQL Server you try to access?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Chai" <chai@.trs.state.il.us> wrote in message
>> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
>> Hello:
>> I have a SSIS package that will import data from one DB to another. I
>> would like to set it up as a nightly job. When I did, it faied within
>> couple seconds & the error saying that Login failed for user
>> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
>> uses.
>> Message
>> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
>> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
>> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
>> manager "SourceConnectionOLEDB" 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
>> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
>> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
>> Description: SSIS Error Code
>> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> AcquireConnection method call to the connection manager
>> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
>> error messages posted before this with more information on why the ...
>> The package execution fa... The step failed.
>>
>> Am I missing something here?
>> Thank you in advavnce for your responses.
>>
>> Chai
>|||Is the SQL server agent start-up account a network account?
Especially when jobs are running across the network, use a network account
to start the SQL Server agent services.
"Tibor Karaszi" wrote:
> Can you log in interactively using the TRSNT\dev02-sql01 account and execute your package?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chai" <chai@.trs.state.il.us> wrote in message news:%23JcE06%23FIHA.1164@.TK2MSFTNGP02.phx.gbl...
> > Tibor:
> >
> > Yes, I tried what you suggested and still received the same error.
> >
> >
> > Chai
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
> >> When I did, it faied within couple seconds & the error saying that Login
> >> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
> >> account that SQL Agent uses.
> >>
> >> Did you add that Windows account to the SQL Server you try to access?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Chai" <chai@.trs.state.il.us> wrote in message
> >> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
> >> Hello:
> >>
> >> I have a SSIS package that will import data from one DB to another. I
> >> would like to set it up as a nightly job. When I did, it faied within
> >> couple seconds & the error saying that Login failed for user
> >> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
> >> uses.
> >>
> >> Message
> >> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
> >> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
> >> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
> >> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
> >> manager "SourceConnectionOLEDB" 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
> >> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
> >> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
> >> Description: SSIS Error Code
> >> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> AcquireConnection method call to the connection manager
> >> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
> >> error messages posted before this with more information on why the ...
> >> The package execution fa... The step failed.
> >>
> >>
> >> Am I missing something here?
> >>
> >> Thank you in advavnce for your responses.
> >>
> >>
> >> Chai
> >
> >sql

How to run SSIS package

How can I:

1) Create a Job in 2005 to run my SSIS project? Specifically syntax and where I would place the all in the job's properties

2) Can I run an SSIS package from the command prompt? what would be the syntax

You should read BOL as it has extensive information on these topics.

1) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/541ee5ac-2c9f-4b74-b4f0-13b7bd5920b0.htm

This is general info on agent and you would use the SSIS subsystem for running SSIS packages.

2) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

Matt

|||this is a dumb question but how do I run those paths to get to your URLs above?|||Open BOL and put the URL into the URL box.

how to run ssis in sql 2005

I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?? Integration Services isn't included with any edition of SQL Server 2005 Express, as far as I am aware. See http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx for further information. See the Integration and Interoperability section of the following URL, http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx, for more specific information. Andrew Watt [MVP] <vhrao@.discussions.microsoft.com> wrote in message news:fc88e4bb-cb03-4ba7-8d14-e19bd3a2fd39@.discussions.microsoft.com...I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?|||SSIS is not part of SQL Express, it is included in higher editions.

How to Run DTS Package from remote server with SQL 2005

I am new to SQL Server and need to understand how to run DTS packages (SSIS) in SQL 2005 from a remote server? In other words run them from another server from where the database is installed.

I am looking for any links on the subject, guidance on how to set this up and how to use it.

Regards,

Lee

You just have to have the ssis runtime installed on the system you want to run the packages from. As long as you can make a connection from that machine to your database machine you should be ok. And of course you also need the workstation components installed on whatever machine you're doing your dev work from.

If you want to store your packages in the msdb db you may run into a problem because the default is to use the default instance on the local machine to connect to the msdb. There's a file you can edit to change this but I can't remember it off the top of my head.

|||If I correctly understard the question, you have a server where you have installed SSIS and some packages, and you want to start these packages from a remote computer (client), right?

The usual way to set this up is to create Agent Job that will run the package, without any schedule - so it would not run by itself. Then when you need to run the package from the client, you execute Agent stored proc (via SQL) to start the Job.|||

Hi Mike,

Thanks for the information. I will share this with the other developers working with me on this.

Lee

sql

How to run encrypted child package from parent package

I am trying to figure out how to run an SSIS package that is encrypted but also has a child package which is encrypted. I am trying to run from the cmd line using dtexec and specifying DECRYPT <pwd> for the parent package but how do you specify for the child package.

Please help if you have seen such an issue before or you have a work around.

Thanks

Newbie

The Execute Package task has a PackagePassword property that you can set at design-time, or by a configuration at run-time.

How to run an exe in the SSIS scripting task

Hi,

Can anyone help me how to run an exe file in the scripting task.

I was trying the below code but it's giving an error.

Set wshShell = WScript.CreateObject ("WSCript.shell")
wshshell.run "c:\Test\ABC.exe", 6, True
set wshshell = nothing

Thanks,

rkn

Here's one way. You may want to check out the MSDN for any security risks, etc.

Dim procID As Integer

procID = Shell("""C:\Program Files\Microsoft Office\OFFICE11\winword.exe"" c:\temp\test.doc")

Larry Pope|||

I would be inclined to choose .NET's Process.Start method as the more language-neutral approach...but it's possible that this is what the VB Shell command calls anyway.

-Doug

|||I believe the Process.Start method has the limitation of not being able

to add cmdline parameters, which was included in the original posters

code.

If I'm wrong about the Process.Start method not being able to use

cmdline parameters, please post some code. I would love to be

able to use that rather than Shell.

Larry|||

I think what you are looking for is something like this:

System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo(Setting.PostProcessor,commandLineArgs);

startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;

startInfo.WorkingDirectory = "C:\\Test";

startInfo.UseShellExecute = false;

System.Diagnostics.Process process = new System.Diagnostics.Process();

process.StartInfo = startInfo;

process.Start();

Hossein Riazi

|||

The Start method also has other overloads suited for command line arguments, such as

Process.Start Method (String, String)

Starts a process resource by specifying the name of an application and a set of command-line arguments, and associates the resource with a new Process component.

-Doug

How to run a xmla script from an SSIS package

I have a xmla script that adds an role to a SQL Server 2005 Analysis Service
s
database (see below). How do I run that from an SSIS package ? Is there a
specific SSIS task that will do it ?
Here's the script:
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Ask</DatabaseID>
</ParentObject>
<ObjectDefinition>
<Role xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>Role</ID>
<Name>REDPEPPER</Name>
<Members>
<Member>
<Name>Everyone</Name>
</Member>
</Members>
</Role>
</ObjectDefinition>
</Create>
Thanks,
CraigHello CraigHB,
There is an "Analysis Services Execute DDL" task which should do the trick
Darren Gosbell - SQL Server MVP
blog: http://geekswithblogs.net/darrengosbell

> I have a xmla script that adds an role to a SQL Server 2005 Analysis
> Services database (see below). How do I run that from an SSIS package
> ? Is there a specific SSIS task that will do it ?
> Here's the script: <Create
> xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
> <ParentObject> <DatabaseID>Ask</DatabaseID> </ParentObject>
> <ObjectDefinition> <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <ID>Role</ID>
> <Name>REDPEPPER</Name> <Members> <Member> <Name>Everyone</Name>
> </Member> </Members> </Role> </ObjectDefinition> </Create>
> Thanks, Craig
>

How to run a xmla script from an SSIS package

I have a xmla script that adds an role to a SQL Server 2005 Analysis Services
database (see below). How do I run that from an SSIS package ? Is there a
specific SSIS task that will do it ?
Here's the script:
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Ask</DatabaseID>
</ParentObject>
<ObjectDefinition>
<Role xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>Role</ID>
<Name>REDPEPPER</Name>
<Members>
<Member>
<Name>Everyone</Name>
</Member>
</Members>
</Role>
</ObjectDefinition>
</Create>
Thanks,
Craig
Hello CraigHB,
There is an "Analysis Services Execute DDL" task which should do the trick
Darren Gosbell - SQL Server MVP
blog: http://geekswithblogs.net/darrengosbell

> I have a xmla script that adds an role to a SQL Server 2005 Analysis
> Services database (see below). How do I run that from an SSIS package
> ? Is there a specific SSIS task that will do it ?
> Here's the script: <Create
> xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
> <ParentObject> <DatabaseID>Ask</DatabaseID> </ParentObject>
> <ObjectDefinition> <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <ID>Role</ID>
> <Name>REDPEPPER</Name> <Members> <Member> <Name>Everyone</Name>
> </Member> </Members> </Role> </ObjectDefinition> </Create>
> Thanks, Craig
>
sql

How to run a console program in ssis?

I need to execute a console program and capture its output.

What's the best way to do it?

(no xp_cmdshell approach)

Thanks,

The Execute Process task is used for this. Michael Entin just posted about this: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

How to run "Execute Package" Task Conditionally in SSIS?

I have a boolean variable in my package. How to run "Execute Package" Task Conditionally using that variable?
For example if the variable is True, I want to run the Task else just skip this task and continue to the next one. The Control flow should continue after this task. How do I achieve this?

How to do the same for "Execute SQL" task?Lets assume this simple scenario:

Task 1 > Execute package (task 2) > Task 3

Lets change this for the desired behavior.
After Task1, have one arrow go to Execute package task, as above AND have another arrow go from Task 1 to Task 3 directly.

Now, right-click on BOTH the arrows and specify expression on each of them. One expression will be @.my_variable = true and another will be @.my_variable = false.

Depending upon value of @.my_variable, one of the paths will be taken.

HTH,
Nitesh|||This doe't help I tried it already. It breaks the flow. The control never reaches to Task3. I want to just skip Task2. In Both case I want to run Task3. When the condition is not met for Task2, the control flow breaks there. According to MS, that is by design. I have no idea to about how to achieve this. Any help will be appreciated.|||Ok, Let's try this. Have the control flow as follows:
Task 1 -> Task 2 -> Task 3
And another path directly from Task 1 -> Task 3
For Task 2, set the expression for "Disable" property based on the variable value you want to use to decide whether Task 2 should be executed or not.

If that variable is true, Task 2 will be disabled, if not it will remain enabled. Let me know if that lets you accomplish your scenario.

Thanks
Ranjeeta|||

See this thread:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=63760
K

|||

mci wrote:

This doe't help I tried it already. It breaks the flow. The control never reaches to Task3. I want to just skip Task2. In Both case I want to run Task3. When the condition is not met for Task2, the control flow breaks there. According to MS, that is by design. I have no idea to about how to achieve this. Any help will be appreciated.

Place task1 and task2 in a sequence container. Task3 should execute after the sequence container. You can put an expression on the precedence constraint between task1 and task2 to specify whether task2 should execute or not.

More details here: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1889.aspx. The bit in the "UPDATE" section at the bottom illustrates exactly what I have described here.

-Jamie

How to retrieve Global Variables in an ActiveX Script Task using VBScript in SSIS

I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:

==========================================

Function Main()

Response.Expires=-1

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False

objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing

End Function
=========================================

I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).

My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.

Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...

Michael

No answer on all your questions but stick with using the XML source as this is what SSIS is designed for.

While the XML source does not cater for a variable for the XSD path, it does allow you to set it via expressions. Essentially the same thing.

When the data flow is highlighted, see properties > Expressions. There is a [XMLSource.DataSchemaDefinition] property which you can set if a variable or a value built up from expressions.

Also, stay away from the Active X Script task...