DTA package handling multiple file formats

Hi,

Currently we get data from more then 200 different sources and all of
our vendors provide data in different file formats. The problem is we
have more then 100 DTS packages now and the maintainance is very
diffucult.
Every time our vendor changes the format we have to change in multiple
DTS packages.
Is anybody know what would be the right way of reducing the no. of DTS
packages.
The type of file formats we get are .xls .txt .dat .csv etc. and .txt
& .dat files comes with different delimitters. The # of columns also
varies from file to file. Is it possible to have a DTS package which
can handle diff file formats and loads data into a staging table and
from there based of the source of the file we can move data into
respective tables & columns.

We are using SQL SERVER 2000

Thanks in advance.

Subodh"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0501240846.58b2b293@.posting.google.c om...
> Hi,
> Currently we get data from more then 200 different sources and all of
> our vendors provide data in different file formats. The problem is we
> have more then 100 DTS packages now and the maintainance is very
> diffucult.
> Every time our vendor changes the format we have to change in multiple
> DTS packages.
> Is anybody know what would be the right way of reducing the no. of DTS
> packages.
> The type of file formats we get are .xls .txt .dat .csv etc. and .txt
> & .dat files comes with different delimitters. The # of columns also
> varies from file to file. Is it possible to have a DTS package which
> can handle diff file formats and loads data into a staging table and
> from there based of the source of the file we can move data into
> respective tables & columns.
> We are using SQL SERVER 2000
> Thanks in advance.
> Subodh

Personally, I would look at writing an external script or program in C#,
Python, Perl or whatever to manipulate the files and load the staging table.
The script could load the data directly to the staging table by dynamically
generating INSERTs, or it might transform the source files to your own
standard file format to be used with bcp.exe, BULK INSERT or the DTS Bulk
Insert task.

Your maintenance efforts would be then directed at the program, not at the
packages, which is probably a good thing - it's likely easier to modify one
module/class/object than 10 packages, and most languages have good library
support for parsing, tokenizing, regexes and so on. Or perhaps a hybrid
solution might work - an external program for proprietary file formats, and
standard DTS connections/tasks for the rest. You might also want to ask in
microsoft.public.sqlserver.dts to see if someone else has experienced a
similar situation.

Finally, since your basic issue (as I understood it) is that you have too
many file formats, you should consider agreeing a standard file format - at
least with your larger clients/vendors - rather than looking at it just as a
technical problem. I have no idea how easy that would be in your company's
situation, of course.

Simon|||
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DTA Compatibility

Runing the next query using DTA:

selectLTrim(RTrim(CodCtaCte))as CodCtaCte, Nombre from CtaCtes where CuePrefi ='C'orderby Nombre

with SQL Server 2000 SP4 instance, I got this error:

"Tuning process exit unexpectedly"

Now, the same DTA and query but using SQL Server 2005 SP2 instances, works fine.

Both insances are in the same Server with Windows 2003.

Any clue?

Moving to the SQL Server forums from Katmai. The Katmai forums are for SQL Server 2008.

Thanks,
Phil|||May be my lack of English is the reason for this move.

In my original post I was talking about Katmai or SQL Server 2008.

Thanks,

Luis

DTA Compatibility

Phil move this post to SQL 2005 forum.

I suppose my lack of English was the reason, so I post it again.

I'm working with DTA Katmai SQL Server 2008.

Runing the next query using DTA:

selectLTrim(RTrim(CodCtaCte))as CodCtaCte, Nombre from CtaCtes where CuePrefi ='C'orderby Nombre

with SQL Server 2000 SP4 instance, I got this error:

"Tuning process exit unexpectedly"

Now, the same DTA and query but using SQL Server 2005 SP2 instances, works fine.

Both insances are in the same Server with Windows 2003.

Any clue?

Luis

I guess there was bug within SQL 2005 too working with DTA as that was found to be a bug in DTA in dealing with indexed views, in your case I woudln't take a guess that you are using Indexed views or any of them are involved or not.

|||Satya (old friend),

I never had a problem with DTA 2005 and SQL 2000.
If fact, DTA 2005 provide better indexes than ITW.
May be is a Katmai bug.|||So, I have to understand Katmai will not support sql 2000. I'm right?|||

From your posting it seems that DTA Katmai against Shiloh fails while DTA Katmai against Yukon works.Is this correct?

