DTS problem\bug

We have a server W2k\SQL2k in which we are able to open a DTS designer, save
the package, but on refresh the DTS package is not listed. We rebooted the
server, and run set of DBCCâ's which all came clean. Did anyone experience
similar issue?Hi
I have not seen this behavour before and it's not listed on
http://www.sqldts.com/default.aspx?106
Are you saving the package into the database? If you save it into a
structured file, what happens if you try to import it and save it into the
database?
If this package is being imported from elsewhere, are you at the same
service pack and MDAC levels?
If all that fails you may want to run profiler to see that the package gets
inserted into msdb.
John
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:E60E3786-0D0D-4FA8-B169-4C8918C5DBED@.microsoft.com...
> We have a server W2k\SQL2k in which we are able to open a DTS designer,
save
> the package, but on refresh the DTS package is not listed. We rebooted the
> server, and run set of DBCC's which all came clean. Did anyone experience
> similar issue?

DTS Permissions

Assuming you can open the package, simply click on
package in the upper left and save as...
>--Original Message--
>We have a DTS package that was created and saved by
>someone who is no longer with the company. How can we
>convert this DTS package to a valid ID? Thanks
>.
>try sp_reassign_dtspackageowner
Olu Adedeji
"Peter Cwik" <anonymous@.discussions.microsoft.com> wrote in message
news:155bb01c3fa49$ecf965f0$a601280a@.phx
.gbl...
> Assuming you can open the package, simply click on
> package in the upper left and save as...

DTS Packages from 1 SQL Server to another

Hi,

I am tring to transfer a number of DTS Packages from one SQL Server to another. Does anyone have any ideas.

I can save them as either DTS Strucure or Visual Basic Files, but how can I load them again.

After that I am going to rename the servers, I am going to rename the old one with _BAK and I am going to name the new one with the current servers name. Might this cause problems??

Rayread the following article

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp|||Thanks Much, I will check that out and let you know.

Ray

Quote:

Originally Posted by aneeshattingal

read the following article

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp

dts package?

I have a DTS package schedule to run once each day and the package is save in Enterprise Manager. Does Enterprise Manager have to be loaded for the DTS package to run on schedule? Thanks.EM is just an application interface to sql server.

sql server stores the jobs and dts packages in the master database.

sql server agent takes care of the execution of all your jobs, and knows where to find your "stuff"

The is service the is constantly running

Anyone know how the internals of this works.|||To answer the original question, no SQL Enterprise Manager does not need to be running for a scheduled package to run. SQL Agent needs to be running on the server.

The DTS packages and the Job scheduling information are actually stored in the MSDB database, not in master.

While I don't know nearly as much as I'd like to, I know enough to get both of us into major trouble without even breaking a sweat!

-PatP|||msdb, huh...

Hell, failed that interview...

Had a dba here who wanted to "clean up" his server and get rid of un-needed databases...

Northwind
Pubs
model
msdb
master

scrub...

He didn't last very long...|||How do I get slq agent running? I keep getting that error that sqlagent is not running.

Thanks|||The easy way is to start the SQL Service Manager, select the correct server in the top drop down box, select SQL Server Agent in the lower box, then click the button with the green "start" triangle.

-PatP|||I have heard of some places that will drop pubs and Northwind. I never do. Too useful for testing stuff.

As for the DTS internals, I will take an ill-advised stab at explaining it all.

When you create a new DTS package, you are really creating a VB script which will do all the things that you told (not necessarily wanted) the DTS package to do. Technically, you could write a VB script in notepad to run a DTS package, but I already tried it. It stank.

The generated script is stored by default in the msdb database (sysdtspackages), but can be stored as a VB script, a proprietary DTS formatted file, or in the SQL Server metadata repository (have not touched that, myself).
A VB script version could be run on any windows machine, but will promptly choke, if it can not find the .dlls in order to get all the fancy functions it needs.

