DTS question - need your help!

I am trying to use DTS load Excel data to SQL Server

The Excel Spreadsheet has 100 columns in each row. Let say I have 3 rows in Excel sheet.
Each row has 100 columns as follows,

column1, column2, ...column100

Now I want to load it to a table which has only one column.

The first record will be the column1 of the first row from the excel spreadsheet
The second record will be the column2 of the first row from the excel spreadsheet
...
The 100th record will be the column100 of the first row from the excel spreadsheet

The 101th record will be the column1 of the second row from the excel spreadsheet
...

Can anyone give me a suggections? Thanks in advance,

PeterHmm, any way you can transpose the columns in Excel? I can't think of a simple way to create an SQL Statement to transpose the columns in DTS. It might be easier to manipulate Excel, rather than trying to do the transpose in DTS.|||I agree with the first post, but you could do it starting with DTS. Suck the excel sheet into a 'holding' table and then run a stored procedure that takes each cell and makes it a record by itself. You could use a cursor to step through the records and do 100 inserts to the final table for each row in the holding table.|||Is the order truyly that important?

You can get all the data in one column by using the following SQL statement instead of a table as your source:

SELECT Column1
FROM [Sheet1$]
UNION ALL
SELECT Column2
FROM [Sheet1$]
UNION ALL
SELECT Column3
FROM [Sheet1$]
...

You'll need a lot of cut and past (or a handy little script) to generate all the UNIONed selects.

If the order truly is important, then I'd add another column to your spreadsheet for the row number. Import the entire spreadsheet into the a temp table using something like this:

SELECT TheValue = Column1,
TheRow = RowNumber, -- your row number column
TheColumn = 1
FROM [Sheet1$]
UNION ALL
...

Increment the column number for each column. Again, a script will write this monster select for you. Then you can insert into your final table:

INSERT ...
SELECT TheValue
FROM MyTempImportTable
ORDER by TheRow ASC, TheColumn ASC

FYI - You may need to use UNION rather than UNION ALL. I don't recall the exact workings of EXCEL with respect to the ALL part. A little experimenting (or a syntax) error should clear it up pretty quickly.

DTS Performance Issues?

I have roughly ~80 machines periodically performing DTS transfers to a central server. The central server apparently can't handle this load. When watching in the performance monitor, after about one hour of being online, disk use is steadily pegged at 100% and the server becomes unresponsive. How do I track this down further and further diagnose/resolve this performance issue.

I can look at the current activity window but I don't know how to interpret or use the results.

thanks!Roger,

I wonder would it be easier to pull the data rather than push from the 80+ clients ? At least the server would be able to schedule them sequentially.

How much data are we talking about here ? Improvements might be made via indexing etc on the server table side ?

Mark|||Just a thought, may be you can export the data to a media as files and have the server import the data from the media as file import one by one.

DTS Package error

Hi,
I am using a DTS package to retrieve data from one set of tables /flat files and load into another set of tables. The package is scheduled to run daily. After the failur/success of every step I get an email notification.
But sometimes the package starts, i get the email notification for that ..and then the package never proceeds. Neither I get a failure message nor the success message for the next step. When i checked in the log files I got the attached file, with the error code
8004043B.

This package calls some other packages. But what I feel is that its not completing even the first step.Hence the child packages are not being called.

Please help!!!!Enable logging for this package, so that the error message will be recorded in the log file.

Try setting the "Execute on main package thread" option. Right-click the
task, Workflow Properties, Options tab.|||Are you calling an external VB executable through your DTS ?

DTS Package - AS400 data load to SQL Server

I'm trying to convert numeric dates on the AS400 to smalldatetime in
SQL. I'm able to customize to match format but when I save the
tranformation I get the following error. I don't know why they sould
let you customize the field if you cant convert. Any ideas?
"Error Description: TranformationDateTimeString('X') column pair 1
(source column 'Y'(DBTYPE_NUMERIC), destination column 'Z'
(DBTYPE_DBTIMESTAMP)): Cannot convert source datatype to datetime
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!If this is an import, try dumping it to a temp table then let the MS drivers
do the job natively. What drivers are you using for this? client access?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

DTS package

I'm using SQL 7.0 (but may have the same problem in SQL 2000). The
problem is: I created a DTS package to load data from a csv file. The
package works fine by manually start, but cannot be scheduled as a
job. As soon as it becomes a JOB, it doesn't work. Can anyone give me
an idea?
Thanks.

Saiyou"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0403020842.249335a@.posting.google.co m...
> I'm using SQL 7.0 (but may have the same problem in SQL 2000). The
> problem is: I created a DTS package to load data from a csv file. The
> package works fine by manually start, but cannot be scheduled as a
> job. As soon as it becomes a JOB, it doesn't work. Can anyone give me
> an idea?
> Thanks.
> Saiyou

http://support.microsoft.com/?kbid=269074

Simon

DTS MAPI profile

I get "Unable to load MAPI profiles" error message in DTS Designer trying to
add "Send Mail" task.
SQLMail has been installed and configured (and tested, it works) on the same
server.
Please help, thank you in advanceWhen in the designer, it's using the current users windows
account and the current users mail profile from the location
or PC where you start up DTS.
Are you opening up the designer while logged onto the server
using the service account or are you doing this from your
PC? If it's from a PC, it doesn't matter what you have setup
on the server.
-Sue
On Fri, 11 Aug 2006 13:54:02 -0700, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:

>I get "Unable to load MAPI profiles" error message in DTS Designer trying t
o
>add "Send Mail" task.
>SQLMail has been installed and configured (and tested, it works) on the sam
e
>server.
>Please help, thank you in advance|||I'm logging on using service account (MSSQLServer and SQLServerAgent run
under this account)
"Sue Hoegemeier" wrote:

> When in the designer, it's using the current users windows
> account and the current users mail profile from the location
> or PC where you start up DTS.
> Are you opening up the designer while logged onto the server
> using the service account or are you doing this from your
> PC? If it's from a PC, it doesn't matter what you have setup
> on the server.
> -Sue
> On Fri, 11 Aug 2006 13:54:02 -0700, Leon Shargorodsky
> <LeonShargorodsky@.discussions.microsoft.com> wrote:
>
>|||If you are logging onto the server directly and logging on
using the service account used by SQL Server and SQL Agent.
And if from here you can see the mail profiles, they have
all been setup, tested and they are working correctly, and
from here you open up the DTS designer and it can't find
profiles when adding a Send Mail task, then I have no idea.
That sounds like a problem with the mail profile itself but
apparently your SQL Mail and SQL Agent Mail are both working
fine. Pretty much all of the typical issues that cause the
error you report as not being applicable so I haven't a
clue. Other than MAPI just is unreliable crap.
-Sue
On Mon, 14 Aug 2006 11:10:02 -0700, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I'm logging on using service account (MSSQLServer and SQLServerAgent run
>under this account)
>"Sue Hoegemeier" wrote:
>

DTS MAPI profile

