Converting DTS to Stored procedure

Hi,

I created a DTS package for importing a textfile, parse the data and insert it into some tables.

I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...

Now I have to convert the package into a stored procedure instead.
SQL Querys are no problem but the importing of the textfile is.
How do I do it?

First step is to import only row 0 to a table, second step is to import row 1 -> n

Any examples would be nice... :-)bcp or bulk insert...

what's row 0?

Probably a header...

Can you create a "work" table for your own use?

How many rows are we talking about?

Where is the data coming from (it's Access isn't...grrrrrr)

What do you mean by parse (SUBSTRING)?

Does your dog have fleas?

Why is the sky blue?

What's the name of your dba...(just kidding)

Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...|||Whats bcp?

The file contains a header in the first row and then tab separated data...

I have a rawtable that it is imported to now by DTS.

It's about 20000 rows, textfile...

By parsing i mean several queries that translates the data from the rawtable into other tables.

And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...

The sky is blue beacuse God have his blue underwear on...

My dba is named Sven.

--

I have tried to use xp_cmdshell but I'm not allowed too...

Seems like I have to do the import in my client.. written in C++.|||Hi,

You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine ... Hope this helps.

CREATE PROC dbo.DTSExecutePKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)
-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN 1
END

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

IF @.hr <> 0
BEGIN
RETURN 1
END

GO|||Got a:

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
*** Create Package object failed

--

Seems like I have no permission to run sp_OACreate either...

Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.

converting date field from Informix to SQL nvarchar

I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)

Any help is greatly appreciated!

JT

The goodness we share is rewarded twice!try to find the record that cause the failure:
select * From <your table name> where isdate(<date varchar column name>)=0

varchars in yyyy-mm-dd format usually converted to dates data type without problems...|||Thank you for the reply.

So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).

Run the select against the varchar column?|||You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0|||OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:

/*

Tuesday, April 13, 2004 13:20:17

User: sa

Server: NYCRPSTOR01

Database: Mysis

Application: MS SQLEM - Data Tools

*/

'findet' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.|||Originally posted by Brett Kaiser
You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0

Did you run this?

What did you get?

$1,000 bucks you've got non date data in that column|||That worked!!! Thank you for your help!!!|||What worked?

Did you find non date data?|||before you change the column type find what cause the conversion error!|||There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.

Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?

Again, thank you for your help! It is greatly appreciated!!!|||just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table

Converting comp to ASCII IN SQL

Hi
I am importing data from mainframe using DTS the problem is that I am getting some COMP fields from the mainframe these are compressed data fields that are used to reduce storage space on the main frame. Can some one please help me understand how we can use DTS to solve this problem that is decompress it before transferring to table in SQL server.
any code in scripting language to help decompress this datatype will be really appreciated.

Thanksthe SQL forum is for the SQL language