Potentially this is hitting a bug when DTA tries to analyze the showplan_parsing output for a query . This is not a problem in SQL Server 2005 since we use the more robust showplan_xml interface.

Can you check if there are any dtaengine mini dumps in the ErrorDumps Folder ? (typically the path is C:\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps) .If so we can take a look at the minidumps

Please let me know

Manoj

|||Your first question is correct.

Also there is no problem with DTA 2008 and SQL 2005.

There is nothing in ErrorDumps.

Thanks.|||

Hi Luis

Given that there are no user mode dumps the best way to proceed would be to file the problem as a bug using the connect program. It will be easier to debug if you have a representative database and workload.

Let us know if you have any questions on how to proceed

Manoj

DTA Compatibility

Phil move this post to SQL 2005 forum.

I suppose my lack of English was the reason, so I post it again.

I'm working with DTA Katmai SQL Server 2008.

Runing the next query using DTA:

select LTrim(RTrim(CodCtaCte)) as CodCtaCte, Nombre from CtaCtes where CuePrefi = 'C' order by Nombre

with SQL Server 2000 SP4 instance, I got this error:

"Tuning process exit unexpectedly"

Now, the same DTA and query but using SQL Server 2005 SP2 instances, works fine.

Both insances are in the same Server with Windows 2003.

Any clue?

Luis

I guess there was bug within SQL 2005 too working with DTA as that was found to be a bug in DTA in dealing with indexed views, in your case I woudln't take a guess that you are using Indexed views or any of them are involved or not.

|||Satya (old friend),

I never had a problem with DTA 2005 and SQL 2000.
If fact, DTA 2005 provide better indexes than ITW.
May be is a Katmai bug.|||So, I have to understand Katmai will not support sql 2000. I'm right?|||

From your posting it seems that DTA Katmai against Shiloh fails while DTA Katmai against Yukon works.Is this correct?

Potentially this is hitting a bug when DTA tries to analyze the showplan_parsing output for a query . This is not a problem in SQL Server 2005 since we use the more robust showplan_xml interface.

Can you check if there are any dtaengine mini dumps in the ErrorDumps Folder ? (typically the path is C:\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps) .If so we can take a look at the minidumps

Please let me know

Manoj

|||Your first question is correct.

Also there is no problem with DTA 2008 and SQL 2005.

There is nothing in ErrorDumps.

Thanks.|||

Hi Luis

Given that there are no user mode dumps the best way to proceed would be to file the problem as a bug using the connect program. It will be easier to debug if you have a representative database and workload.

Let us know if you have any questions on how to proceed

Manoj

DTA Compatibility

Phil move this post to SQL 2005 forum.

I suppose my lack of English was the reason, so I post it again.

I'm working with DTA Katmai SQL Server 2008.

Runing the next query using DTA:

select LTrim(RTrim(CodCtaCte)) as CodCtaCte, Nombre from CtaCtes where CuePrefi = 'C' order by Nombre

with SQL Server 2000 SP4 instance, I got this error:

"Tuning process exit unexpectedly"

Now, the same DTA and query but using SQL Server 2005 SP2 instances, works fine.

Both insances are in the same Server with Windows 2003.

Any clue?

Luis

I guess there was bug within SQL 2005 too working with DTA as that was found to be a bug in DTA in dealing with indexed views, in your case I woudln't take a guess that you are using Indexed views or any of them are involved or not.

|||Satya (old friend),

I never had a problem with DTA 2005 and SQL 2000.
If fact, DTA 2005 provide better indexes than ITW.
May be is a Katmai bug.|||So, I have to understand Katmai will not support sql 2000. I'm right?|||

From your posting it seems that DTA Katmai against Shiloh fails while DTA Katmai against Yukon works.Is this correct?

Potentially this is hitting a bug when DTA tries to analyze the showplan_parsing output for a query . This is not a problem in SQL Server 2005 since we use the more robust showplan_xml interface.

Can you check if there are any dtaengine mini dumps in the ErrorDumps Folder ? (typically the path is C:\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps) .If so we can take a look at the minidumps

Please let me know

Manoj

|||Your first question is correct.

Also there is no problem with DTA 2008 and SQL 2005.

There is nothing in ErrorDumps.

Thanks.|||

Hi Luis

Given that there are no user mode dumps the best way to proceed would be to file the problem as a bug using the connect program. It will be easier to debug if you have a representative database and workload.