I get "Unable to load MAPI profiles" error message in DTS Designer trying to
add "Send Mail" task.
SQLMail has been installed and configured (and tested, it works) on the same
server.
Please help, thank you in advanceWhen in the designer, it's using the current users windows
account and the current users mail profile from the location
or PC where you start up DTS.
Are you opening up the designer while logged onto the server
using the service account or are you doing this from your
PC? If it's from a PC, it doesn't matter what you have setup
on the server.
-Sue
On Fri, 11 Aug 2006 13:54:02 -0700, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:
>I get "Unable to load MAPI profiles" error message in DTS Designer trying to
>add "Send Mail" task.
>SQLMail has been installed and configured (and tested, it works) on the same
>server.
>Please help, thank you in advance|||I'm logging on using service account (MSSQLServer and SQLServerAgent run
under this account)
"Sue Hoegemeier" wrote:
> When in the designer, it's using the current users windows
> account and the current users mail profile from the location
> or PC where you start up DTS.
> Are you opening up the designer while logged onto the server
> using the service account or are you doing this from your
> PC? If it's from a PC, it doesn't matter what you have setup
> on the server.
> -Sue
> On Fri, 11 Aug 2006 13:54:02 -0700, Leon Shargorodsky
> <LeonShargorodsky@.discussions.microsoft.com> wrote:
> >I get "Unable to load MAPI profiles" error message in DTS Designer trying to
> >add "Send Mail" task.
> >SQLMail has been installed and configured (and tested, it works) on the same
> >server.
> >
> >Please help, thank you in advance
>|||If you are logging onto the server directly and logging on
using the service account used by SQL Server and SQL Agent.
And if from here you can see the mail profiles, they have
all been setup, tested and they are working correctly, and
from here you open up the DTS designer and it can't find
profiles when adding a Send Mail task, then I have no idea.
That sounds like a problem with the mail profile itself but
apparently your SQL Mail and SQL Agent Mail are both working
fine. Pretty much all of the typical issues that cause the
error you report as not being applicable so I haven't a
clue. Other than MAPI just is unreliable crap.
-Sue
On Mon, 14 Aug 2006 11:10:02 -0700, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:
>I'm logging on using service account (MSSQLServer and SQLServerAgent run
>under this account)
>"Sue Hoegemeier" wrote:
>> When in the designer, it's using the current users windows
>> account and the current users mail profile from the location
>> or PC where you start up DTS.
>> Are you opening up the designer while logged onto the server
>> using the service account or are you doing this from your
>> PC? If it's from a PC, it doesn't matter what you have setup
>> on the server.
>> -Sue
>> On Fri, 11 Aug 2006 13:54:02 -0700, Leon Shargorodsky
>> <LeonShargorodsky@.discussions.microsoft.com> wrote:
>> >I get "Unable to load MAPI profiles" error message in DTS Designer trying to
>> >add "Send Mail" task.
>> >SQLMail has been installed and configured (and tested, it works) on the same
>> >server.
>> >
>> >Please help, thank you in advance
>>

DTS load to multiple tables

I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I’m not sure if I am using the right tool for the job.I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database.Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables.There are no unique ids in the source data since it is coming from a 3^rd party and the tables are not related to others.I’ve created a DTS Package and have the beginnings of several Transform Data Tasks.Each destination table has an Identity id column, which is calculated automatically.The roadblock I’ve run into is that I can’t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key.Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together.The Transform Data Task only allows me to reference one source and one destination, not multiple destinations.I hope this makes sense.Any suggestions would be appreciated.

Here’s a simple example that may help illustrate the problem.My database is much more complex.

Input table is called parcels and each row has three columns:Address, Owner, and Legal_Description.

Output database has two tables that will receive this data:Parcel table will have the Address, Owner and Parcel_ID (auto calculated).Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.

When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured.Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.

How can I do this through DTS?Thanks for any suggestions, code snippets, or references.

Due to the complexity of this task, I would suggest maybe doing this in a .NET winforms application. Set up ODBC connections to the two databases. Now write some queries in the Access database to divide the data up appropriately. Next, write insert procedures in the SQL Server database, which return an outparameter which is the id field. To get this, in the insert proc, use the @.@.IDENTITY or the SCOPE_IDENTITY calls to get the id value of the inserted row. Capture this in the .NET application, and pass this in to the insert proc in the related table. Alternatively, keep a cache of the data mappings, maybe in a temporary table or a dataset, and do the inserts as bulk inserts. Then run an update procedure which sets the foreign key based on the database mappings in the original database. So, for example, based on the Legal_Description in the legal table, update the Parcel_Id in the legal table using the owner and address values that used to share a row with the Legal_Description. Either approach should work.

let me know if you need more guidance here. Some of the SQL database engine people, or the SSIS people may be able to point you to a DTS solution that can do this. Alternatively, you could create a DTS solution that inserts into one table, inserts into the other, then calls the update procedure as described above. There is a separate SSIS forum (the new DTS), you may be better posting the question there.

HTH

For more T-SQL tips, check out my blog:

DTS load master file to different tables

We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):

For instance, my file has:

20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900

If "M", the fields are "age", "gender", "birthdate", "state", "salary"

If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"

We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.

Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.

