IMEX=1 in OpenDataSource

I'm having an issue with selecting data from an Excel 2002 spreadsheet using
OpenDataSource (or DTS for that matter). Excel will sometimes identify the
column type as numeric and ignore all character data in the column,
returning nulls instead of the data (known issue -
http://support.microsoft.com/?id=194124). I've editted my registry to force
the Jet drive to always use text as the data types for columns; however, the
registry is ignored unless the "IMEX=1" switch appears in the connection
string. All the examples of using the switch that I've found have been for
using it with the command object in VBScript. Can this switch be used with
the OpenDataSource TSQL command? If so, what is the syntax and do I need to
have Office installed on the sql server?
Thanks!!!
WalterHi
It can be used according to http://www.connectionstrings.com/ e.g.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
There is also a MAXSCANROWS extended property
http://support.microsoft.com/defaul...kb;en-us;278973
John
"Walt Mallon" wrote:

> I'm having an issue with selecting data from an Excel 2002 spreadsheet usi
ng
> OpenDataSource (or DTS for that matter). Excel will sometimes identify th
e
> column type as numeric and ignore all character data in the column,
> returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to for
ce
> the Jet drive to always use text as the data types for columns; however, t
he
> registry is ignored unless the "IMEX=1" switch appears in the connection
> string. All the examples of using the switch that I've found have been fo
r
> using it with the command object in VBScript. Can this switch be used wit
h
> the OpenDataSource TSQL command? If so, what is the syntax and do I need
to
> have Office installed on the sql server?
> Thanks!!!
> Walter
>
>|||Got it!!! Had some quotes in the wrong place:
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\CEXP-Mixed-Analyze.xls;Extended properties="Excel
8.0;HDR=YES;IMEX=1"')...[data$]
That one worked!!!
Thanks.
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:e3M07$AyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> I'm having an issue with selecting data from an Excel 2002 spreadsheet
> using OpenDataSource (or DTS for that matter). Excel will sometimes
> identify the column type as numeric and ignore all character data in the
> column, returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to
> force the Jet drive to always use text as the data types for columns;
> however, the registry is ignored unless the "IMEX=1" switch appears in the
> connection string. All the examples of using the switch that I've found
> have been for using it with the command object in VBScript. Can this
> switch be used with the OpenDataSource TSQL command? If so, what is the
> syntax and do I need to have Office installed on the sql server?
> Thanks!!!
> Walter
>

i''m sorry man no was my intention

i′m sorry my bad english

i have a dts in sql server 2005

for example in sql server 2000 i can do that :

over DTS right-clik choose option "Scheduled pachage" and this create a scheduled what i mean is that this produces a "JOB"

what i mean is that i need to know how can to create a scheduled to DTS′s in sql 2005

Please anyone of you provide some solution for this.

Okay, I completely misunderstood what you wanted before. But, I think I can help you now.

Once you have the SSIS package (DTS packages mean something else - like packages from SQL SERVER 2000 DTS) you can use SQL Server Agent to shedule the package to be run at a certain time.

Use the SQL Server Agent to schedule your package.

|||

sorry man:

what i do in sql server 2005 is:

right-click over SQL Server Agent , choose the option "New" and choose option "Schedule" but isn't what i want, may be i am not specfic

when i do right-click over dts in sql server 2000 and choose the option schedule package this produces an scheduled and i can see it in the part of the jobs

Now how i can do that in sql server 2005? i need to see of my DTSs the jobs that i created and you said me that i can do by SQL Server Agent in SQL server 2005 and this isn′t do what i want.

Please anyone of you provide some solution for this.
Thanks :)

|||

limon26,

I merged this post into your earlier thread. If you want to reply to another reply you should use the reply button and not the new thread button. This will keep the discussion in one thread and others that can help will be able to follow the discussion more easy.

Thanks.

|||

i apologies for that

so what can i do about my DTSs in sql server 2005 ?

|||

I am not that experienced with SSIS for 2005, hopefully swells or somebody else will be able to help you further...

|||

In SQL Server 2000, you could create a schedule by right-clicking on a package in Enteprise Manager and selecting "Schedule Package." SQL Server 2000 created the SQL Server Agent job. You could find the jobs in Enterprise Manager under the Management folder, in the jobs tree.

In SQL Server 2005 there are some changes ...

1. SQL Server Agent is not automatically enabled. Make sure the service has been started.

2. You do not automatically create scheduled jobs for SSIS. You should right click on the SQL Server Agent icon in the SQL Server Object Explorer in the Management Studio and select to create a new Agent Job. When you do that, you can add a new job step to execute an SSIS package. You can also add a new schedule.

The end result is similar to 2000 - jobs are listed under the SQL Server Agent node; jobs have steps - one step executes a package; and jobs have schedules.

An important difference is that SSIS now has an Agent subsytem for more better integrated execution.

hth

Donald

|||

