How to execute a SSIS package from VS 6.0?

Dear all,

I wrote this post in another category but the answer did not satisfied so that I'd like to know if really anyone has ever used or experienced with this possibility. No by .Net language rather than Vb 6.0 or ASP or even instanciacing DMO library.

Thanks a lot for your support,

According this link http://support.microsoft.com/?kbid=817248 it seems possible but I haven't idea if possible keeping in mind that it has been made by yourself no for others... So that, I mean, these assemblies comes along with Sql Server 2005 installation.

sql

How to execute a SSIS dtsx package from an asp.net 2.0 application?

I have a SSIS package that I want users to be able to execute by clicking a button on an a web page. The package does not require any parameters to be passed to it. Previously I've executed DTS packages without any problems but after a fair bit of investigation and trawling the net I've not found a way to do this successfully with SSIS. Some code I've tried -

 Dim app As New Application() ' ' Load package from file system ' Dim package As Package = app.LoadPackage("c:\ssis\Package.dtsx", Nothing) 'package.ImportConfigurationFile("c:\ExamplePackage.dtsConfig") 'Dim vars As Variables = package.Variables 'vars("MyVariable").Value = "value from c#" Dim result As DTSExecResult = package.Execute() lblResult.Text = "Package Execution results: {0} " & result.ToString()

All I get is a message 'Failure'.

Does anyone have an example of how to do this?

Your requirement is different and you are doing in wrong way. The mentioned code run the package on the same machine.

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1801974&SiteID=1

|||

ok I've gone for option number 3 on your first link which uses a webservice to execute the package and then I can run the webservice through my asp.net page.

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

This is my webservice - the example taken from that page,

