Having problems with variables

I have a Execute Process Task that pretty much executes a batch file that downloads a file that has a dynamic file name (with datetime stamp). Now I would like to load this file to a Flat File Source task in the Data Flow Task section automatically. So, creating a file manager or something on the fly. Is something like this possible with SSIS? Or am I simply hitting the wall here?

Thank you.

Why can you not just drop a data-flow into your package and make sure it executes after the Execute process task?

-Jamie

Have DTS Package prompt for a file name

I have a user in the IT department that wants a process to take his text file and import it into a SQL Server table. Simple enough with a DTS package.

The rub is that he want's to execute the package and have it prompt him at that point for where the file resides. I tried to get him to go into the DTS pacakge and update the connection, but he doesn't want to do it that way. I suggested renaming the file to a common name to be used each time the application runs, and he wasn't interested in that solution either.

Any help you can give would be greatly appreciated. Thanks!

Have a look at this:

http://www.sqldts.com/default.aspx?226

Has anyone seen a Licensing screen during the install process?

Hi again.

I have been deploying the first few test SQL 2005 machines recently, from what I was told is the full-feature edition DVD received from the https://licensing.microsoft.com website.

We have purchased a mixture of per-processor and per-user licenses to use for this software. However, I am never asked what licensing mode to use during the install.

Can anyone confirm or deny that a setup screen appears asking what licensing version you want to use (per-user or per-processor)?

I cannot confirm what was downloaded from the licensing-website because I don't have access through the company.

I have the same symptoms as seen on this postings:

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

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

However, I don't see such a Control Panel utility to change the licensing type.

Thanks to anyone who can assist.

The Licensing is not controlled through installation or configuration in SQL Server 2005. It is simply a mater of ensuring that you purchase the correct license for the configuration that you are running.

Thanks

Michelle

|||

Just to make sure I understand this: in our case, we've purchased a SQL Server 2005 license from an OEM. We've installed the Enterprise Eval version of SQL Server 2005, supposedly good for 180 days. How does it go from an Eval version to a "purchased" version?

RDTuengel

|||

It won't. Although Michelle is saying above the licensing is based on the honor system, most evaluation editions cut-out after the designated number of days.

You will need to get your hands on an over-the-counter version of the software.

I'm surprised you were able to purchase a license without receiving the original software. You may want to call the OEM or Microsoft.

I'm not sure if you can "upgrade" an evaluation edition to an over-the-counter version... you may want to investigate.

Good luck.

Has anyone seen a Licensing screen during the install process?

Hi again.

I have been deploying the first few test SQL 2005 machines recently, from what I was told is the full-feature edition DVD received from the https://licensing.microsoft.com website.

We have purchased a mixture of per-processor and per-user licenses to use for this software. However, I am never asked what licensing mode to use during the install.

Can anyone confirm or deny that a setup screen appears asking what licensing version you want to use (per-user or per-processor)?

I cannot confirm what was downloaded from the licensing-website because I don't have access through the company.

I have the same symptoms as seen on this postings:

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

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

However, I don't see such a Control Panel utility to change the licensing type.

Thanks to anyone who can assist.

The Licensing is not controlled through installation or configuration in SQL Server 2005. It is simply a mater of ensuring that you purchase the correct license for the configuration that you are running.

Thanks

Michelle

|||

Just to make sure I understand this: in our case, we've purchased a SQL Server 2005 license from an OEM. We've installed the Enterprise Eval version of SQL Server 2005, supposedly good for 180 days. How does it go from an Eval version to a "purchased" version?

RDTuengel

|||

It won't. Although Michelle is saying above the licensing is based on the honor system, most evaluation editions cut-out after the designated number of days.

You will need to get your hands on an over-the-counter version of the software.

I'm surprised you were able to purchase a license without receiving the original software. You may want to call the OEM or Microsoft.

I'm not sure if you can "upgrade" an evaluation edition to an over-the-counter version... you may want to investigate.

Good luck.

Has anyone seen a Licensing screen during the install process?

Hi again.

I have been deploying the first few test SQL 2005 machines recently, from what I was told is the full-feature edition DVD received from the https://licensing.microsoft.com website.

We have purchased a mixture of per-processor and per-user licenses to use for this software. However, I am never asked what licensing mode to use during the install.

Can anyone confirm or deny that a setup screen appears asking what licensing version you want to use (per-user or per-processor)?

I cannot confirm what was downloaded from the licensing-website because I don't have access through the company.

I have the same symptoms as seen on this postings:

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

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

However, I don't see such a Control Panel utility to change the licensing type.

Thanks to anyone who can assist.

The Licensing is not controlled through installation or configuration in SQL Server 2005. It is simply a mater of ensuring that you purchase the correct license for the configuration that you are running.

Thanks

Michelle

|||

Just to make sure I understand this: in our case, we've purchased a SQL Server 2005 license from an OEM. We've installed the Enterprise Eval version of SQL Server 2005, supposedly good for 180 days. How does it go from an Eval version to a "purchased" version?

RDTuengel

|||

It won't. Although Michelle is saying above the licensing is based on the honor system, most evaluation editions cut-out after the designated number of days.

You will need to get your hands on an over-the-counter version of the software.

I'm surprised you were able to purchase a license without receiving the original software. You may want to call the OEM or Microsoft.

I'm not sure if you can "upgrade" an evaluation edition to an over-the-counter version... you may want to investigate.

Good luck.

Hardware spec

