Call a DTS Package from another DTS Package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Call a DTS Package from another DTS Package

Author

Topic

nd
Starting Member

10 Posts

Posted - 2011-08-30 : 11:06:29

Hi,

I need to generate few tables at run time, I can not stick with no of tables(Variable no of tables). After that I want to send the data of these files in different text file respectively.

I want to create a simple package to transfer data from single table to single text file.

After I want to execute this package multiple times from my parent package with parameters tableName, fileName and filePath.

My questions are:
1: How can I execute another package from my parent package?

2: How can I pass parameters to the new child package?

3: How can I create child package which can use these parameters supplied by parent package?

4: Can I use previously defined database connection from the same parent package for calling new package or any operation in sql script task?

5. Please use windows authentication

Thanks in advance.

Nee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 11:11:54

1. You can call another package using execute package task
2.you can declare a variable in parent and send value through it

see

http://iman77.blogspot.com/2008/05/ssis-passing-parameters-from-parent-to.html

3. you need create child package in BIDS in same way you created parent package. it will just have a variable to receive value passed from parent

4. you can provided you store the connection string in a configuration file

5.it uses windows authentication

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nd
Starting Member

10 Posts

Posted - 2011-09-01 : 02:33:20

Thank you for your response.

Its ok with step 1 and 2, but how can I use that variable in my child package.
My child package has one connection object and one output file object with a transformation task.

Now do you want me to use Sql Script task to use values of those variables?
So how can I use these values to set Source table name and Destination file name and file path in Data Transformation task, and how can i set properties of output file object using these variables?

Nee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 02:35:09

quote:
Originally posted by nd

Thank you for your response.

Its ok with step 1 and 2, but how can I use that variable in my child package.
My child package has one connection object and one output file object with a transformation task.

Now do you want me to use Sql Script task to use values of those variables?
So how can I use these values to set Source table name and Destination file name and file path in Data Transformation task, and how can i set properties of output file object using these variables?

Nee

you can map various properties of your task to take values from variables using dynamic properties task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nd
Starting Member

10 Posts

Posted - 2011-09-02 : 07:25:32

Thanx Vishakh,

I can not use Execute Package task, because I have to execute my child package multiple times at run time.
I am using a ActiveXScript task.

I am getting one problem here while executing the script at lines

Dim DTSPack
DTSPack = CreateObject("dts.package")
DTSPack.LoadFromSQLServer "Server","","","","","","","child_package"

Error is :
Error Code: 0
Error Source: Microsoft VBScript runtime error
Error Description: Object doesn't support this property or method: 'DTSPack.LoadFromSQLServer'

Can anyone help me out please?

Nee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 07:33:20

why do you need to execute package multiple times?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nd
Starting Member

10 Posts

Posted - 2011-09-02 : 10:31:50

I have some data related to few clients, I have to generate text files for them separately. No of clients depends on the no of customers we interacted in a particular month.

Nee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 12:47:53

why not put them in a loop and generate files. they will be of same structure rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nd
Starting Member

10 Posts

Posted - 2011-09-03 : 05:12:24

Sounds very good, yeah they have same structure, how can I do that? I will appreciate code snippets also if possible.

Nee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-03 : 05:33:04

you can implement loop using activex script like below

http://sqldts.com/246.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nd
Starting Member

10 Posts

Posted - 2011-09-03 : 05:47:33

I dont know how to create text files and load data into text files from the tables in SQL Server.

Nee

nd
Starting Member

10 Posts

Posted - 2011-09-04 : 10:38:07

Thanks Visakh, Finally I have done that using loops. Thank you again

Nee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 12:56:18

WC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources