dts : how to process a varying list of csv files ?

Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSE S#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725

dts : how to process a varying list of csv files ?

Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725

dts : how to process a varying list of csv files ?

Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file withi
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Dat
Source="e:\tst.csv";Extende
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you ar
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can'
find a good help on the web
any one can help ?
Thanks in advance
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672Have a look at
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"dominique deboise" <dominique.deboise.1r1e0t@.no-mx.msusenet.com> wrote in
message news:dominique.deboise.1r1e0t@.no-mx.msusenet.com...
> Hi all,
> I need to upload multiple csv files into one dts.
> 1)
> Is there a simple standard way to retrieve the list of csv file within
> the dts ?
> 2)
> to process the csv files (various file name) I tried to use
> opendatasource (as I did for excel files) :
> select * from
> OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
> Source="e:\tst.csv";Extended
> properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
>
> Unfortunatly, I does not work , the message is :
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
> Make sure that the path name is spelled correctly and that you are
> connected to the server on which the file resides.]
>
> my path is correct, it might be a syntax & parameter error but I can't
> find a good help on the web
> any one can help ?
>
> Thanks in advance
>
> Dominique
>
> --
> dominique deboise
> ---
> dominique deboise's Profile:
> http://www.msusenet.com/member.php?userid=2231
> View this thread: http://www.msusenet.com/t-1870556725
>|||I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672

DTS - Multiple Files to Multiple Tables

I am trying to write (my first, unfortunatly) DTS, and am having some problems.

I need to be able to import multiple flatfiles (all in the same format, just with different schema), each one going into a different table. I have written an application to call my DTS, sending it variables for the tablename and the filename. This works fine when I test it on a single flatfile.

My problem is, the Tranformation object does not reset after each DTS call, so I get "Column does not exist" errors after the first successful import. I can go into the DTS Manager and reset the Transformation options, but that would defeat the purpose of automation. Is there anyway to reset, or another technique, the Transformation object so that it will continuosly work on files that use different schema?

I am very new at DTS, so please consider me "ignorant" when replying.

Thanks in advance.

- JordanExpansion:

I basically need a way to "remove all transformations and remap columns" automatically after, or before, each new flatfile is imported. I can't find an option to do this.

Any suggestions?

Or am I going about this task all wrong?|||I think you want to create a separate file connection a separate data source connection for each transformation. If file A has to successfully import before file B, C, or D can be imported, then link your steps together with workflow "on success" links like:

File A--transform--table A--On Success--File B--transform--table B--On Success--File C--transform--table C--On Success--File D--transform--table D

It is possible to modify your transformations through scripts, but that would be much more difficult. Just keep your file names the same and the four-step package can be re-used the next time you need to do this import.|||Thank you for the reply,

My problem is that I have a variable number of files. Maybe if I better explain what I'm trying to do, it would help.

Each month, we get updates for a database in the form of flatfiles. The flatfiles come with various names, and stored in various directories. Each flatfile name consist of the tablename that it belongs to.

I have written a small application that recursivly loops through all directories, and grabs the files. Then, it will call my DTS sending some variables: fileName and tableName. Using these variables, I would like to have the DTS import the fileName into the tableName with simple column-to-column mapping.

This process works for the first file. But the DTS retains the mapping, so the next file that is sent (with a different number of columns into a different table) stops the DTS because of "column does not exist" errors.

In a nutshell: I'd like to use a single DTS which will import one file into one table. But each call would use a different file that goes into a different table.

Thanks,|||I've decided to use BULK INSERT instead. My DTS still accepts a fileName and tableName, but now it just does a bulk insert from the file. This method seems to be working fine for me.

Thanks for the help,

DTS - Looping in a Data DrivenQuery

Hi Guys

I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET

I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.

Thanks in advance.

Cheers
Chris

The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********

' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")

DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))

DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))

DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database

' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")

Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )

DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript

If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If

If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If

If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If

string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop

Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......

Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub

Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub

in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)

Make sense??|||Hi Rokslide

I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.

It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,

Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.

Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)

Where are you working in Christchurch? I used to live and work there myself. :)|||Hi

I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.

Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.

Mmmmm country air... ;)

Let me know if you need any more help with that DTS package.

DTExec Reporting Options