After create your ssis package. Here is step you have to do to make a job run....

My ssis is running every 10 min.....

Here is what I do to make it work.......

Create the proxy account, And SQL Server Agent running under your NT Acount and then the job Steps running under the "SQL Agent Service Account" and your nt account as the owner of the job. It will works perfect.

I don't know if you know how to create proxy account. Here is how to create:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

kenny

|||Similar problems, but (so far) the credentials/proxy doesn't seem to resolve it.
We have two people who have developed some SSIS packages, myself and another developer. The other developer's SSIS package runs fine from BIDS, DTexec and DTexecUI, but not from SQL Server agent. My SSIS package runs fine from BIDS, DTexec, DTexecUI and local SQLServer Agent. I haven't had the opportunity to have it deplyed into another environment to check if I have problems from the server.
What was interesting to me, in attempting to test the credentials, was that the working SQL Server Agent job, it I edit it to use the proxy, fails. If I edit the job back to using the SQL Agent Service Account (on the RUN AS tab on the task), then it works successfully.
Now this may be because I've set up the credential incorrectly, since it was my first time (be gentle :-)). I'm going to try and modify the script above and see if that works any better. The credential I set up used a local user I created that was part of the powerusers group. The package does file operations locally (based on the current configuration file). The 'ProtectLevel' was set to 'EncryptSenstitiveWithPassword', and this specific test job is using SQL Server as the package source.
Nay help? The KB http://support.microsoft.com/kb/918760 states that this is ' This behavior is by design.' - but surely it should also be easy to use?
|||using the scipr (after removing the duplicate, and adding exec's), I generated a proxy and a credential. The user being used is the same one I create earlier (which is a member of the power user group on my laptop). I get the following error:
-
Date 25/07/2006 14:21:59
Log Job History (Test Excel Orchestration)
Step ID 1
Server MyLaptop\SQL2005_SP1_TEST
Job Name Test Excel Orchestration
Step Name Invoke Excel Orchestration
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: MyLaptop\SSISAgentJobCredUser. The package could not be loaded. The step failed.
-
this proves the credential at least worked - it did try to run with the correct user, but still no joy.
.... even less joy now, since I now get the following, trying to use this in the way that worked before:

Date 25/07/2006 14:27:45
Log Job History (Test Excel Orchestration)
Step ID 1
Server MyLaptop\RGALBRAITH\SQL2005_SP1_TEST
Job Name Test Excel Orchestration
Step Name Invoke Excel Orchestration
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.

|||

Hey,

Today I got the same error as you, to fixed I didn't create the credentials, I changed the owner for the Scheduled Jobs instead, and it's works great!

|||

I fixed this by adding the owner of the package to the owned schema's in the agent roles in msdb.

take a look at this link:

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

i'm sorry man no was my intention

i′m sorry my bad english

i have a dts in sql server 2005

for example in sql server 2000 i can do that :

over DTS right-clik choose option "Scheduled pachage" and this create a scheduled what i mean is that this produces a "JOB"

what i mean is that i need to know how can to create a scheduled to DTS′s in sql 2005

Please anyone of you provide some solution for this.

Okay, I completely misunderstood what you wanted before. But, I think I can help you now.

Once you have the SSIS package (DTS packages mean something else - like packages from SQL SERVER 2000 DTS) you can use SQL Server Agent to shedule the package to be run at a certain time.

Use the SQL Server Agent to schedule your package.

|||

sorry man:

what i do in sql server 2005 is:

right-click over SQL Server Agent , choose the option "New" and choose option "Schedule" but isn't what i want, may be i am not specfic

when i do right-click over dts in sql server 2000 and choose the option schedule package this produces an scheduled and i can see it in the part of the jobs

Now how i can do that in sql server 2005? i need to see of my DTSs the jobs that i created and you said me that i can do by SQL Server Agent in SQL server 2005 and this isn′t do what i want.

Please anyone of you provide some solution for this.
Thanks :)

|||

limon26,

I merged this post into your earlier thread. If you want to reply to another reply you should use the reply button and not the new thread button. This will keep the discussion in one thread and others that can help will be able to follow the discussion more easy.

Thanks.

|||

i apologies for that

so what can i do about my DTSs in sql server 2005 ?

|||

I am not that experienced with SSIS for 2005, hopefully swells or somebody else will be able to help you further...

|||

In SQL Server 2000, you could create a schedule by right-clicking on a package in Enteprise Manager and selecting "Schedule Package." SQL Server 2000 created the SQL Server Agent job. You could find the jobs in Enterprise Manager under the Management folder, in the jobs tree.

In SQL Server 2005 there are some changes ...

1. SQL Server Agent is not automatically enabled. Make sure the service has been started.

2. You do not automatically create scheduled jobs for SSIS. You should right click on the SQL Server Agent icon in the SQL Server Object Explorer in the Management Studio and select to create a new Agent Job. When you do that, you can add a new job step to execute an SSIS package. You can also add a new schedule.