Actual package formatted DTS packages (from msdb, Metadata, or the proprietary file format) can all be run by the dtsrun.exe utility. This is what SQL Agent calls, when you schedule a package to run. It also happens to be the same executable that Enterprise Manager calls to run a package for you on your laptop. This is where the client dependencies start. If you have a SQL 2000 Enterprise Manager, you can run your package locally no problem. When you try to run the "same" package via SQL Agent on a SQL 7.0 server, you get nothing but errors. This is because you have asked the script to created with one library, and run with a separate library, and your package has only one library card (sorry, it is past 5:00).

So, now that you are no doubt utterly confused, the answer is "No, Enterprise Manager does not have to be running, but SQLAgent has to be running (in order to spawn the process), the dtsrun.exe utility has to be there (it is for any SQL Server install), and the dts*.dlls have to be there, and be the right version." Clear as mud?|||Thanks everyone for the help. I tried it and it's working just how I want it too. Thanks agian.

DTS Package to VB

One can save a DTS Package to a VB .bas file. Can you modify the VB
code and then reload the package and execute it? I can see an option to
save a package to vb but can't see an option to open a vb file as a
package.
RonI'm using .NET and could not get the VB program to compile using the
example in the DTS Help. I was getting a "Statement is not a valid
namespace" for the following statements:
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
I work with C# and Java and am not that familiar with VB.NET.
Any help appreciated.
Ron|||hi,
As far as I know it is not possible. As you said, oneself is be able to save
into .BAS file and then modify and save it again and even executing from VB
6.0 using dtspkg.dll. Why don't open it through sql server 2000/2005?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"RonL" wrote:

> I'm using .NET and could not get the VB program to compile using the
> example in the DTS Help. I was getting a "Statement is not a valid
> namespace" for the following statements:
> Public goPackageOld As New DTS.Package
> Public goPackage As DTS.Package2
> I work with C# and Java and am not that familiar with VB.NET.
> Any help appreciated.
> Ron
>

DTS Package Logging - can u limit it?

Hello. When you turn Logging on, for a DTS package, it
appears to save the history forever? If that's true, is
there an option to clear DTS package logs, after like 30
days? or would you need to write something to do this
purge manually? THanks, BruceYou can delete all the logs for a package from the Package Properties,
Logging tab.
You can also right click on local packages under the DTS node in
Enterprise Manager and select Logging. From here you have a bit more
control on selecting packages, selecting logs to delete, etc.
If you want to automate this somehow, you could code your own
maintenance of these using the DTS Object Model and schedule this
through a job. Take a look at the RemovePackageLogRecords method of
the PackageSQLServer object.
-Sue
On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:

>Hello. When you turn Logging on, for a DTS package, it
>appears to save the history forever? If that's true, is
>there an option to clear DTS package logs, after like 30
>days? or would you need to write something to do this
>purge manually? THanks, Bruce|||THanks Sue. Yes, I was looking for an option hopefully
built-in to SQL Server, to give me a set number of logs,
or a set number of days, like you can adjust the number of
SQL error log files. Yep, I'll check out the homegrown
approach... THanks, Bruce

>--Original Message--
>You can delete all the logs for a package from the
Package Properties,
>Logging tab.
>You can also right click on local packages under the DTS
node in
>Enterprise Manager and select Logging. From here you have
a bit more
>control on selecting packages, selecting logs to delete,
etc.
>If you want to automate this somehow, you could code your
own
>maintenance of these using the DTS Object Model and
schedule this
>through a job. Take a look at the
RemovePackageLogRecords method of
>the PackageSQLServer object.
>-Sue
>On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>
>.
>

DTS Package Logging - can u limit it?