<WebMethod()> _Public Function LaunchPackage( _ByVal sourceTypeAs String, _ByVal sourceLocationAs String, _ByVal packageNameAs String)As Integer'DTSExecResultDim packagePathAs String Dim myPackageAs PackageDim integrationServicesAs New Application' Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName)Select Case sourceTypeCase"file"' Package is stored as a file. ' Add extension if not present.If String.IsNullOrEmpty(Path.GetExtension(packagePath))Then packagePath =String.Concat(packagePath,".dtsx")End If If File.Exists(packagePath)Then myPackage = integrationServices.LoadPackage(packagePath,Nothing)Else Throw New ApplicationException( _"Invalid file location: " & packagePath)End If Case"sql"' Package is stored in MSDB. ' Combine logical path and package name.If integrationServices.ExistsOnSqlServer(packagePath,".",String.Empty,String.Empty)Then myPackage = integrationServices.LoadFromSqlServer( _ packageName,"(local)",String.Empty,String.Empty,Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case"dts"' Package is managed by SSIS Package Store. ' Default logical paths are File System and MSDB.If integrationServices.ExistsOnDtsServer(packagePath,".")Then myPackage = integrationServices.LoadFromDtsServer(packagePath,"localhost",Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case Else Throw New ApplicationException( _ "Invalid sourceType argument: valid values are'file', 'sql', and 'dts'.")End Select Return myPackage.Execute()

and my asp.net page

Protected Sub btnBuildDW_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnBuildDW.Click ExecutePackage()End Sub Protected Sub ExecutePackage()Dim launchPackageServiceAs New SCCBuildDW.SCCBuildDWDim packageResultAs Integer Try packageResult = launchPackageService.LaunchPackage("file","c:\ssis","Package")Catch exAs Exception' The type of exception returned by a Web service is: ' System.Web.Services.Protocols.SoapException lblResult.Text ="The following exception occurred: " & ex.MessageEnd Try lblResult.Text = packageResult.ToString'CType(packageResult, PackageExecutionResult).ToString ' Console.ReadKey()End Sub Private Enum PackageExecutionResult PackageSucceeded PackageFailed PackageCompleted PackageWasCancelledEnd Enum

I'm getting PackageFailed. The webservice runs and returns 1 - which must be PackageFailed.

Any ideas what I'm doing wrong? The webservice is using windows authentication and the folder the package is in has Everyone Full Control.

|||

I've made some progress. It was failing due to permissions, I've altered these and can now execute a package containing a stored procedure. However my original package runs a stored procedure and then an Analysis Services Task. This still fails - I've checked the user is a member of the role that has access to run the task.

Is there something else I need to do?

|||

I'm still stuck on this - can anyone offer any help?

|||

I have this working now on my local machine. As soon as I move it to the live server I get PackageFailed when I attempt to run it. Has anyone experienced similar problems?

Given the amount of information on the internet I'm beginning to think I'm the only one who wants to execute SSIS in ASP.NET!

|||

i have similar issues too. maybe running ssis dtsx package using web services is not a good idea at all. especially if we run into so many authentication issues.

what about using sql server agent to run ssis package!

|||

I'm now using a stored procedure to execute the package and this works without problems.

Details of my code can be found here -http://forums.asp.net/p/1149972/1873190.aspx#1873190

|||

I'm also having the same problems you have described. I don't have the option of executing from a stored procedure.

I noticed that when I use the <identity impersonate="true"> and specified the username and password, that I got much further, but the package still fails.

I'm guessing that I'll probably end up adjusting the permissions on certain directories and so forth until this works.

|||

Hi all,

this might be useful.

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1518271

I tried using this and it works.

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks
|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a saved package?

Sorry for such a simple question -

I ran the Import data wizard and chose to save the package to sql server. Now I would like to execute that package I saved again, but I can't find where to do it in the management studio. Help!

Open SQL Server Management Studio - Select "Integration Services" in the Connect option.
Expand "Stored Packages" - Expand "MSDB".

You will find your saved package there.

Thanks,
Loonysan

How to execute a Package?

Hallo

I created my first Package and i am not able to deploy it in my SQL server.

I created a Development Utility and in the OutputPath i got a *.dtsx and a *.SSISDevelopmentManifest file.
When i run the packaage (*.dtsx) it works but when i try to install the package with *.SSISDevelopmentManifest it just genereates a folder in the selected Folder
(...\Microsoft SQL Server\90\DTS\Packages\) and no more.

What am i missing?

All the "Deployment" does is copy the *.dtsx files to the file system or MSDB on the target SQL server. You apparently selected "file system". It did exactly what it was suppose do.

Now you need to connect to the "Integration Services" on the server and look under "File System" and you will see the SSIS packages.|||

Hi soanfu,

When using the Deployment Utility, a directory is created in the Microsoft SQL Server\90\DTS\Packages\ folder. This happens regardless of where your package gets deployed. If the package gets deployed to the file system, the dtsx file will be copied into this directory. If you deploy to SQL Server, the directory is created but remains empty.

As someone else pointed out, connecting to the Integration Services instance will allow you to view all packages deployed to a server - both file system and SQL Server. To connect, open SQL Server Management Services. In the Object Explorer, click the Connect button. Select Integration Services and log in. You should be able to view packages stored to the SQL Server under the Stored Packages\MSDB folder.

Hope this helps,
Andy

|||

Thanks for the tip!

I didn’t know that there is a “Integration Services".

But know I have one more question.

In “Sql Server 2000” we could program the execution of DTS Packages with the option Tasks. How can I do it in SQL Server 2005?

|||You mean, to schedule it? You could use SQL Server Agent for that.

How to execute a DTS Via a stored procedure

Hi I have created a DTS package, it runs well, but i would like to execute it in a storeprocedure

Is this possible and if so where do i start from, I have looked all over for it tho every where i look creates a DTS from a store procedure.

I need desperate help

Thanks in advanvcehere is an excellent solution for you

http://p2p.wrox.com/topic.asp?TOPIC_ID=16647

How to execute a DTS Package from inside a job

Good day!

Just wanted to ask how can I execute a DTS package from inside a scheduled job.

I have 3 DTS Package, so instead of scheduling the 3 package separately...I created a job to execute the 3 packages in a single scheduled time.

My syntax inside the command window is:
EXEC DTSPackageName

But its not working.
Any help is greatly appreciated.
Thanks.you can rightclick the package and schedule it.

or go to run --> dtsrunui --> advanced and generate the code to be put into the SQL Job. Do this for each package and then just copy and paste each of the code into the job steps.|||Hi!

I just did what you instructed me. But a syntax error occurred.
This is what i've pasted in the job step.

DTSRun /S "SQL2K-VBP" /N "FFPJLC_TO_CMS" /G "{7DE00045-4E3C-445D-A0B7-BA95370EB072}" /W "0" /E

Thanks for taking the time to answer my questions.

God bless. :)|||the job step is operating system command

run the dtsrunui and schedule the package, it will create the job automatically with the package you choose. then look into the job step to see how it is coded.|||DTSRun /S "SQL2K-VBP" /N "FFPJLC_TO_CMS" /G "{7DE00045-4E3C-445D-A0B7-BA95370EB072}" /W "0" /E

God bless. :)
Look up DTSRun in Books OnLine. When you set up the job step, make it an "operating system command". Here is an example (/E means trusted login
i.e. Windows authentication):