Hi
We are in the process of going live on two SQL applications. I am evaluating
hardware requirements and would like to see if I am on the right track.
2 SQL apps. There will be about 15 users on both and down the line we will
be implementing a web interface to both DBs. Database A vendor wants 4GB RAM
and Database B vendor wants about 2GB.
I am going down the following route:
IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
8GB RAM
ServeRAID8k adapter (DB compatible)
6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x 146GB)
Windows 2003 Server Enterprise
2 x SQL 2005 Standard CPU license
(Incase anyone if wondering what RAID 5EE is - it's a fancy version of RAID
5 with hotswap).
I am going to configure the operating system and programs on the RAID 1
array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the RAM
on DB2 to 2GB). There should be plenty of spare for the OS.
Am I barking up the wrong tree? Would I be better splitting the RAID5EE into
two seperate RAID 1 Arrays so the each DB exists on a seperate array?
This might seem overkill, but performance (and future expansion) is
extremely important.
Thanks in advance.
RobbieHi
It is not clear why you want two SQL server instances! They will require
more resource than a single instance even if you specify the maximum amount
of memory.
In general if you can afford it try and get Raid 10 rather than 5, you may
save some money by buying smaller discs for the OS. It would also be better
to split your data and log files onto separate disc arrays, rather than
separate the each instance onto two disc arrays (if you absolutely need two
instances!).
You don't say if you are buying 10K or 15K discs or how much cache is on the
discs.
Check out http://www.sql-server-performance.com including
http://www.sql-server-performance.com/jc_system_storage_configuration.asp
John
"Robbie Niblock" wrote:
> Hi
> We are in the process of going live on two SQL applications. I am evaluating
> hardware requirements and would like to see if I am on the right track.
> 2 SQL apps. There will be about 15 users on both and down the line we will
> be implementing a web interface to both DBs. Database A vendor wants 4GB RAM
> and Database B vendor wants about 2GB.
> I am going down the following route:
> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
> 8GB RAM
> ServeRAID8k adapter (DB compatible)
> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x 146GB)
> Windows 2003 Server Enterprise
> 2 x SQL 2005 Standard CPU license
>
> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of RAID
> 5 with hotswap).
> I am going to configure the operating system and programs on the RAID 1
> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the RAM
> on DB2 to 2GB). There should be plenty of spare for the OS.
> Am I barking up the wrong tree? Would I be better splitting the RAID5EE into
> two seperate RAID 1 Arrays so the each DB exists on a seperate array?
> This might seem overkill, but performance (and future expansion) is
> extremely important.
> Thanks in advance.
> Robbie
>
>|||You didn't mention the db sizes, average query, average frequency of query,
etc.
So far, it all sounds good -in fact, a very nice box.
If you are only running SQL Server on the box, the OS only needs 1 GB of
memory -that could allow more for either instance.
If you are really concerned about ''safety', putting the databases on Raid
1's would provide a level of redundency -as well as better separation of the
two Vendors' data, thinking backups, administrative uses, etc.
Licenses. A single EE license 'might' provide you more options for Growth,
expansion, and 'high availability'. (And it allows multiple instances)
Depending upon your VL agreement, it may not be much more than 2 Standard
editions.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
> Hi
> We are in the process of going live on two SQL applications. I am
> evaluating hardware requirements and would like to see if I am on the
> right track.
> 2 SQL apps. There will be about 15 users on both and down the line we will
> be implementing a web interface to both DBs. Database A vendor wants 4GB
> RAM and Database B vendor wants about 2GB.
> I am going down the following route:
> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
> 8GB RAM
> ServeRAID8k adapter (DB compatible)
> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
> 146GB)
> Windows 2003 Server Enterprise
> 2 x SQL 2005 Standard CPU license
>
> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
> RAID 5 with hotswap).
> I am going to configure the operating system and programs on the RAID 1
> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the
> RAM on DB2 to 2GB). There should be plenty of spare for the OS.
> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
> into two seperate RAID 1 Arrays so the each DB exists on a seperate array?
> This might seem overkill, but performance (and future expansion) is
> extremely important.
> Thanks in advance.
> Robbie
>|||Thanks for the response. We are using 15k disks - 8mb buffer.
I was thinking two instances because the two DBs require different
collation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9BDD41CE-F8B9-45B6-8AC6-66D92FBB1EB6@.microsoft.com...
> Hi
> It is not clear why you want two SQL server instances! They will require
> more resource than a single instance even if you specify the maximum
> amount
> of memory.
> In general if you can afford it try and get Raid 10 rather than 5, you may
> save some money by buying smaller discs for the OS. It would also be
> better
> to split your data and log files onto separate disc arrays, rather than
> separate the each instance onto two disc arrays (if you absolutely need
> two
> instances!).
> You don't say if you are buying 10K or 15K discs or how much cache is on
> the
> discs.
> Check out http://www.sql-server-performance.com including
> http://www.sql-server-performance.com/jc_system_storage_configuration.asp
> John
> "Robbie Niblock" wrote:
>> Hi
>> We are in the process of going live on two SQL applications. I am
>> evaluating
>> hardware requirements and would like to see if I am on the right track.
>> 2 SQL apps. There will be about 15 users on both and down the line we
>> will
>> be implementing a web interface to both DBs. Database A vendor wants 4GB
>> RAM
>> and Database B vendor wants about 2GB.
>> I am going down the following route:
>> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
>> 8GB RAM
>> ServeRAID8k adapter (DB compatible)
>> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
>> 146GB)
>> Windows 2003 Server Enterprise
>> 2 x SQL 2005 Standard CPU license
>>
>> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
>> RAID
>> 5 with hotswap).
>> I am going to configure the operating system and programs on the RAID 1
>> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
>> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the
>> RAM
>> on DB2 to 2GB). There should be plenty of spare for the OS.
>> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
>> into
>> two seperate RAID 1 Arrays so the each DB exists on a seperate array?
>> This might seem overkill, but performance (and future expansion) is
>> extremely important.
>> Thanks in advance.
>> Robbie
>>|||Thanks for your input.
Surely though I can have multiple instances with SQL Standard?
Robbie
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eJaO4AqpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> You didn't mention the db sizes, average query, average frequency of
> query, etc.
> So far, it all sounds good -in fact, a very nice box.
> If you are only running SQL Server on the box, the OS only needs 1 GB of
> memory -that could allow more for either instance.
> If you are really concerned about ''safety', putting the databases on Raid
> 1's would provide a level of redundency -as well as better separation of
> the two Vendors' data, thinking backups, administrative uses, etc.
> Licenses. A single EE license 'might' provide you more options for Growth,
> expansion, and 'high availability'. (And it allows multiple instances)
> Depending upon your VL agreement, it may not be much more than 2 Standard
> editions.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robbie Niblock" <robbie@.nospam.com> wrote in message
> news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> We are in the process of going live on two SQL applications. I am
>> evaluating hardware requirements and would like to see if I am on the
>> right track.
>> 2 SQL apps. There will be about 15 users on both and down the line we
>> will be implementing a web interface to both DBs. Database A vendor wants
>> 4GB RAM and Database B vendor wants about 2GB.
>> I am going down the following route:
>> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
>> 8GB RAM
>> ServeRAID8k adapter (DB compatible)
>> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
>> 146GB)
>> Windows 2003 Server Enterprise
>> 2 x SQL 2005 Standard CPU license
>>
>> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
>> RAID 5 with hotswap).
>> I am going to configure the operating system and programs on the RAID 1
>> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
>> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the
>> RAM on DB2 to 2GB). There should be plenty of spare for the OS.
>> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
>> into two seperate RAID 1 Arrays so the each DB exists on a seperate
>> array?
>> This might seem overkill, but performance (and future expansion) is
>> extremely important.
>> Thanks in advance.
>> Robbie
>|||Of course you can, I didn't mean to imply otherwise.
You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
was trying to suggest that 2 EE license may be all you need for this
situation.
And my comment about separation the 2 Vendors data is related to
SarBox/HIPPA issues. It may not be necessary in your situation.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:etJDJLqpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> Thanks for your input.
> Surely though I can have multiple instances with SQL Standard?
> Robbie
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eJaO4AqpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> You didn't mention the db sizes, average query, average frequency of
>> query, etc.
>> So far, it all sounds good -in fact, a very nice box.
>> If you are only running SQL Server on the box, the OS only needs 1 GB of
>> memory -that could allow more for either instance.
>> If you are really concerned about ''safety', putting the databases on
>> Raid 1's would provide a level of redundency -as well as better
>> separation of the two Vendors' data, thinking backups, administrative
>> uses, etc.
>> Licenses. A single EE license 'might' provide you more options for
>> Growth, expansion, and 'high availability'. (And it allows multiple
>> instances) Depending upon your VL agreement, it may not be much more than
>> 2 Standard editions.
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Robbie Niblock" <robbie@.nospam.com> wrote in message
>> news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> We are in the process of going live on two SQL applications. I am
>> evaluating hardware requirements and would like to see if I am on the
>> right track.
>> 2 SQL apps. There will be about 15 users on both and down the line we
>> will be implementing a web interface to both DBs. Database A vendor
>> wants 4GB RAM and Database B vendor wants about 2GB.
>> I am going down the following route:
>> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
>> 8GB RAM
>> ServeRAID8k adapter (DB compatible)
>> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
>> 146GB)
>> Windows 2003 Server Enterprise
>> 2 x SQL 2005 Standard CPU license
>>
>> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
>> RAID 5 with hotswap).
>> I am going to configure the operating system and programs on the RAID 1
>> array (c:\) and the SQL databases on the RAID 5EE. I will be creating
>> two SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and
>> the RAM on DB2 to 2GB). There should be plenty of spare for the OS.
>> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
>> into two seperate RAID 1 Arrays so the each DB exists on a seperate
>> array?
>> This might seem overkill, but performance (and future expansion) is
>> extremely important.
>> Thanks in advance.
>> Robbie
>>
>|||OK - sorry if I picked it up wrong :o)
I don't think going Enterprise Ed is going to benefit us and it is a massive
price increase.
Thanks for your help.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23nC16VqpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Of course you can, I didn't mean to imply otherwise.
> You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
> was trying to suggest that 2 EE license may be all you need for this
> situation.
> And my comment about separation the 2 Vendors data is related to
> SarBox/HIPPA issues. It may not be necessary in your situation.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robbie Niblock" <robbie@.nospam.com> wrote in message
> news:etJDJLqpGHA.3584@.TK2MSFTNGP03.phx.gbl...
>> Thanks for your input.
>> Surely though I can have multiple instances with SQL Standard?
>> Robbie
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:eJaO4AqpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> You didn't mention the db sizes, average query, average frequency of
>> query, etc.
>> So far, it all sounds good -in fact, a very nice box.
>> If you are only running SQL Server on the box, the OS only needs 1 GB of
>> memory -that could allow more for either instance.
>> If you are really concerned about ''safety', putting the databases on
>> Raid 1's would provide a level of redundency -as well as better
>> separation of the two Vendors' data, thinking backups, administrative
>> uses, etc.
>> Licenses. A single EE license 'might' provide you more options for
>> Growth, expansion, and 'high availability'. (And it allows multiple
>> instances) Depending upon your VL agreement, it may not be much more
>> than 2 Standard editions.
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Robbie Niblock" <robbie@.nospam.com> wrote in message
>> news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> We are in the process of going live on two SQL applications. I am
>> evaluating hardware requirements and would like to see if I am on the
>> right track.
>> 2 SQL apps. There will be about 15 users on both and down the line we
>> will be implementing a web interface to both DBs. Database A vendor
>> wants 4GB RAM and Database B vendor wants about 2GB.
>> I am going down the following route:
>> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
>> 8GB RAM
>> ServeRAID8k adapter (DB compatible)
>> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
>> 146GB)
>> Windows 2003 Server Enterprise
>> 2 x SQL 2005 Standard CPU license
>>
>> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
>> RAID 5 with hotswap).
>> I am going to configure the operating system and programs on the RAID 1
>> array (c:\) and the SQL databases on the RAID 5EE. I will be creating
>> two SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB
>> and the RAM on DB2 to 2GB). There should be plenty of spare for the OS.
>> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
>> into two seperate RAID 1 Arrays so the each DB exists on a seperate
>> array?
>> This might seem overkill, but performance (and future expansion) is
>> extremely important.
>> Thanks in advance.
>> Robbie
>>
>>
>|||You may want to consider two more smaller drives as well, if you are
looking for best bang for the buck. One for the windows swap file, one
for TempDB. It's cheap and gives a nice performance boost.
Robbie Niblock wrote:
> OK - sorry if I picked it up wrong :o)
> I don't think going Enterprise Ed is going to benefit us and it is a massive
> price increase.
> Thanks for your help.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23nC16VqpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> > Of course you can, I didn't mean to imply otherwise.
> >
> > You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
> > was trying to suggest that 2 EE license may be all you need for this
> > situation.
> >
> > And my comment about separation the 2 Vendors data is related to
> > SarBox/HIPPA issues. It may not be necessary in your situation.
> >
> > --
> > Arnie Rowland*
> > "To be successful, your heart must accompany your knowledge."
> >
> >
> >
> > "Robbie Niblock" <robbie@.nospam.com> wrote in message
> > news:etJDJLqpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> >> Thanks for your input.
> >>
> >> Surely though I can have multiple instances with SQL Standard?
> >>
> >> Robbie
> >>
> >> "Arnie Rowland" <arnie@.1568.com> wrote in message
> >> news:eJaO4AqpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> >> You didn't mention the db sizes, average query, average frequency of
> >> query, etc.
> >>
> >> So far, it all sounds good -in fact, a very nice box.
> >>
> >> If you are only running SQL Server on the box, the OS only needs 1 GB of
> >> memory -that could allow more for either instance.
> >>
> >> If you are really concerned about ''safety', putting the databases on
> >> Raid 1's would provide a level of redundency -as well as better
> >> separation of the two Vendors' data, thinking backups, administrative
> >> uses, etc.
> >>
> >> Licenses. A single EE license 'might' provide you more options for
> >> Growth, expansion, and 'high availability'. (And it allows multiple
> >> instances) Depending upon your VL agreement, it may not be much more
> >> than 2 Standard editions.
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "Robbie Niblock" <robbie@.nospam.com> wrote in message
> >> news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
> >> Hi
> >>
> >> We are in the process of going live on two SQL applications. I am
> >> evaluating hardware requirements and would like to see if I am on the
> >> right track.
> >>
> >> 2 SQL apps. There will be about 15 users on both and down the line we
> >> will be implementing a web interface to both DBs. Database A vendor
> >> wants 4GB RAM and Database B vendor wants about 2GB.
> >>
> >> I am going down the following route:
> >>
> >> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
> >> 8GB RAM
> >> ServeRAID8k adapter (DB compatible)
> >> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
> >> 146GB)
> >> Windows 2003 Server Enterprise
> >> 2 x SQL 2005 Standard CPU license
> >>
> >>
> >> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
> >> RAID 5 with hotswap).
> >>
> >> I am going to configure the operating system and programs on the RAID 1
> >> array (c:\) and the SQL databases on the RAID 5EE. I will be creating
> >> two SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB
> >> and the RAM on DB2 to 2GB). There should be plenty of spare for the OS.
> >>
> >> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
> >> into two seperate RAID 1 Arrays so the each DB exists on a seperate
> >> array?
> >>
> >> This might seem overkill, but performance (and future expansion) is
> >> extremely important.
> >>
> >> Thanks in advance.
> >>
> >> Robbie
> >>
> >>
> >>
> >>
> >>
> >
> >|||On Thu, 13 Jul 2006 10:55:06 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
>was trying to suggest that 2 EE license may be all you need for this
>situation.
According to the documentation, Standard Edition supports up to 16
named instances, while EE supports up to 50. Would not a single SD
licence cover both instances, since it is all running on one box? Or
is the licensing different? I could not find anything on this at the
MS site.
Roy|||Hi
If they are not vastly different collations, then you may want to see if one
application can be converted.
Having the faster discs will help, if your cabinet allows it add more drives
if you can, as suggested for tempdb (which you will have 2 off!!!) and the
windows swap file.
You will need to do some sizing to see if you can reduce the disc capacity
may save some money, increasing the number of spindles in your raid stripes
will also help performance. With all these discs you may not have enough
slots!!!
John
"Robbie Niblock" wrote:
> Thanks for the response. We are using 15k disks - 8mb buffer.
> I was thinking two instances because the two DBs require different
> collation.
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9BDD41CE-F8B9-45B6-8AC6-66D92FBB1EB6@.microsoft.com...
> > Hi
> >
> > It is not clear why you want two SQL server instances! They will require
> > more resource than a single instance even if you specify the maximum
> > amount
> > of memory.
> >
> > In general if you can afford it try and get Raid 10 rather than 5, you may
> > save some money by buying smaller discs for the OS. It would also be
> > better
> > to split your data and log files onto separate disc arrays, rather than
> > separate the each instance onto two disc arrays (if you absolutely need
> > two
> > instances!).
> >
> > You don't say if you are buying 10K or 15K discs or how much cache is on
> > the
> > discs.
> >
> > Check out http://www.sql-server-performance.com including
> > http://www.sql-server-performance.com/jc_system_storage_configuration.asp
> >
> > John
> >
> > "Robbie Niblock" wrote:
> >
> >> Hi
> >>
> >> We are in the process of going live on two SQL applications. I am
> >> evaluating
> >> hardware requirements and would like to see if I am on the right track.
> >>
> >> 2 SQL apps. There will be about 15 users on both and down the line we
> >> will
> >> be implementing a web interface to both DBs. Database A vendor wants 4GB
> >> RAM
> >> and Database B vendor wants about 2GB.
> >>
> >> I am going down the following route:
> >>
> >> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
> >> 8GB RAM
> >> ServeRAID8k adapter (DB compatible)
> >> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
> >> 146GB)
> >> Windows 2003 Server Enterprise
> >> 2 x SQL 2005 Standard CPU license
> >>
> >>
> >> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
> >> RAID
> >> 5 with hotswap).
> >>
> >> I am going to configure the operating system and programs on the RAID 1
> >> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
> >> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the
> >> RAM
> >> on DB2 to 2GB). There should be plenty of spare for the OS.
> >>
> >> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
> >> into
> >> two seperate RAID 1 Arrays so the each DB exists on a seperate array?
> >>
> >> This might seem overkill, but performance (and future expansion) is
> >> extremely important.
> >>
> >> Thanks in advance.
> >>
> >> Robbie
> >>
> >>
> >>
>
>|||This is a multi-part message in MIME format.
--000101070106040008010608
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Roy Harvey wrote:
> On Thu, 13 Jul 2006 10:55:06 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>
>> You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
>> was trying to suggest that 2 EE license may be all you need for this
>> situation.
> According to the documentation, Standard Edition supports up to 16
> named instances, while EE supports up to 50. Would not a single SD
> licence cover both instances, since it is all running on one box? Or
> is the licensing different? I could not find anything on this at the
> MS site.
> Roy
>
The licensing is not so much about the instances, but number of
processors. In this case Robbie has chosen a DUAL proc. server, so he
needs 2 processor licenses - no matter what version of SQL server he
chooses (of course assuming he is licensing per processor).
The only think about the configuration I'd change, is the RAID 5 array.
Instead of one RAID 5 array for both databae and logfiles, I'd create 2
RAID1 arrays and then have my database on one array and logs on the
other one. You don't mention anything about database sizes, so it's
difficult to say if this configuration will give you enough space though.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--000101070106040008010608
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Roy Harvey wrote:
<blockquote cite="mid0a4db2dsg8gqei78rim48v64h94opr8vcv@.4ax.com"
type="cite">
<pre wrap="">On Thu, 13 Jul 2006 10:55:06 -0700, "Arnie Rowland" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:arnie@.1568.com"><arnie@.1568.com></a>
wrote:
</pre>
<blockquote type="cite">
<pre wrap="">You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
was trying to suggest that 2 EE license may be all you need for this
situation.
</pre>
</blockquote>
<pre wrap=""><!-->
According to the documentation, Standard Edition supports up to 16
named instances, while EE supports up to 50. Would not a single SD
licence cover both instances, since it is all running on one box? Or
is the licensing different? I could not find anything on this at the
MS site.
Roy
</pre>
</blockquote>
<font size="-1"><font face="Arial">The licensing is not so much about
the instances, but number of processors. In this case Robbie has chosen
a DUAL proc. server, so he needs 2 processor licenses - no matter what
version of SQL server he chooses (of course assuming he is licensing
per processor).<br>
<br>
The only think about the configuration I'd change, is the RAID 5 array.
Instead of one RAID 5 array for both databae and logfiles, I'd create 2
RAID1 arrays and then have my database on one array and logs on the
other one. You don't mention anything about database sizes, so it's
difficult to say if this configuration will give you enough space
though.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--000101070106040008010608--

