Integrate more than one SSIS packages into one package

Hi

I'm new to SSIS field.

I'm importing data from flat files to sql server 2005 through SSIS packages.

I have around 30 packages which transfers data from flat files to corresponding database tables.

I want a single package that will run all the 30 packages by running that single package.

Like in a single stored procedure we can run multiple stored procedure, I want the same solutions for my packages.

Is there any method which can solve the above issue?

Please kindly guide me .

Its urgent. please reply soon.

Thanks in advance.

Hi priyanka,

You could use the Execute Package Task to call your other packages on one "mother" package. You can find it in your Toolbox under Control Flow Items.|||

Hello Jon;

Thanks a lot for your quick response.Your reply gave me a lot of knowledge, but still I'm in problem. By using Execute Package Task I can run any existing package, which is previously created.But I have Two queries.

1. It will run the package which contain the last flat file as data source, here my question is how to add a new flat file(since my flat file changes monthly) while running the package using "mother" package.I guess you got my point. Secondly,

2. By solving the point no.1 will not also solve my problem. As my requirement is , I want one package, on running this all other packages saved in one folder should be run automatically. I want to say that instead of running all packages one by one, I want to run them by one single package.

Thanks a lot again and eagerly waiting for your reply.

Thanks

Priyanka

|||

Hello Jon,

Sorry for disturbing again. I got the solution to point no-1. If possible help me for point no. 2, Where by running one mother package I can execute all other child projects.

Thanks

Priyanka

|||You can actually have them execute all at the same time by NOT connecting them to each other with the control flow arrow connectors. The packages will then execute all stand-alone tasks at the same time.

Try to experiment this first with 3 to 5 Execute Package Task objects. You'll see that they'll turn yellow (executing) all at the same time.

I hope I understood your problem correctly.|||

Thanks a lot. You are really a very nice person.

The above points solved my problem.Now I can run all the packages at a time.That was my requirement.

But it is fine if I'll run this in my machine. If I want to send the package to the client , who require to run the package to update the database at his end(He wants to run only one package. I can give him all the child packages and the mother package after running them at my end).But how he will be able to set the path for source flat file connection and destination sql server database connection.

In the Execute Package Task , is there any option where I can change the source and destination of each package, or is there any other way?

Thanks a lot again...

priyanka

|||

Priyanka,

To facilitate deployment in different environments, SSIS can use 'Package Configurations' that basically allows you to store package's property values outside of the package to be applied at run time; overriding the values given at design time. Such configuration values can reside in a table, XML file, environment variables, etc. I think using package configuration will solve the problem of deploying the packages at your client location. Search on this forum and the web and you will find a lot of good information for that.

|||Rafael is right on about package configurations.

You can find info on this here, here and here.|||Oh, and one more tip. If the "mother" and "children" packages are all in one folder, you may try to totally omit the absolute path of the child packages in their connectionstrings and leave just the file name. That would make the "mother" package search for the "child" package in the same folder as it is running.

Goodluck |||

Hello;

Package configuration not working.

In my project I 've one Mother package and 10 child packages.I've added the package configuration to all the child packages.
I ve created XML type package configuration.
In all the package configurations I've checked the Connection Manegers checked boxes.
But while I'm trying to run the Mother package, its throughing error as bellow

"Error: Error 0xC0012050 while loading package file "E:\Visual Studio 2005\TestPackage\PackageWithConfig\Integration Services Project1\Integration Services Project1\Divisions.dtsx". Package failed validation from the ExecutePackage task. The package cannot run."

Without using package configuration, its working fine.
Can you please tell me the entire flow of process with package configuration?

Thanks
Priyanka

|||

That is weird. It seems like you create a new set of children packages in a different location; make sure you are not missing something on that new set of files. Try to execute a single child package without calling it from the master package, just to check that is not something else.

There are a lot of posts and other good information about package configurations on line:

http://www.google.com/search?hl=en&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=SSIS+package+configurations&spell=1

|||Can you look for the message where the package fails in validation?|||

Thanks.

The issue has been resolved.Actually I was not concern about the security issue for password, that was missing in the dtsConfig file. By giving the password , it is now working fine.

Thanks to all of u.

|||

Is there any better approach to integrate packages into one package?

My target is to create a single package , by running this will run all the child packages.In the above process I've to change the flat file source path each time by searching it from the Xml file(Package configuration file) , which is a time consuming and boring process.I have a number of child files.So for each file searching for source path in Package configuration file is not an interesting approach.

Anyways I have to set the source path but is there any other easier and fast approach to achieve this.

Thanks.

|||

priyanka.dash wrote:

Is there any better approach to integrate packages into one package?

My target is to create a single package , by running this will run all the child packages.In the above process I've to change the flat file source path each time by searching it from the Xml file(Package configuration file) , which is a time consuming and boring process.I have a number of child files.So for each file searching for source path in Package configuration file is not an interesting approach.

Anyways I have to set the source path but is there any other easier and fast approach to achieve this.

Thanks.

You know you can create several copies of the XML configuration file and then simply copy whichever one you need to the "real" xml config file.... Then you don't have to spend time editing the files. Just create them once and copy whichever one you need at the time.