Currently, we are running a Master Package with sub-Packages that are executed as a result. We run multiple days by executing a .bat file of DTExec commands. For Example:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /SET \Package.Variables[ReportingDate].Value;"1/02/2007" > etl_20070102.log
mkdir E:\ETL\ErrorLogs\Archive\20070102
copy E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070102

Date values are incremented for as many days as we want to run. The log gives progress information and the Started, Finished, Elapsed time for the the Master package.

We are interested in manipulating the script entries to get the Start, Finished, Elapsed time for the sub-Packages that are initiated by this script. I think that I could use the Reporting option:

Code Snippet

/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]

Of course I can't find a good example to model the script. Is there anyone else using DTExec to get the run time statistics for each and every package? If so, can you forward that part of the script that accomplishes this task? BTW, we are going to implement run-time auditing to a table at some point but we are not there yet. Of course, my manager would like statistics now.

Thanks in advance.

You could get all those details by enabling package logging on each package; that is just a few clicks away. You can choose where the log information is going to be: file, table etc.

Package logging is enable/disable at the package level, so you need to edit each package.

|||

If my package itself fails to load for some reason, the logging will not happen (this is my assumption), so how do I capture that. The DTEXEC return codes just says the package failed to load. For eg when I executing one of the packages I got the return code as 1, but the console displayed the below error.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14
(Only the owner of DTS Package 'kk-test' or a member of the sysadmin role may create new versions of it.).
The SQL statement that was issued has failed.

I dont think there is any way for me to capture this error.

Thanks

|||

You could try redirecting the output from DTEXEC to a file.

Code Snippet

DTEXEC [your package params] > log.txt

|||

Visual SSIS package execution stats (e.g. # executions, most recent execution timestamp, avg runtime , # failures, # successes, and all by machine) is available for "free" via SQL Server Reporting services, provided one uses the stock SQL Server log provider in each package.

These run time stats can be had via the following BI project: SSIS Log Provider Reports .

The stock SQL Server log provider writes to table named sysdtslog90 via the stored procedure sp_dts_addlogentry in whatever server instance/database is pointed to by the log provider's connection manager.

Now, since the report does hit the table directly, its probably a more reasonable approach to push the logging data into a cube periodically, reporting from there, but that feature is not included.

|||

jwelch wrote:

You could try redirecting the output from DTEXEC to a file.

Code Snippet

DTEXEC [your package params] > log.txt

This works if I execute the DTEXEC directly in the command prompt, but when I execute using WScript shell in vbscript it fails with return value 6 saying "The utility encountered an internal error of syntactic or semantic errors in the command line"

Code Snippet

strShellCommand = "DTEXEC /SQL "\pkg-1 " /SERVER MBIXDEV1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E > \\serv1\logs\test.tmp"
Set objWshShell = WScript.CreateObject("WScript.Shell")

lngReturnValue = objWshShell.Run(strShellCommand , vbNormalFocus, True)

So how do I make this work? If I copy the strShellCommand value to command prompt and run it works.

Thanks

|||

See Michael's post here: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

You probably need to run it as CMD.EXE /C DTEXEC [rest of your commandline] > log.txt

|||

Karunakaran,

We invoke a .bat file from the vbs script:

Code Snippet

IF (colFiles1.Count = 1 _
AND colFiles2.Count = 1 _
AND colFiles3.Count = 1 _
AND colFiles4.Count = 1 _
AND colFiles5.Count = 1 _
AND colFiles6.Count = 1 _
AND colFiles7.Count = 1) THEN
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "E:\\ETL\\daily_import_and_etl_with_cmd_input.bat 0807 8/7"
Wscript.Quit
END IF

The .bat file looks like this basically:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"%2/2007" > E:\ETL\ErrorLogs\Processing\etl_2007%1log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
MKDIR E:\ETL\ErrorLogs\Archive\2007%1
MOVE E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\2007%1

The vbs looks for files and then kicks off the ETL. The .bat file has the commands for the ETL.|||

Thanks John, cmd did the trick.

Unfortunately I cannot take the batch file approach because my sys admins will not allow, thanks for suggesting that though.

DTExec Reporting Options

Currently, we are running a Master Package with sub-Packages that are executed as a result. We run multiple days by executing a .bat file of DTExec commands. For Example:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /SET \Package.Variables[ReportingDate].Value;"1/02/2007" > etl_20070102.log
mkdir E:\ETL\ErrorLogs\Archive\20070102
copy E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070102

Date values are incremented for as many days as we want to run. The log gives progress information and the Started, Finished, Elapsed time for the the Master package.

We are interested in manipulating the script entries to get the Start, Finished, Elapsed time for the sub-Packages that are initiated by this script. I think that I could use the Reporting option:

Code Snippet

/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]