Hardware spec

Hi
We are in the process of going live on two SQL applications. I am evaluating
hardware requirements and would like to see if I am on the right track.
2 SQL apps. There will be about 15 users on both and down the line we will
be implementing a web interface to both DBs. Database A vendor wants 4GB RAM
and Database B vendor wants about 2GB.
I am going down the following route:
IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
8GB RAM
ServeRAID8k adapter (DB compatible)
6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x 146GB)
Windows 2003 Server Enterprise
2 x SQL 2005 Standard CPU license
(Incase anyone if wondering what RAID 5EE is - it's a fancy version of RAID
5 with hotswap).
I am going to configure the operating system and programs on the RAID 1
array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the RAM
on DB2 to 2GB). There should be plenty of spare for the OS.
Am I barking up the wrong tree? Would I be better splitting the RAID5EE into
two seperate RAID 1 Arrays so the each DB exists on a seperate array?
This might seem overkill, but performance (and future expansion) is
extremely important.
Thanks in advance.
RobbieHi
It is not clear why you want two SQL server instances! They will require
more resource than a single instance even if you specify the maximum amount
of memory.
In general if you can afford it try and get Raid 10 rather than 5, you may
save some money by buying smaller discs for the OS. It would also be better
to split your data and log files onto separate disc arrays, rather than
separate the each instance onto two disc arrays (if you absolutely need two
instances!).
You don't say if you are buying 10K or 15K discs or how much cache is on the
discs.
Check out http://www.sql-server-performance.com including
http://www.sql-server-performance.c...nfiguration.asp
John
"Robbie Niblock" wrote:

> Hi
> We are in the process of going live on two SQL applications. I am evaluati
ng
> hardware requirements and would like to see if I am on the right track.
> 2 SQL apps. There will be about 15 users on both and down the line we will
> be implementing a web interface to both DBs. Database A vendor wants 4GB R
AM
> and Database B vendor wants about 2GB.
> I am going down the following route:
> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
> 8GB RAM
> ServeRAID8k adapter (DB compatible)
> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x 146G
B)
> Windows 2003 Server Enterprise
> 2 x SQL 2005 Standard CPU license
>
> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of RAI
D
> 5 with hotswap).
> I am going to configure the operating system and programs on the RAID 1
> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the R
AM
> on DB2 to 2GB). There should be plenty of spare for the OS.
> Am I barking up the wrong tree? Would I be better splitting the RAID5EE in
to
> two seperate RAID 1 Arrays so the each DB exists on a seperate array?
> This might seem overkill, but performance (and future expansion) is
> extremely important.
> Thanks in advance.
> Robbie
>
>|||You didn't mention the db sizes, average query, average frequency of query,
etc.
So far, it all sounds good -in fact, a very nice box.
If you are only running SQL Server on the box, the OS only needs 1 GB of
memory -that could allow more for either instance.
If you are really concerned about ''safety', putting the databases on Raid
1's would provide a level of redundency -as well as better separation of the
two Vendors' data, thinking backups, administrative uses, etc.
Licenses. A single EE license 'might' provide you more options for Growth,
expansion, and 'high availability'. (And it allows multiple instances)
Depending upon your VL agreement, it may not be much more than 2 Standard
editions.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
> Hi
> We are in the process of going live on two SQL applications. I am
> evaluating hardware requirements and would like to see if I am on the
> right track.
> 2 SQL apps. There will be about 15 users on both and down the line we will
> be implementing a web interface to both DBs. Database A vendor wants 4GB
> RAM and Database B vendor wants about 2GB.
> I am going down the following route:
> IBM server with 2 x Dual Core Xeon CPUs (3.00GHz each)
> 8GB RAM
> ServeRAID8k adapter (DB compatible)
> 6 x 146GB 15k SCSI (1 x RAID 1 Array - 2 x 146GB , 1 x RAID 5EE - 4 x
> 146GB)
> Windows 2003 Server Enterprise
> 2 x SQL 2005 Standard CPU license
>
> (Incase anyone if wondering what RAID 5EE is - it's a fancy version of
> RAID 5 with hotswap).
> I am going to configure the operating system and programs on the RAID 1
> array (c:\) and the SQL databases on the RAID 5EE. I will be creating two
> SQL instances (DB1 and DB2). I'll restrict the RAM on DB1 to 4GB and the
> RAM on DB2 to 2GB). There should be plenty of spare for the OS.
> Am I barking up the wrong tree? Would I be better splitting the RAID5EE
> into two seperate RAID 1 Arrays so the each DB exists on a seperate array?
> This might seem overkill, but performance (and future expansion) is
> extremely important.
> Thanks in advance.
> Robbie
>|||Thanks for the response. We are using 15k disks - 8mb buffer.
I was thinking two instances because the two DBs require different
collation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9BDD41CE-F8B9-45B6-8AC6-66D92FBB1EB6@.microsoft.com...[vbcol=seagreen]
> Hi
> It is not clear why you want two SQL server instances! They will require
> more resource than a single instance even if you specify the maximum
> amount
> of memory.
> In general if you can afford it try and get Raid 10 rather than 5, you may
> save some money by buying smaller discs for the OS. It would also be
> better
> to split your data and log files onto separate disc arrays, rather than
> separate the each instance onto two disc arrays (if you absolutely need
> two
> instances!).
> You don't say if you are buying 10K or 15K discs or how much cache is on
> the
> discs.
> Check out http://www.sql-server-performance.com including
> http://www.sql-server-performance.c...nfiguration.asp
> John
> "Robbie Niblock" wrote:
>|||Thanks for your input.
Surely though I can have multiple instances with SQL Standard?
Robbie
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eJaO4AqpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> You didn't mention the db sizes, average query, average frequency of
> query, etc.
> So far, it all sounds good -in fact, a very nice box.
> If you are only running SQL Server on the box, the OS only needs 1 GB of
> memory -that could allow more for either instance.
> If you are really concerned about ''safety', putting the databases on Raid
> 1's would provide a level of redundency -as well as better separation of
> the two Vendors' data, thinking backups, administrative uses, etc.
> Licenses. A single EE license 'might' provide you more options for Growth,
> expansion, and 'high availability'. (And it allows multiple instances)
> Depending upon your VL agreement, it may not be much more than 2 Standard
> editions.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robbie Niblock" <robbie@.nospam.com> wrote in message
> news:OsLaYnppGHA.148@.TK2MSFTNGP04.phx.gbl...
>|||Of course you can, I didn't mean to imply otherwise.
You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
was trying to suggest that 2 EE license may be all you need for this
situation.
And my comment about separation the 2 Vendors data is related to
SarBox/HIPPA issues. It may not be necessary in your situation.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robbie Niblock" <robbie@.nospam.com> wrote in message
news:etJDJLqpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> Thanks for your input.
> Surely though I can have multiple instances with SQL Standard?
> Robbie
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eJaO4AqpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||OK - sorry if I picked it up wrong :o)
I don't think going Enterprise Ed is going to benefit us and it is a massive
price increase.
Thanks for your help.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23nC16VqpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Of course you can, I didn't mean to imply otherwise.
> You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
> was trying to suggest that 2 EE license may be all you need for this
> situation.
> And my comment about separation the 2 Vendors data is related to
> SarBox/HIPPA issues. It may not be necessary in your situation.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robbie Niblock" <robbie@.nospam.com> wrote in message
> news:etJDJLqpGHA.3584@.TK2MSFTNGP03.phx.gbl...
>|||You may want to consider two more smaller drives as well, if you are
looking for best bang for the buck. One for the windows swap file, one
for TempDB. It's cheap and gives a nice performance boost.
Robbie Niblock wrote:[vbcol=seagreen]
> OK - sorry if I picked it up wrong :o)
> I don't think going Enterprise Ed is going to benefit us and it is a massi
ve
> price increase.
> Thanks for your help.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23nC16VqpGHA.1440@.TK2MSFTNGP03.phx.gbl...|||On Thu, 13 Jul 2006 10:55:06 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>You indicated that you were buying 2 SQL Standard Edition CPU licenses. I
>was trying to suggest that 2 EE license may be all you need for this
>situation.
According to the documentation, Standard Edition supports up to 16
named instances, while EE supports up to 50. Would not a single SD
licence cover both instances, since it is all running on one box? Or
is the licensing different? I could not find anything on this at the
MS site.
Roy|||Hi
If they are not vastly different collations, then you may want to see if one
application can be converted.
Having the faster discs will help, if your cabinet allows it add more drives
if you can, as suggested for tempdb (which you will have 2 off!!!) and the
windows swap file.
You will need to do some sizing to see if you can reduce the disc capacity
may save some money, increasing the number of spindles in your raid stripes
will also help performance. With all these discs you may not have enough
slots!!!
John
"Robbie Niblock" wrote:

> Thanks for the response. We are using 15k disks - 8mb buffer.
> I was thinking two instances because the two DBs require different
> collation.
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9BDD41CE-F8B9-45B6-8AC6-66D92FBB1EB6@.microsoft.com...
>
>

Hardware requirement

Hello!
Does anyone have any idea what the hardware requirements for a SQL2000 box
would be if I would like to process about 20000 - 30000 inserts per second?
We need to insert large amount of data in SQL database and that would be
peek number of inserts that we need. Any rough numbers? (number of
processors, RAM, disk subsytem configuration...) Database size and number of
user conections are not an important factor at the time since they are quite
small (100GB, 50 connections).
Does anyone have similar processing power on SQL?
Thanks
Dan20-30k inserts/sec is possible on a 2 CPU Xeon, but it
depends on exactly what you are doing and how.
if the only meaningful load is the inserts, then a dual
processor system should be able to handle your load,
otherwise, you might go to a 4 CPU box
RAM and disks will depend on the specifics of what your
are doing
i will be talking on this subject at the next SQL Server
Magazine Connections conference (www.sqlconnections.com)
-joe chang
>--Original Message--
>Hello!
>Does anyone have any idea what the hardware requirements
for a SQL2000 box
>would be if I would like to process about 20000 - 30000
inserts per second?
>We need to insert large amount of data in SQL database
and that would be
>peek number of inserts that we need. Any rough numbers?
(number of
>processors, RAM, disk subsytem configuration...) Database
size and number of
>user conections are not an important factor at the time
since they are quite
>small (100GB, 50 connections).
>Does anyone have similar processing power on SQL?
>Thanks
>Dan
>
>.
>