Integer to HH:MM:SS

Hi,
In SRS 2K, how can I take a field that is listed as an integer (say 60),
and make it look like 01:00:00 in the report? Is there a way to do this in
the Textbox Properties window using the Custom Format section?
Thanks.Found my answer in another thread:
=String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddSeconds(Fields!*Value))
"lucotc" wrote:
> Hi,
> In SRS 2K, how can I take a field that is listed as an integer (say 60),
> and make it look like 01:00:00 in the report? Is there a way to do this in
> the Textbox Properties window using the Custom Format section?
>
> Thanks.

Integer Parameter

I have a parameter field with a data type of int. I want the users to be able to select one or 'all' from the dropdown list. Is there a wildcard for a integer datatype?

Dropdown list example:

All Project Ids

1001

1002

1003

1004

In cases like this I will frequently use a NULL parameter to designate 'all'; can that work for you in this case? An example of this would be something like this:

declare @.example table (integerCol integer)
insert into @.example values (1001)
insert into @.example values (1002)
insert into @.example values (1003)
insert into @.example values (1004)

declare @.anIntegerParm integer
set @.anIntegerParm = null
set @.anIntegerParm = 1002

if @.anIntegerParm is null

select * from @.example

else

select * from @.example
where integerCol = @.anIntegerParm

-- When @.anIntegerParm is null:

-- integerCol
-- --
-- 1001
-- 1002
-- 1003
-- 1004

-- When @.anIntegerParm = 1002:

-- integerCol
-- --
-- 1002

select * from @.example
where @.anIntegerParm is null
or @.anIntegerParm is not null
and integerCol = @.anIntegerParm

-- integerCol
-- --
-- 1002

Integer Index -vs- nVarChar(50) index....

We have a legacy database that was ported over from LDAP where the user
tables are indexed on the username (which is an nVarChar(50) field). We have
sever (ok, 92) related tables all using the nVarChar(50) field as the
primary key.
Does anyone have hard numbers as to whether SQL2000 would be much more
efficient if we were to convert all the tables to use an integer as the
primary key (and relate the tables on that key) instead of the nVarChar(50)?
TIA,
OwenYou can calculate the avg length of that column and comprare to 8 bytes (for
bigint) or 4 bytes (for int). The amount of I/O operations over an index wit
h
a key of, let us say, 20 bytes (10 characters for nvarchar) is for sure
higher compare with 4 or 8 bytes. Not to mention if the username is using a
clustered index, in this case that amount (for example 35 bytes) is
replicated to the nonclustered indexes also.
AMB
"Owen Mortensen" wrote:

> We have a legacy database that was ported over from LDAP where the user
> tables are indexed on the username (which is an nVarChar(50) field). We ha
ve
> sever (ok, 92) related tables all using the nVarChar(50) field as the
> primary key.
> Does anyone have hard numbers as to whether SQL2000 would be much more
> efficient if we were to convert all the tables to use an integer as the
> primary key (and relate the tables on that key) instead of the nVarChar(50
)?
> TIA,
> Owen
>
>|||int will be much more efficient.
hard to give you "Hard Data" but I would think the performance improvements
would be fairly significant especially if the system has a lot of data.
If your Database is only a few Gig and if you only have minor load in any
given day, then it may not be worth the efforts in changing.
cheers
Greg Jackson
PDX, Oregon|||Define hard numbers. Ints are better because they are smaller. They are
also better because they are fixed length. So for each row in your table,
your index pages would hold ~ 2000 rows (8000bytes per page/ 4 bytes per
row) If you have an average of 10 characters per entry, plus the 2 byte
overhead for variable length, then you will only get 8000/12 = 666 rows per
page. Ignoring the dubious nature of the number of rows, it will take 3
times as many pages in the index to handle the need for this index.
Carrying this over to the leaf nodes, if this is the clustered index, you
have to add the 12 bytes for every row rather than 4.
Now, this having been said, pure performance of the index is not the only
issue here. I like integers keys as much as the next guy, and possibly
more, but is this THAT great of a savings. Truly it depends on what your
needs are, and how often you will be doing a full index scan. The cost
might be very small if you are just doing single row retrievals, since you
may not have more than a single level added to the index, as the b-tree
index structure is pretty good.
So the cost to change all of these tables versus ten-twenty milliseconds
might not be worth it. The question is (finally) what is the impetus to
change this. Is something slow? Have you come to the conclusion that for
PK based retrieval it is costing too much? Or other types of retrieval?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:up0n6hgYFHA.2768@.tk2msftngp13.phx.gbl...
> We have a legacy database that was ported over from LDAP where the user
> tables are indexed on the username (which is an nVarChar(50) field). We
> have sever (ok, 92) related tables all using the nVarChar(50) field as the
> primary key.
> Does anyone have hard numbers as to whether SQL2000 would be much more
> efficient if we were to convert all the tables to use an integer as the
> primary key (and relate the tables on that key) instead of the
> nVarChar(50)?
> TIA,
> Owen
>

Integer field question

Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
Neil
I don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.

Integer field question

Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
NeilI don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input t
o
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.

Integer field question

Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
NeilI don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.