Does anyone has any idea on how to achieve this? Thanks in advance!!!It should be:

If "F", the fields are "age", "gender", "birthdate", "state", "salary"

If "M", the fields are "age", "gender", "birthdate", "state", "company", "salary"|||Since your data sets are obviously not fixed-length, you could use a temporary master table into which you import all data sets, e.g. into only one column of varchar(nnn).

Then use queries with string-functions to split the data into the correct number and type of columns for the respective tables M_table and F_table.|||kbk's solution is probably simpler (and therefore usually better). You may also consider a Data Driven Query task. It won't be entirely straightforward and it will require a substantial amount of work in the ActiveX script component (and it's performance will be slower).

But other than these drawbacks, it may work!

Regards,

hmscott

DTS load

New to SQL Server :(
load a txt file to SQL
there is a field, data type is numeric (13,6).
But in the file, it is like this: 9999999999999
How do I add the decimal points to make it: 9999999.999999??
Thanks a lotOriginally posted by qingbo
New to SQL Server :(

load a txt file to SQL

there is a field, data type is numeric (13,6).
But in the file, it is like this: 9999999999999

How do I add the decimal points to make it: 9999999.999999??

Thanks a lot

Add an Execute SQL Task object to the package and insert code something similar to this:

UPDATE tablename
SET ColumnName = ColumnName / 1000000.00

Where ColumnName is the numeric(13, 6) column defined in the table.

Hope this helps.

DTS import

Hello!
Is it possible to load dts package from structured storage file into
SQL server!
Thank you
Yes, in SQL Enterprise Manager you must use Package OPEN to open the
package, then you can do a file save. and in the save dialog you may change
the package to a SQL Serve package..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"davor@.home" <davor.tarandek@.ck.t-com.hr> wrote in message
news:dfc3em$8et$1@.ss405.t-com.hr...
> Hello!
> Is it possible to load dts package from structured storage file into SQL
> server!
> Thank you
|||TO get at the File open in SEM, Go to DTS Packages tab-Open... Also it is
easy to miss the dropdown where you can select how to save the package , but
it is there
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"davor@.home" <davor.tarandek@.ck.t-com.hr> wrote in message
news:dfc3em$8et$1@.ss405.t-com.hr...
> Hello!
> Is it possible to load dts package from structured storage file into SQL
> server!
> Thank you

DTS import

Hello!
Is it possible to load dts package from structured storage file into
SQL server!
Thank youYes, in SQL Enterprise Manager you must use Package OPEN to open the
package, then you can do a file save. and in the save dialog you may change
the package to a SQL Serve package..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"davor@.home" <davor.tarandek@.ck.t-com.hr> wrote in message
news:dfc3em$8et$1@.ss405.t-com.hr...
> Hello!
> Is it possible to load dts package from structured storage file into SQL
> server!
> Thank you|||TO get at the File open in SEM, Go to DTS Packages tab-Open... Also it is
easy to miss the dropdown where you can select how to save the package , but
it is there
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"davor@.home" <davor.tarandek@.ck.t-com.hr> wrote in message
news:dfc3em$8et$1@.ss405.t-com.hr...
> Hello!
> Is it possible to load dts package from structured storage file into SQL
> server!
> Thank you

DTS import

Hello!
Is it possible to load dts package from structured storage file into
SQL server!
Thank youYes, in SQL Enterprise Manager you must use Package OPEN to open the
package, then you can do a file save. and in the save dialog you may change
the package to a SQL Serve package..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"davor@.home" <davor.tarandek@.ck.t-com.hr> wrote in message
news:dfc3em$8et$1@.ss405.t-com.hr...
> Hello!
> Is it possible to load dts package from structured storage file into SQL
> server!
> Thank you|||TO get at the File open in SEM, Go to DTS Packages tab-Open... Also it is
easy to miss the dropdown where you can select how to save the package , but
it is there
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"davor@.home" <davor.tarandek@.ck.t-com.hr> wrote in message
news:dfc3em$8et$1@.ss405.t-com.hr...
> Hello!
> Is it possible to load dts package from structured storage file into SQL
> server!
> Thank you