hanging on one table

Hi,
sometimes (often) my merge replication agent hang on this step:
Processing article 'table1'...
or (occasionally):
The merge process is cleaning up meta data in database 'db1'...
table1 is a large table (22 fields, approx 400.000 rows) with no Primary Key.
I don't have any idea with 'meta data' errors.
After this hang/error we always have this happen again the next time we tried again.
what happen with this? pls help...
TIA
echo
can you post the exact error message you are getting?
Also right click on your problem merge agent and select Agent Properties,
click on steps, and then click on run agent. Then click Edit and at the end
of the commands you find there, hit the space bar, and type -QueryTimeOut
600
Then restart your merge agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"echo" <echo@.discussions.microsoft.com> wrote in message
news:922613C3-C666-412F-B35B-F70B2B044F95@.microsoft.com...
> Hi,
> sometimes (often) my merge replication agent hang on this step:
> Processing article 'table1'...
> or (occasionally):
> The merge process is cleaning up meta data in database 'db1'...
> table1 is a large table (22 fields, approx 400.000 rows) with no Primary
Key.
> I don't have any idea with 'meta data' errors.
> After this hang/error we always have this happen again the next time we
tried again.
> what happen with this? pls help...
> TIA
> echo

Handling GUID/UniqueIdentifier

Hi,