DTSRun /S ServerName /N "DTS Job Name" /E|||Hi!

Thank you so much guys for helping me out about this DTS Package scheduling. The job is working well now.

God bless. :) ;)

How to execute

I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.

Can someone please give me a basic DTEXEC example?

Thanks in advance !Moving to the SSIS forum|||

cheers Louis.

To the OP, perhaps this will help:

Online Beginner Resources
http://blogs.conchango.com/jamiethomson/archive/2007/01/30/SSIS_3A00_-Online-Beginner-Resources.aspx

-Jamie

How to excute package in sequence

How to excute package in sequence . i have an requirement like this the package should execute in a sequence

1.PKG1

2.PKG2

3.PKG3

and in some case PKG1,.PKG2,.PKG3 shoule run parallel.

Thanks & Regards

Jegan.T

Jegant wrote:

How to excute package in sequence . i have an requirement like this the package should execute in a sequence

1.PKG1

2.PKG2

3.PKG3

You know what the Execute Package Task is right? Well you can control the execution of these 3 packages from another package.

1. Put 3 Execute Package tasks into a new package. Point them at your 3 packages

2. Put a precedence constraint from Execute Package Task for PKG1 to Execute Package Task for PKG2

3. Put a precedence constraint from Execute Package Task for PKG2 to Execute Package Task for PKG3

Jegant wrote:

and in some case PKG1,.PKG2,.PKG3 shoule run parallel.

Well in this case...

1. Put 3 Execute Package tasks into a new package. Point them at your 3 packages

2. Drop an empty sequence container onto your new package

3. Drag a precedence constraint from teh sequence container to each of the three Execute Package Tasks.

-Jamie

|||

Hi Jamie

Thanks for your reply . ill try this and get back to you.

Jegan.T

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

sql

How to enable or disable a task programmatically using Script Task

I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.

Now I want to add a functionality :

If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop

if failure mark all tasks at enable and start extracting data and continue further with execution.

I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.

Jatin,

In DTS you would disable the task but that is not how you do things in SSIS. Instead you can use conditional precedence constraints to decide whether to execute a particular task or not. There's a good demo of this here: http://www.sqlis.com/default.aspx?306

-Jamie

|||

Jamie,

Thank you for explaining the same with an example.

Jatin

sql

How to enable or disable a task programmatically using Script Task

I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.

Now I want to add a functionality :

If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop

if failure mark all tasks at enable and start extracting data and continue further with execution.

I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.

Jatin,

In DTS you would disable the task but that is not how you do things in SSIS. Instead you can use conditional precedence constraints to decide whether to execute a particular task or not. There's a good demo of this here: http://www.sqlis.com/default.aspx?306

-Jamie

|||

Jamie,

Thank you for explaining the same with an example.

Jatin

How to embed SQL Management Studio

I'm trying to embed SQL Management Studio in an installation package. Although I can find information on how to embed SQL Server Express I can't find instructions for the Management Studio express, although the web site says it's possible. Can anyone help?

Karen