The end result is similar to 2000 - jobs are listed under the SQL Server Agent node; jobs have steps - one step executes a package; and jobs have schedules.

An important difference is that SSIS now has an Agent subsytem for more better integrated execution.

hth

Donald

|||

After create your ssis package. Here is step you have to do to make a job run....

My ssis is running every 10 min.....

Here is what I do to make it work.......

Create the proxy account, And SQL Server Agent running under your NT Acount and then the job Steps running under the "SQL Agent Service Account" and your nt account as the owner of the job. It will works perfect.

I don't know if you know how to create proxy account. Here is how to create:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

kenny

|||Similar problems, but (so far) the credentials/proxy doesn't seem to resolve it.

We have two people who have developed some SSIS packages, myself and

another developer. The other developer's SSIS package runs fine from

BIDS, DTexec and DTexecUI, but not from SQL Server agent. My SSIS

package runs fine from BIDS, DTexec, DTexecUI and local SQLServer

Agent. I haven't had the opportunity to have it deplyed into another

environment to check if I have problems from the server.

What was interesting to me, in attempting to test the credentials, was

that the working SQL Server Agent job, it I edit it to use the proxy,

fails. If I edit the job back to using the SQL Agent Service Account

(on the RUN AS tab on the task), then it works successfully.

Now this may be because I've set up the credential incorrectly, since

it was my first time (be gentle :-)). I'm going to try and modify the

script above and see if that works any better. The credential I set up

used a local user I created that was part of the powerusers group. The

package does file operations locally (based on the current

configuration file). The 'ProtectLevel' was set to

'EncryptSenstitiveWithPassword', and this specific test job is using

SQL Server as the package source.

Nay help? The KB http://support.microsoft.com/kb/918760 states that

this is ' This behavior is by design.' - but surely it should also be

easy to use?|||using the scipr (after removing the duplicate, and adding exec's), I

generated a proxy and a credential. The user being used is the same one

I create earlier (which is a member of the power user group on my

laptop). I get the following error:

-

Date 25/07/2006 14:21:59

Log Job History (Test Excel Orchestration)

Step ID 1

Server MyLaptop\SQL2005_SP1_TEST

Job Name Test Excel Orchestration

Step Name Invoke Excel Orchestration

Duration 00:00:01

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: MyLaptop\SSISAgentJobCredUser. The package could not be loaded. The step failed.

-

this proves the credential at least worked - it did try to run with the correct user, but still no joy.

.... even less joy now, since I now get the following, trying to use this in the way that worked before:
Date 25/07/2006 14:27:45

Log Job History (Test Excel Orchestration)

Step ID 1

Server MyLaptop\RGALBRAITH\SQL2005_SP1_TEST

Job Name Test Excel Orchestration

Step Name Invoke Excel Orchestration

Duration 00:00:00

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.|||

Hey,

Today I got the same error as you, to fixed I didn't create the credentials, I changed the owner for the Scheduled Jobs instead, and it's works great!

|||

I fixed this by adding the owner of the package to the owned schema's in the agent roles in msdb.

take a look at this link:

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

IIS Logs Max Per Hour

Hi
Being very new to SQL so iam hoping that this will be
easy.
Using a dts package to transfer IIS logs into a SQL
database i would now like to knpw of there is any
resource that gives some idea on the best stats to take
from IIS Logs.
i have create a query that is a count of [c-ip] i would
like to find out what is the max hourly count of [c-ip]
grouped by day the data is very mixed as when log a is
recorded could be 3 a second or not one for a few
seconds ? but generally data is at per second level?

Quote:

Originally posted by Simon Jelinek
Hi
Being very new to SQL so iam hoping that this will be
easy.
Using a dts package to transfer IIS logs into a SQL
database i would now like to knpw of there is any
resource that gives some idea on the best stats to take
from IIS Logs.
i have create a query that is a count of [c-ip] i would
like to find out what is the max hourly count of [c-ip]
grouped by day the data is very mixed as when log a is
recorded could be 3 a second or not one for a few
seconds ? but generally data is at per second level?

Try asking your manager, I hear he is very good at this sort of thing

Ignoring Source Field

Hi,

I am implementing a Transactional Replication btwn two SQL 2000 servers, which is using Data Transform services (DTS package) to manipulate the data. I need to ignore a source column from replication but that column field value needs to be appended with other field column.

For ex. Let two source fields be named FirstName and LastName. In the target database i need to merge those field values into a Single field called CustomerName. Also i don't want the two source fields to be replicated in the target.

I tried to ignore the field by unchecking the Column Transformation and Mapping screen, but then unable to append the field value to the other field.

Is there any other way to do the above?

Thanks in advance.

urs,
T. Jayakumar

I don't think there is a way to use a field in a transformation but not include it in the package data.

Phil Garding