moving thread to Microsoft SQL Server forum|||The usage of the COBOL "COMP" type is actually implementation specific. See the notes on COMP (http://www.discinterchange.com/TechTalk_COBOL_comp_.html) at DiscInterchange or pretty much any COBOL book for a discussion of this problem.

If you know which type of mainframe the COMP field came from, I can offer a good guess at a decoder for it, but I don't know of any way to absolutely guarantee a good conversion for you.

-PatP

Convert table using DTS

Hi,
how to:
using dts convert row in table to column.
i have table:
col1,col2
b1 , 1
b2 , 2
b1 , 4
b3 , 3
b2 , 5
and i want using dts convert/rewrite this table to another table:
b1,b2,b3
1,null,null
null,2,null
4,null,null
null,null,3
null,5,null"tolo" <ultrapack@.ultrapack.pl> wrote in message news:<4120536f$1@.news.home.net.pl>...
> Hi,
> how to:
> using dts convert row in table to column.
> i have table:
> col1,col2
> b1 , 1
> b2 , 2
> b1 , 4
> b3 , 3
> b2 , 5
> and i want using dts convert/rewrite this table to another table:
> b1,b2,b3
> 1,null,null
> null,2,null
> 4,null,null
> null,null,3
> null,5,null

The easiest way is probably to use a TSQL query:

select
case when col1 = 'b1' then col2 else NULL end as 'b1',
case when col1 = 'b2' then col2 else NULL end as 'b2',
case when col1 = 'b3' then col2 else NULL end as 'b3'
from
dbo.MyTable

You could use this either in an Execute SQL task, or as the source for
a Transform Data task. If both databases are on the same server, or on
different servers on a well-connected network, then you could also do
a simple INSERT...SELECT...

Simon

convert Julian Date to Calendar Date

How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
following guideline found at Internet?
To convert Julian date to Gregorian date:
double JD = 2299160.5;
double Z = Math.Floor(JD+0.5);
double W = Math.Floor((Z - 1867216.25)/36524.25);
double X = Math.Floor(W/4);
double AA = Math.Floor(Z+1+W-X);
double BB = Math.Floor(AA+1524);
double CC = Math.Floor((BB-122.1)/365.25);
double DD = Math.Floor(365.25*CC);
double EE = Math.Floor((BB-DD)/30.6001);
double FF = Math.Floor(30.6001*EE);
double Day = BB-DD-FF;
double Month;
double Year;
if((EE-13) <= 12 && (EE-13) > 0)
Month = EE-13;
else
Month = EE-1;
if(Month == 1 || Month == 2)
Year = CC-4715;
else
Year = CC-4716;Sam
declare @.julian char(8)
select @.julian = '1996.031'
declare @.dt datetime
select @.dt =
DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.juli
an,1,4)+'0101',212))
select @.dt
"Sam" <cybersam88@.hotmail.com> wrote in message
news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
> How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
> following guideline found at Internet?
> To convert Julian date to Gregorian date:
> double JD = 2299160.5;
> double Z = Math.Floor(JD+0.5);
> double W = Math.Floor((Z - 1867216.25)/36524.25);
> double X = Math.Floor(W/4);
> double AA = Math.Floor(Z+1+W-X);
> double BB = Math.Floor(AA+1524);
> double CC = Math.Floor((BB-122.1)/365.25);
> double DD = Math.Floor(365.25*CC);
> double EE = Math.Floor((BB-DD)/30.6001);
> double FF = Math.Floor(30.6001*EE);
> double Day = BB-DD-FF;
> double Month;
> double Year;
> if((EE-13) <= 12 && (EE-13) > 0)
> Month = EE-13;
> else
> Month = EE-1;
> if(Month == 1 || Month == 2)
> Year = CC-4715;
> else
> Year = CC-4716;
>|||Well, if you really want to use that formula, then just assign a bunch of
values, along the lines of:
declare @.JD float,
@.Z float,
@.W float,
@.X float,
@.AA float,
@.BB float,
@.CC float,
@.DD float,
@.EE float,
@.FF float
set @.JD = 2299160.5
set @.Z = @.JD+0.5
set @.W = (@.Z - 1867216.25)/36524.25
set @.X = @.W/4.0
set @.AA = @.Z+1+@.W-@.X
set @.BB = @.AA+1524
set @.CC = (@.BB-122.1)/365.25
set @.DD = 365.25*@.CC
set @.EE = (@.BB-@.DD)/30.6001
set @.FF = 30.6001*@.EE
declare @.day float, @.month float, @.year float
set @.Day = @.BB-@.DD-@.FF
if ((@.EE-13) <= 12) and ((@.EE-13) > 0)
set @.Month = @.EE-13
else
set @.Month = @.EE-1
if(@.Month = 1 OR @.Month = 2)
set @.Year = @.CC-4715
else
set @.Year = @.CC-4716
select @.day, @.month, @.year
But I'm not sure that's right. I think a Julian date is just the number of
days since some date about 4000 BC... and that you do it like this:
declare @.mydate datetime
set @.mydate = dateadd(dd, @.JD-2451545, '1-jan-2000')
But this is assuming you just have an integer for your number. If it's a
float, then you will also want to do something like:
select dateadd(second,86400 * (@.JD - convert(bigint,@.JD)),@.mydate)
Which just adds on the fraction to the number of seconds.
Hope this helps,
Rob|||How about convert VB Script to T-SQL?
CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Sam
> declare @.julian char(8)
> select @.julian = '1996.031'
> declare @.dt datetime
> select @.dt =
> DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.ju
li
> an,1,4)+'0101',212))
> select @.dt
>
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Sam
declare @.julian char(8)
select @.julian = REPLACE('1996.031','.','')
SELECT CAST(CAST(@.julian /1000 AS CHAR(4))+'01'+CAST(@.julian %1000 AS
CHAR(2))AS DATETIME)
"Sam" <cybersam88@.hotmail.com> wrote in message
news:elKd5AwSFHA.3244@.TK2MSFTNGP15.phx.gbl...
> How about convert VB Script to T-SQL?
> CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
DATEADD(dd,convert(int,right(@.julian,3))
- 1,convert(datetime,substring(@.juli[color
=darkred]
>

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
Loane
Loane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRING(WEDate, 3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns is
>in the format char(6) rather than datetime. The problem seems to be that the
>text file column is in the format 'ddmmyy'. When I use the CONVERT statement
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
LoaneLoane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRI
NG(WEDate,3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns i
s
>in the format char(6) rather than datetime. The problem seems to be that th
e
>text file column is in the format 'ddmmyy'. When I use the CONVERT statemen
t
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message tha
t
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>