I am presently working on a ETL Process of importing data from XML source to the database (SQL Server 2000/2005).

I have GUID data in the XML file and i need to import that data into the database tables.

My Clarification is, if i import the GUID from XML to the database table, in the future, if the Database Engine generates new GUID's for new data, is there a posibility that the database engine might generate SAME guid as the one i imported from XML? As the GUID's in XML were not generated by the target Database Engine, can the database engine possibly generate the same GUID similar to the one i imported from XML?

Regards,

Vikram

If the Guid column in your table has a UNIQUE constraint applied, then you will have no problem.|||GUIDs have a theoritical possibility of duplication once every 100 years somewhere in one computer in the istalled base of every computer in the world. (approximate)|||

Thanks. I just wanted to have it confirmed.

Regards,

Vikram

Handling flat files that do not exist

Hello,

I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.

Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)

I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.

So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?

Thanks!

Lee.

I recall that I have a couple data flows in my package, each within a sequence container and connected them with completion(blue) arrows and even if the first one failed the second one continued on...

|||

That is one solution that I could use. It doesn't seem like it is the "elegant" way to handle it, but it will work.

Thanks for the suggestion.

|||

well

Is dragging red arrow to some alert/send mail task will not serve purpose?

|||

Scoutn wrote:

Hello,

I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.

Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)

I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.

So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?

Thanks!

Lee.

You can use a script task to check whether or not a file exists. This code should help:

File.Exists Method
(http://msdn2.microsoft.com/en-us/library/system.io.file.exists.aspx)

-Jamie

|||

Jamie,

First allow me to say that your blog has been an invaluable resource. I have learned a lot from your articles, thank you!

Utsav, I am not looking to be notified of the error; I just want the task to merrily carry on like nothing took place. I do understand what you meant though, thank you.

Sometimes the flat file will be there and sometimes it won't be. If it isn't, I would still like the task move on to the next with a "Success" result, not just completion. That is why I was thinking of disabling the tasks that do not have corresponding flat files progmatically when I load the package from my application.

I guess I should be asking; when I set Dts.TaskResult = Dts.Results.Success, why does it not actually return success?

(I know there are a couple of ways I can get around this issue, I'm just looking for the easiest without setting the task to continue on "Completion" ;)

Thanks again,
Lee.

|||

Check if this thread has something that can help you.

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

Instead of disabling the task; try to use an expression in the precedence constraint in the control flow.

Rafael Salas

|||Thank you very much. I did search the forum, I guess I was just using the wrong search term.

Perfect!|||

The thread (and the subsequent blog articles) solved my problem. I needed to put each task in a sequence container with their own script task checking for the files; if I used one script task to look for all the files the next task would not run because at least one file would return false.

Thanks again for everyones help!

handling failover at the application level with Sql2005 Mirroring

We're in the process of writing an application that we want to work with a
synchronous mirrored Sql 2005 database and a witness.
As I currently understand it the best way to handle failover with this setup
is to catch the server not available error and then retry the operation.
Any open transaction will be rolled back (in effect) so we need to retry the
entire transaction.
We have complicated transactional processes at our business logic layer, so
we have quite a few places where we begin a transaction, carry out a number
of operations that may involve multiple database calls, and then commit or
rollback on error.
In order to add this retry functionality it looks like I need to add a
try-catch around every call at the business logic layer as it needs to wrap
the transaction. This is possible, but messy.
Is there a better way of doing this? Ideally the handing of the retry
should be at the data access layer, but as the transaction in progress when
the failover happens will be lost it doesn't look possible to handle this in
individual data commands.
Any ideas?
Keith Henry
This isn't unique to Database Mirroring. You would have to do that in every
case where you are dealing with a failover configuration. There is no logic
that basically says "retry". You have to code this all yourself.
When the mirror fails over, you will get a disconnect and any transactions
in flight will be rolled back. The only thing your applications can take
advantage of if they are using the new MDAC library. In this situation,
there is code carried that will cache both the principal and mirror. Your
application can simply reconnect to the principal and the MDAC layer will
transparently redirect the connection and requests to the mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Keith Henry" <k.henry@.link-hrsystems.com> wrote in message
news:u2mk5jjLGHA.984@.tk2msftngp13.phx.gbl...
> We're in the process of writing an application that we want to work with a
> synchronous mirrored Sql 2005 database and a witness.
> As I currently understand it the best way to handle failover with this
> setup is to catch the server not available error and then retry the
> operation. Any open transaction will be rolled back (in effect) so we need
> to retry the entire transaction.
> We have complicated transactional processes at our business logic layer,
> so we have quite a few places where we begin a transaction, carry out a
> number of operations that may involve multiple database calls, and then
> commit or rollback on error.
>
> In order to add this retry functionality it looks like I need to add a
> try-catch around every call at the business logic layer as it needs to
> wrap the transaction. This is possible, but messy.
>
> Is there a better way of doing this? Ideally the handing of the retry
> should be at the data access layer, but as the transaction in progress
> when the failover happens will be lost it doesn't look possible to handle
> this in individual data commands.
> Any ideas?
> Keith Henry
>
|||Thanks,
I guess we'll have to go with the retry from the top level then.
Keith Henry
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:udYhiKnLGHA.208@.tk2msftngp13.phx.gbl...
> This isn't unique to Database Mirroring. You would have to do that in
> every case where you are dealing with a failover configuration. There is
> no logic that basically says "retry". You have to code this all yourself.
> When the mirror fails over, you will get a disconnect and any transactions
> in flight will be rolled back. The only thing your applications can take
> advantage of if they are using the new MDAC library. In this situation,
> there is code carried that will cache both the principal and mirror. Your
> application can simply reconnect to the principal and the MDAC layer will
> transparently redirect the connection and requests to the mirror.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Keith Henry" <k.henry@.link-hrsystems.com> wrote in message
> news:u2mk5jjLGHA.984@.tk2msftngp13.phx.gbl...
>

Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D

half baked conversion - speed issue - any low hanging fruit ?

Continuing question about my MS Access to SQL conversion project :
0)Many thanks for previous assistance - we now have the batch process
running successfully on the clients network
1) It's an MS Access batch process using approximately 150 tables, 300
queries - sounds like a mess but it is a reasonably disciplined and
structured application - dealing with real world, very noisy data from
3 sources, massaging them in to a unified set of data, recording data
over a 25 year period.
2) The client does not want to pay for a full conversion to SQL - ie
convert all of MS Access code to stored procedures - just wants the
back end across to SQL for use with other tools (such as Cognos)
3) The batch process takes 12 hours on my little development
environment and 24 hours plus on their corporate network
(the original pure MS Access batch process take 45 minutes on my
network and 6 hours on theirs)
4) The client is now starting to understand the need to move some of
the processing in to the SQL server. For example - I have experimented
and found that a delete query on an intermediate work table will take
10 minutes via the Access front end, but 5 seconds as a pass thru
query. Also, some queries are taking 100 minutes to run across the
network, and if I focus attention on turning these in to pass thru
queries - I am sure I can drastically speed them up.
QUESTION
Before I put the client to the expense of additional development - are
there any other steps I should follow first - ie are there any
settings I should check on the SQL server.
For example - I don't need transaction logging at all - its really a
single user, batch application - if it crashes half way thru we can
just restart it and it is built in such a way that it will sort itself
out.
I have to confess that I only know enough about SQL server to be
dangerous - so if anyone could point me at some topics - I will go off
and do some research - but at the moment I don't know where to start.
Many thanks
TonyYou can't turn off logging completely but you can under the right conditions
do a "minimally logged load" Look in BooksOnLine under that topic for more
details. But this will only help with loading of tables and not
manipulating the data once there. Are you using SET NOCOUNT ON in all your
batches? I have no clue as to what they are really doing but if you are
trying to issue Deletes etc. via a gui when you can simply pass the query in
you are definitely going to slow things down. I suspect there are lots of
things you can do to speed things up but without knowing more about exactly
what you are doing and how it's pretty hard to say.
--
Andrew J. Kelly SQL MVP
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
> Many thanks
> Tony|||<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
>
Good to hear.
> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
>
Yeah... there's "ideal" and "reality" :-)
> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
Any idea why so much longer? More data or what?
> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
Yes. Generally in cases like this, as much as can be done on the server
should be. As you note, the speed improvements can be dramatic.
Ultimately this will probably sell them on moving more to SQL. Also, I'll
bet their network admins will notice the lower load as more is moved to the
DB and will thank you for it.
> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
"Maybe". There are some best practices, such as splitting log traffic to a
separate RAID 1 or RAID 10.
But, generally I'd look at code first. If you're already going from 600
seconds to 5 seconds, tweaking the server most likely won't get you another
120x improvement.
> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
Simple logging will help.
> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
>
Sounds like you're off to a good start already.
> Many thanks
> Tony