Of course I can't find a good example to model the script. Is there anyone else using DTExec to get the run time statistics for each and every package? If so, can you forward that part of the script that accomplishes this task? BTW, we are going to implement run-time auditing to a table at some point but we are not there yet. Of course, my manager would like statistics now.

Thanks in advance.

You could get all those details by enabling package logging on each package; that is just a few clicks away. You can choose where the log information is going to be: file, table etc.

Package logging is enable/disable at the package level, so you need to edit each package.

|||

If my package itself fails to load for some reason, the logging will not happen (this is my assumption), so how do I capture that. The DTEXEC return codes just says the package failed to load. For eg when I executing one of the packages I got the return code as 1, but the console displayed the below error.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14
(Only the owner of DTS Package 'kk-test' or a member of the sysadmin role may create new versions of it.).
The SQL statement that was issued has failed.

I dont think there is any way for me to capture this error.

Thanks

|||

You could try redirecting the output from DTEXEC to a file.

Code Snippet

DTEXEC [your package params] > log.txt

|||

Visual SSIS package execution stats (e.g. # executions, most recent execution timestamp, avg runtime , # failures, # successes, and all by machine) is available for "free" via SQL Server Reporting services, provided one uses the stock SQL Server log provider in each package.

These run time stats can be had via the following BI project: SSIS Log Provider Reports .

The stock SQL Server log provider writes to table named sysdtslog90 via the stored procedure sp_dts_addlogentry in whatever server instance/database is pointed to by the log provider's connection manager.

Now, since the report does hit the table directly, its probably a more reasonable approach to push the logging data into a cube periodically, reporting from there, but that feature is not included.

|||

jwelch wrote:

You could try redirecting the output from DTEXEC to a file.

Code Snippet

DTEXEC [your package params] > log.txt

This works if I execute the DTEXEC directly in the command prompt, but when I execute using WScript shell in vbscript it fails with return value 6 saying "The utility encountered an internal error of syntactic or semantic errors in the command line"

Code Snippet

strShellCommand = "DTEXEC /SQL "\pkg-1 " /SERVER MBIXDEV1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E > \\serv1\logs\test.tmp"
Set objWshShell = WScript.CreateObject("WScript.Shell")

lngReturnValue = objWshShell.Run(strShellCommand , vbNormalFocus, True)

So how do I make this work? If I copy the strShellCommand value to command prompt and run it works.

Thanks

|||

See Michael's post here: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

You probably need to run it as CMD.EXE /C DTEXEC [rest of your commandline] > log.txt

|||

Karunakaran,

We invoke a .bat file from the vbs script:

Code Snippet

IF (colFiles1.Count = 1 _
AND colFiles2.Count = 1 _
AND colFiles3.Count = 1 _
AND colFiles4.Count = 1 _
AND colFiles5.Count = 1 _
AND colFiles6.Count = 1 _
AND colFiles7.Count = 1) THEN
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "E:\\ETL\\daily_import_and_etl_with_cmd_input.bat 0807 8/7"
Wscript.Quit
END IF

The .bat file looks like this basically:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"%2/2007" > E:\ETL\ErrorLogs\Processing\etl_2007%1log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
MKDIR E:\ETL\ErrorLogs\Archive\2007%1
MOVE E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\2007%1

The vbs looks for files and then kicks off the ETL. The .bat file has the commands for the ETL.|||

Thanks John, cmd did the trick.

Unfortunately I cannot take the batch file approach because my sys admins will not allow, thanks for suggesting that though.

DTC in multiple instance cluster

WE have a 2 node win 2k3 x64 cluster (also x86 clusters with same problem)
with 2 instanses of SQL 2005 x64 and the MSDTC resource in the admin.
cluster group (quorum group).
This is a active/passive cluster, all 3 groups/vir. servers on same node.
The problem appears when one of the SQL servers fail over to the other node.
Then the MSDTC transactions fail on this sql server.
Result = we have to manually fail over the MSDTC resource/group also.
I have read about a DTC proxy but how do I get this to work ?
Bj?rn
Some say that this shouldn't happen, but we experienced this often as well.
The fix is to place the MS DTC in its own cluster resource group, which will
require another set of dedicated IP Addresses, physical disk, and network
name resources.
http://support.microsoft.com/kb/301600/en-us
Sincerely,
Anthony Thomas

"Bjrn" <Bjrn@.discussions.microsoft.com> wrote in message
news:251D1C52-C076-40A0-BE2B-2B5EF4031146@.microsoft.com...
> WE have a 2 node win 2k3 x64 cluster (also x86 clusters with same problem)
> with 2 instanses of SQL 2005 x64 and the MSDTC resource in the admin.
> cluster group (quorum group).
> This is a active/passive cluster, all 3 groups/vir. servers on same node.
> The problem appears when one of the SQL servers fail over to the other
node.
> Then the MSDTC transactions fail on this sql server.
> Result = we have to manually fail over the MSDTC resource/group also.
> I have read about a DTC proxy but how do I get this to work ?
> Bjrn
>

Dtabases over multiple instances

Hi all,
For performance reason, I want to devide all databases over multiple
instances.
Say 3 instances in a 4-node cluster(win2K3).Active/Active/Active/Passive
In this case, the application would need to know on which
VirtualServer/Instance the database resides.
If there is no way to tell the application, is there a solution within
SQL(2000) for it to do some kind of redirect to the rightServer/Instance
where the database does reside ?
I am looking for something similar like Exchange, where all servers in the
Organisation know where(which server) your mailbox resides.
So you could connect to any mail server and get directed to the one with
your mailbox.
Thx,
Erwin
Clustering doesn't work like that. Each instance owns a defined set of
databases. Each database can be owned by only one instance. The entire
instance fails as a unit to another host node should the first host node go
down. Clustering is not a scale-out technology.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<Eppie> wrote in message news:eHE27g9nEHA.3792@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> For performance reason, I want to devide all databases over multiple
> instances.
> Say 3 instances in a 4-node cluster(win2K3).Active/Active/Active/Passive
> In this case, the application would need to know on which
> VirtualServer/Instance the database resides.
> If there is no way to tell the application, is there a solution within
> SQL(2000) for it to do some kind of redirect to the rightServer/Instance
> where the database does reside ?
> I am looking for something similar like Exchange, where all servers in the
> Organisation know where(which server) your mailbox resides.
> So you could connect to any mail server and get directed to the one with
> your mailbox.
> Thx,
> Erwin
>
|||Thx,
I understand that.
I am looking for some kind of solution where you could connect to A database
without having to know which server(or instance) the database physically
resides(clustered or non-clustered).
As in the Exchange example, connecting to An Exhange server which will
redirect you to the server actually holding your mailbox.
It would be a good option for SQL to be able to do this too.
Thx,
Erwin
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OxwJwI%23nEHA.2904@.TK2MSFTNGP15.phx.gbl...
> Clustering doesn't work like that. Each instance owns a defined set of
> databases. Each database can be owned by only one instance. The entire
> instance fails as a unit to another host node should the first host node
go[vbcol=seagreen]
> down. Clustering is not a scale-out technology.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <Eppie> wrote in message news:eHE27g9nEHA.3792@.TK2MSFTNGP11.phx.gbl...
the
>
|||Hi Eppie,
You can puiblish the database to AD and let you're application search for
the database name in AD ; find the server/instance name and connect. This
solution however has nothing to do with clustering but counts for all SQL
sever installations.
The SQL server magazine had an article on this in their April number
providing explanation and code to get it working. (see link below)
http://www.winnetmag.com/Windows/Art...41/pg/1/1.html
(members only)
"Eppie" wrote:

> Thx,
> I understand that.
> I am looking for some kind of solution where you could connect to A database
> without having to know which server(or instance) the database physically
> resides(clustered or non-clustered).
> As in the Exchange example, connecting to An Exhange server which will
> redirect you to the server actually holding your mailbox.
> It would be a good option for SQL to be able to do this too.
> Thx,
> Erwin
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OxwJwI%23nEHA.2904@.TK2MSFTNGP15.phx.gbl...
> go
> the
>
>

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!