Let us know if you have any questions on how to proceed

Manoj

DTA Common Error and sp1

Does anyone know if sp1 resolves the common DTA error "failed to initialize CRT"? I found this KB article on it http://support.microsoft.com/Default.aspx?kbid=913395, yet this resolution did not work for my boxes (i have both a laptop and desktop with 2005/build 1399 developer). I did not notice this error being listed in the http://support.microsoft.com/kb/916940

Thanks,

Derek

Guys,

2.Also please check if an environment variable __COMPAT_LAYER is set?
"set __COMPAT_LAYER=" , basically disabled the compat layer and dta
ran OK

i added this EV and DTA now works, but I shouldnt have to do this. Does sp1 resolve and if not I assume sp2?

|||I went ahead and applied sp1 all common components (this DOES INCLUDE CLIENT COMPONENTS) of sp1/x86 and sure enough it works? Where is this listed in the sp1 fixes?|||for the sake of clarity, the first fix (environmental variable) was applied to my laptop. the second fix (sp1 common components) was applied to my desktop.

DTA (Database Tuning Advisor) Malfunction, no error message

Hello, I am having an extremely odd problem with the Database Engine Tuning Advisor, on SQL Server 2005 Enterprise x64. It was working fine, and I was tuning the queries for a bunch of database views we have created. About 2/3 of the way through the list DTA began getting stuck on "Submitting Configuration Information" every time it was run.

I get no error messages, it just sits and spins on Submitting Configuration Information for days and days at a time. When other users view my session, its status says ready as though it had never started, but I am unable to start nor stop it until I close out of it and simply delete it out of the Session Monitor on the left. Other users are able to run sessions without issue, and nothing has changed on the server. I get the same behavior whether I am tuning a query or a workload file. **Edit** A workload file eventually died on the first step with the nebulous "Tuning process exited unexpectedly." There are no entries in dbo.DTA_tuninglog. In dbo.DTA_progress, only TuningStage 0 is present for that SessionID.

I have tried using a different SQL user, rebooting, uninstalling all SQL Server components from my PC then re-installing them, and deleting out all previous DTA session data with no luck. We have also recently installed SP2 for SQL Server 2005 on the server, and I updated my local installation with Microsoft Update. The problem persists. I would appreciate any input you could provide!

Thanks,
Konrad Willmert
Indiana Wesleyan University

I am having the Same Problem, I have no Idea what to do to fix it.

I get this error even after a complete Uninstall (Per the Book) and ReInstallation (Per the Book).

I have tried 3 times.

|||I am having a similar problem. I can get it to start consuming the workload, but it errors out every time. My SQL version is 9.0.3042 so I don't think KB 917131 applies. Was there ever a resolution?|||Shelly,

I have neither found nor received any resolution to this problem--other than asking my co-workers to tune things for me.
|||I gave up as well. I am just using other systems to do the testing.

DTA (Database Tuning Advisor) Malfunction, no error message

Hello, I am having an extremely odd problem with the Database Engine Tuning Advisor, on SQL Server 2005 Enterprise x64. It was working fine, and I was tuning the queries for a bunch of database views we have created. About 2/3 of the way through the list DTA began getting stuck on "Submitting Configuration Information" every time it was run.

I get no error messages, it just sits and spins on Submitting Configuration Information for days and days at a time. When other users view my session, its status says ready as though it had never started, but I am unable to start nor stop it until I close out of it and simply delete it out of the Session Monitor on the left. Other users are able to run sessions without issue, and nothing has changed on the server. I get the same behavior whether I am tuning a query or a workload file. **Edit** A workload file eventually died on the first step with the nebulous "Tuning process exited unexpectedly." There are no entries in dbo.DTA_tuninglog. In dbo.DTA_progress, only TuningStage 0 is present for that SessionID.

I have tried using a different SQL user, rebooting, uninstalling all SQL Server components from my PC then re-installing them, and deleting out all previous DTA session data with no luck. We have also recently installed SP2 for SQL Server 2005 on the server, and I updated my local installation with Microsoft Update. The problem persists. I would appreciate any input you could provide!

Thanks,
Konrad Willmert
Indiana Wesleyan University

I am having the Same Problem, I have no Idea what to do to fix it.

I get this error even after a complete Uninstall (Per the Book) and ReInstallation (Per the Book).