Hello. When you turn Logging on, for a DTS package, it
appears to save the history forever? If that's true, is
there an option to clear DTS package logs, after like 30
days? or would you need to write something to do this
purge manually? THanks, Bruce
You can delete all the logs for a package from the Package Properties,
Logging tab.
You can also right click on local packages under the DTS node in
Enterprise Manager and select Logging. From here you have a bit more
control on selecting packages, selecting logs to delete, etc.
If you want to automate this somehow, you could code your own
maintenance of these using the DTS Object Model and schedule this
through a job. Take a look at the RemovePackageLogRecords method of
the PackageSQLServer object.
-Sue
On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:

>Hello. When you turn Logging on, for a DTS package, it
>appears to save the history forever? If that's true, is
>there an option to clear DTS package logs, after like 30
>days? or would you need to write something to do this
>purge manually? THanks, Bruce
|||THanks Sue. Yes, I was looking for an option hopefully
built-in to SQL Server, to give me a set number of logs,
or a set number of days, like you can adjust the number of
SQL error log files. Yep, I'll check out the homegrown
approach... THanks, Bruce

>--Original Message--
>You can delete all the logs for a package from the
Package Properties,
>Logging tab.
>You can also right click on local packages under the DTS
node in
>Enterprise Manager and select Logging. From here you have
a bit more
>control on selecting packages, selecting logs to delete,
etc.
>If you want to automate this somehow, you could code your
own
>maintenance of these using the DTS Object Model and
schedule this
>through a job. Take a look at the
RemovePackageLogRecords method of
>the PackageSQLServer object.
>-Sue
>On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>
>.
>

DTS Package Logging - can u limit it?

Hello. When you turn Logging on, for a DTS package, it
appears to save the history forever? If that's true, is
there an option to clear DTS package logs, after like 30
days? or would you need to write something to do this
purge manually? THanks, BruceYou can delete all the logs for a package from the Package Properties,
Logging tab.
You can also right click on local packages under the DTS node in
Enterprise Manager and select Logging. From here you have a bit more
control on selecting packages, selecting logs to delete, etc.
If you want to automate this somehow, you could code your own
maintenance of these using the DTS Object Model and schedule this
through a job. Take a look at the RemovePackageLogRecords method of
the PackageSQLServer object.
-Sue
On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:
>Hello. When you turn Logging on, for a DTS package, it
>appears to save the history forever? If that's true, is
>there an option to clear DTS package logs, after like 30
>days? or would you need to write something to do this
>purge manually? THanks, Bruce|||THanks Sue. Yes, I was looking for an option hopefully
built-in to SQL Server, to give me a set number of logs,
or a set number of days, like you can adjust the number of
SQL error log files. Yep, I'll check out the homegrown
approach... THanks, Bruce
>--Original Message--
>You can delete all the logs for a package from the
Package Properties,
>Logging tab.
>You can also right click on local packages under the DTS
node in
>Enterprise Manager and select Logging. From here you have
a bit more
>control on selecting packages, selecting logs to delete,
etc.
>If you want to automate this somehow, you could code your
own
>maintenance of these using the DTS Object Model and
schedule this
>through a job. Take a look at the
RemovePackageLogRecords method of
>the PackageSQLServer object.
>-Sue
>On Fri, 29 Oct 2004 10:23:48 -0700, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>>Hello. When you turn Logging on, for a DTS package, it
>>appears to save the history forever? If that's true, is
>>there an option to clear DTS package logs, after like 30
>>days? or would you need to write something to do this
>>purge manually? THanks, Bruce
>.
>

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 know you can save a DTS file off as a vb file. I've used that quite often
.
But is there any way to run a DTS package from T-SQL code?Btw, I've been playing around with dtsrun for the last half hour and can't
get it to work. I keep getting the following error msg whether I run it
under the domain service account, my own account or the sa password:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"CLM" wrote:

> I know you can save a DTS file off as a vb file. I've used that quite oft
en.
> But is there any way to run a DTS package from T-SQL code?|||Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsru
n
and it works.
--
TIA,
ChrisR
"CLM" wrote:
[vbcol=seagreen]
> Btw, I've been playing around with dtsrun for the last half hour and can't
> get it to work. I keep getting the following error msg whether I run it
> under the domain service account, my own account or the sa password:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
>
> "CLM" wrote:
>|||Thx. I just got it to work!
"ChrisR" wrote:
[vbcol=seagreen]
> Post your code and maybe we'll see the problem. I use xp_cmdshell with dts
run
> and it works.
> --
> TIA,
> ChrisR
>
> "CLM" wrote:
>

DTS Package

I know you can save a DTS file off as a vb file. I've used that quite often.
But is there any way to run a DTS package from T-SQL code?
Btw, I've been playing around with dtsrun for the last half hour and can't
get it to work. I keep getting the following error msg whether I run it
under the domain service account, my own account or the sa password:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"CLM" wrote:

> I know you can save a DTS file off as a vb file. I've used that quite often.
> But is there any way to run a DTS package from T-SQL code?
|||Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsrun
and it works.
TIA,
ChrisR
"CLM" wrote:
[vbcol=seagreen]
> Btw, I've been playing around with dtsrun for the last half hour and can't
> get it to work. I keep getting the following error msg whether I run it
> under the domain service account, my own account or the sa password:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
> "CLM" wrote:
|||Thx. I just got it to work!
"ChrisR" wrote:
[vbcol=seagreen]
> Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsrun
> and it works.
> --
> TIA,
> ChrisR
>
> "CLM" wrote:

DTS Package

I know you can save a DTS file off as a vb file. I've used that quite often.
But is there any way to run a DTS package from T-SQL code?Btw, I've been playing around with dtsrun for the last half hour and can't
get it to work. I keep getting the following error msg whether I run it
under the domain service account, my own account or the sa password:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"CLM" wrote:
> I know you can save a DTS file off as a vb file. I've used that quite often.
> But is there any way to run a DTS package from T-SQL code?|||Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsrun
and it works.
--
TIA,
ChrisR
"CLM" wrote:
> Btw, I've been playing around with dtsrun for the last half hour and can't
> get it to work. I keep getting the following error msg whether I run it
> under the domain service account, my own account or the sa password:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
> "CLM" wrote:
> > I know you can save a DTS file off as a vb file. I've used that quite often.
> > But is there any way to run a DTS package from T-SQL code?|||Thx. I just got it to work!
"ChrisR" wrote:
> Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsrun
> and it works.
> --
> TIA,
> ChrisR
>
> "CLM" wrote:
> > Btw, I've been playing around with dtsrun for the last half hour and can't
> > get it to work. I keep getting the following error msg whether I run it
> > under the domain service account, my own account or the sa password:
> >
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> >
> >
> > "CLM" wrote:
> >
> > > I know you can save a DTS file off as a vb file. I've used that quite often.
> > > But is there any way to run a DTS package from T-SQL code?

DTS job error

Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas ?
Thanks a lot
Mike
I would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas ?
>Thanks a lot
>Mike
|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nmakes them easier to handle.
|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... ?? This is strange. I've
got errors for all the users i used.
Any idea ?

>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
2
regarding
job
>.
>
|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot

>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... ?? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>

DTS job error

Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas '
Thanks a lot
MikeI would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas '
>Thanks a lot
>Mike|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can
change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nma
kes them easier to handle.|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... '? This is strange. I've
got errors for all the users i used.
Any idea ?

>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
>
2
regarding
job
>.
>|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot

>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... '? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>

DTS is crazy! or maybe its me?

Hey all,
I need to import an excel file into a SQL table. Users are able to modify and save the excel file. I have a stored procedure that calls a structured storage file which does the import. Both the excel file and the structured storage file reside in the same location. The first time that the stored procedure fires the DTS it works fine, but if the data in the excel file is overwirtten by a user it appears that the import does not function.
Am I missing something here?
Here is the SPROC:
CREATE PROCEDURE [dbo].[importEmployees] AS

DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, 'C:\importEmployees.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

GO

Ok, so it was me... I had the datatype wrong on the excel file which was set to numeric and I put in text.