Please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Check for an answer to your post in Setup and Upgrade

How to dynamically pull data for the past month?

I have a query that I want to schedule as a DTS package and have it run on
the first of every month to pull data for the previous month. How can I set
the SQL statement to determine what the last month was and use that for the
query parameters?
Thanks in advance for your help!
Isaac WeathersThe last day of the previous month is
select dateadd(dd, -(datepart(dd,getdate()) ), getdate())
I didn't test this but it gets the current day of the month (say the
12th, ) , then subtracts that many days from the current date, leaving you
at the last day of the prior month...You can then take that date and
subtract the the day -1 from that date, giving you the first day of the
month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I
> set
> the SQL statement to determine what the last month was and use that for
> the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>|||'====yyyymm format
if len(Month(DateAdd("M", -1, Date()))) = 1 then
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) & "0" &
Month(DateAdd("M", -1, Date()))
Else
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) &
Month(DateAdd("M", -1, Date()))
End If
'=====mm/dd/yyy format
DateString = Month(DateAdd("M", -1, Date()) ) & "/01/" &
DatePart("YYYY", DateAdd("M", -1, Date()))|||This will do the trick:
DateAdd("m",-1,
CAST(CONVERT(nvarchar(2), Month(GetDate()))+ '/1/' +
CONVERT(nvarchar(4), Year(GetDate()))
AS SmallDateTime))
AS FirstDayOfLastMonth,
CAST(CONVERT(nvarchar(12), GetDate() - Day(GetDate())) AS SmallDateTime)
AS LastDayOfLastMonth,
GeoSynch
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I set
> the SQL statement to determine what the last month was and use that for the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>

How to Dynamically Generate FileName using SSIS

Hi

I have generated a ssis package that creates a text file based on a query retrieving records from the table.

Now i want the file name to be appended with the date and time of package execution. So the filename will be something like ; Filename_MMDDYYYY.txt.

How can i do this with the help of SSIS?

Anyone having an idea would be of great help.

Regards,

Salman Shehbaz.

Have a look at expressions.|||

for me the following link did the magic;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=932686&SiteID=1

how to dynamically change source text file in sql server dts

Hi ,

I have a table (source file) where all sourse file(its all text file) name and location stored. i have a dts packeage, through this package all the sourse file will upload. So I need to dynamically change the source file name in dts packages.

How i can do it?

Can any body plz help me out!

Thnaks

SAndipan

I just did it, by using dynamic property.

thanks

How to do XML Configuration With Different Connection Manager in SSIS

Hi

I want know how to config SSIS Package With different connection Manager.

Sample .

When Design time i gave The Connection Manager(Devlopement Server) Name : STG1.XXXSchema

Server Name STG.ssss

Password : xxxxx

I make the The XML configuration file .

after deploy the Package i want from Development Server to Production Server . so i will change the Package Config File Open wiht Notepad .

Server Name STG.qqq

Password :xxxxxx

after Edit Connection String I run the Package . It Always Taking Whatever igave the Connection String When Design time only .is not taking what connection string edit hte in the Package Config File. Please Any one give the Solution .

How we can Change the connection string in XML Config File .Whne I tested . It always taking what ever we given in the Desing Time .

Thanks & Regards,

Jeyakumar .M

The whole point of configurations is that it should work like this, changing the file, means that the new value is read.

Obvious things to check are that the config file is in the correct location and is being read. When you run a package, if it cannot find the config file, it will raise a warning event. Depending on how you execute this may not be visible, but you can easily turn on logging to ensure you do capture this information. What warning events do you get?

You can also check that the package is using configuration correctlyon development, by looking in the log. You shoudl see an OnInformation event like this-

The package is attempting to configure from the XML file "E:\MyConfigdtsConfig".

If you cannot see this on development, then there is obviously a problem with the way you have setup the configuration to start with.

|||

Hi thanks for your reply message. Hi i need help from yourside. how to enable the Log or how to view the log. can you send me the Detail . what ever ur previuos answer . how can i view the log. or how to create the log and see the information .

Thanks & Regards,

Jeyakumar.M