I have tried 3 times.

|||I am having a similar problem. I can get it to start consuming the workload, but it errors out every time. My SQL version is 9.0.3042 so I don't think KB 917131 applies. Was there ever a resolution?|||Shelly,

I have neither found nor received any resolution to this problem--other than asking my co-workers to tune things for me.
|||I gave up as well. I am just using other systems to do the testing.

DTA (Database Tuning Advisor) Malfunction, no error message

Hello, I am having an extremely odd problem with the Database Engine Tuning Advisor, on SQL Server 2005 Enterprise x64. It was working fine, and I was tuning the queries for a bunch of database views we have created. About 2/3 of the way through the list DTA began getting stuck on "Submitting Configuration Information" every time it was run.

I get no error messages, it just sits and spins on Submitting Configuration Information for days and days at a time. When other users view my session, its status says ready as though it had never started, but I am unable to start nor stop it until I close out of it and simply delete it out of the Session Monitor on the left. Other users are able to run sessions without issue, and nothing has changed on the server. I get the same behavior whether I am tuning a query or a workload file. **Edit** A workload file eventually died on the first step with the nebulous "Tuning process exited unexpectedly." There are no entries in dbo.DTA_tuninglog. In dbo.DTA_progress, only TuningStage 0 is present for that SessionID.

I have tried using a different SQL user, rebooting, uninstalling all SQL Server components from my PC then re-installing them, and deleting out all previous DTA session data with no luck. We have also recently installed SP2 for SQL Server 2005 on the server, and I updated my local installation with Microsoft Update. The problem persists. I would appreciate any input you could provide!

Thanks,
Konrad Willmert
Indiana Wesleyan University

I am having the Same Problem, I have no Idea what to do to fix it.

I get this error even after a complete Uninstall (Per the Book) and ReInstallation (Per the Book).

I have tried 3 times.

|||I am having a similar problem. I can get it to start consuming the workload, but it errors out every time. My SQL version is 9.0.3042 so I don't think KB 917131 applies. Was there ever a resolution?|||Shelly,

I have neither found nor received any resolution to this problem--other than asking my co-workers to tune things for me.
|||I gave up as well. I am just using other systems to do the testing.

DTA (Database Engine Tuning Advisor) error when opening connection

Hi all,

I've got some problem to launch DTA, after the connection, I've got this message :

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

Failed to open a new connection.

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

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.connectButton_Click(Object sender, EventArgs e)

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

Failed to establish a connection to the server.

Any ideas ?

Information : SQL Server 2005 beta 2 Enterprise edition CTP June 2005

Thanks for your futurse answers.

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

|||

Manojas wrote:

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

I am using the evaluation copy of SQL2005 that I downloaded yesterday and having the same or similar problem. I can use the Management Studio to connect to the database without any problem.

The following is the details of the error:

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

Failed to open a new connection.

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

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

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

Failed to establish a connection to the server.

DTA (Database Engine Tuning Advisor) error when opening connection

Hi all,

I've got some problem to launch DTA, after the connection, I've got this message :

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

Failed to open a new connection.

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

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.connectButton_Click(Object sender, EventArgs e)

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

Failed to establish a connection to the server.

Any ideas ?

Information : SQL Server 2005 beta 2 Enterprise edition CTP June 2005

Thanks for your futurse answers.

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

|||

Manojas wrote:

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

I am using the evaluation copy of SQL2005 that I downloaded yesterday and having the same or similar problem. I can use the Management Studio to connect to the database without any problem.

The following is the details of the error:

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

Failed to open a new connection.

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

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

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

Failed to establish a connection to the server.

DTA (Database Engine Tuning Advisor) error when opening connection

Hi all,

I've got some problem to launch DTA, after the connection, I've got this message :

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

Failed to open a new connection.

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

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.connectButton_Click(Object sender, EventArgs e)

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

Failed to establish a connection to the server.

Any ideas ?

Information : SQL Server 2005 beta 2 Enterprise edition CTP June 2005

Thanks for your futurse answers.

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

|||

Manojas wrote:

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

I am using the evaluation copy of SQL2005 that I downloaded yesterday and having the same or similar problem. I can use the Management Studio to connect to the database without any problem.

The following is the details of the error:

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

Failed to open a new connection.

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

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

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

Failed to establish a connection to the server.