Problem with Bulk Insert Task and Oracle Database

I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.

I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.

When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).

Does anyone have solution for this problem?

Saurabh Kulkarni wrote:

I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.

I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.

When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).

Does anyone have solution for this problem?

I don't know for sure but I can make an educated guess.

The Bulk Insert task leverages SQL Server's bulk insert functionality. Hence, it is to be used with SQL Server only. The first sentance in the BOL topic about Bulk Insert Task says "The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view"

i.e. Not Oracle.

Did you read the documentation?

-Jamie

|||

Jamie is right; here is the link to the BOL that talks about Bulk insert task:

http://msdn2.microsoft.com/en-us/library/ms141239.aspx

To solve your problem you can use OLE DB Destination instead and choose fast load option...

|||Rafael,

As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?

Thanks.|||

Kaarthik Sivashanmugam wrote:

Rafael,
As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?
Thanks.

I was not confused; I provided the link to support Jamie statement. I have edited my previous post to clarify it.

|||

sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.

Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.

Regards,

Saurabh

|||

OLE DB destination has several moving parts that will allow you to tune it for large amount of rows. First at all; you should use fast load as the data access mode; at least against SQL server that works as bulk inserts. Then you have 'rows per batch' and 'Maximum Insert commit size' parameter that would help to control how often the transactions will commit. I have seen at least 1 thread in this forum discussing how to use those options. Also and if third party providers are an option for you; I think there is at least 1 vendor offering a high performance connector for Oracle DBs (http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm). I recommend you to run some tests and see by yourself.

|||

Loading data into Oracle could be done by using the "execute process task" and then use sqlldr as command. You have to install the oracle client - which contains the sqlldr - on the maschine, where your package will run. Using the process task gives you all the flexibility for dealing with paramters, config-files and so on, which makes sqlldr fast.

I realized a similar scenario for transfering Sybase data into a MS SQL Server by using standard utilities from Sybase to get data out (or in, whatever the direction is), dependent on a configration table (so fully dynamic).

Norbert

|||

Saurabh Kulkarni wrote:

sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.

Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.

Regards,

Saurabh

The OLE DB Destination is perfectly fine for loadif to Oracle as long as you use it the right way. Read these links:

Persistent Oracle Connector
(http://blogs.conchango.com/jamiethomson/archive/2006/04/07/3575.aspx)

Loading to Oracle
(http://blogs.conchango.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-Loading-to-Oracle.aspx)

-Jamie

Problem with a proxy

Hi

i have a problem with a proxy and i don't know how to resolve this

So, i want to run a SSIS package so i follow this http://support.microsoft.com/kb/918760

However, the package fails and i have a error message which tell me that i can't get proxy's data for proxy_id = 23!

If someone has the solution please help me!!

A little more information would help.

How have you set up the proxy and credentials?

K

sql

problem when selecting dynamic connection setting

Hello All,

I have a SSIS package, desgined in such a way that, there is a table called connection which hold the connection name i.e similar to my DSN connection name & a ID for each DSN there is a unique ID. Now i want if there is a 10 record in the table & i have only 5 DSN connection then i'll work for 5 & for other 5 it 'll skip the process. my task is running success fully for 1 connection. i have used foeach loop container & 3 varibable for that 2 for holding the table value & 1 for refereing the recordset

thanks

So are you saying that the table holds 10 records but only 5 of them are valid for the particular package that you are currently executing?

Then you need to filter the list of connections that you want to "use". Can you not hardcode that filter per package?

Sorry, I don't really understand the requirement.

-Jamie

|||

take a example

i have a table table1(id,DsnConnectionName,.) table has 20 records. that means there is a information for 20 DSN connection. But my machine has only 5 DSN Connection. That is in that table. i have first i'm collecting these record in a record set then connect that data flow task with foreach loop. i have 2 variable 1 hold the ID & another DSNConnectionName.

in that foreach loop container i have placed a script task which is setting the connection according to the DSN name assign in that variable. but when the dsn connection with that name is not available in my system then i gives error. for that i use try catch but due to this it;'s running & inserting data each time.

|||

Why can you not just create all the required DSNs?

Why would you try andrun something against a DSN that does not exist?

If you're not going to create the DSNs then, like I said before, you need some way of filtering the list of connections that you iterate over.

-Jamie

|||

Thanks for your keen interest in my problem.

I got the solution,

solution is very simple i used try catch mathod & set dts.taskresult = dts.results.failure for error condition

Problem When From Sequence file insert into Oracle Destination table in SSIS Package

Hi ,

i was used the Follwing DataFlow for my Package.using Oracle 8i

FalteFile Source -> Data Conversion >OLEDB Destination (Oracle Data table)

using above control flow to map the Source file to Destination . When i run the SSIS Package teh Folwing Error i got

"Truncation Occur maydue to inserting data from data flow column "columnName " with a length of 50 "

regarding this Error i i understood its for happening Data Length . so that i was changed the Source Column Length Exactly Match with the The Destination table.

still i am getting this Error. pls any one give me a solution . SHould i Change the DataType also?

pls give your suggestion

Thanks & Regards

Jeyakumar.M

chennai

At some point of your flow it is still defined as greater than 50. Check all outputs of all components and check external columns of your source as well.

-Jamie

|||

Hi Jamie

Thanks for your reply. all the Source column (Sequence file) and Destination Column (oracle Table ) length are same. But i didn't give Data type match for source and Destination .. Is it Required for Data Type to define in the Source column in Dataconversion control. pls Let me know.

Thanks For Your reply

Jeyakumar.M

chennai

|||

Data types in teh source are by and large determined by the OLE DB Provider. If you need them to be something else then drop a Data Conversion component into your flow.

-Jamie

Problem when Connection is not available

Hello!

I have problem when opening a package or running it through SQL Server Agent and the Server in the Connection is not available. (I have tried with (M) SSIS > Work Offline, it helps opening it, but not running it).

The package has a Foreach Loop Container that loop through several Companies. Each Company have different Connection.

It exist one ODBC and one OLEDB connection. They have the Expression “ConnectionString” set to an variable (@.[User::varODBCConnectionString]/ @.[User::varOLEDBConnectionString]).

The Foreach Loop Container property ForceExectionResult is set to Completion. This results in that if one company is not available when trying to access it, the loop will continue with the next Company.

But the variables have a value from the beginning. If the server in the variables is not available, it is not possible to open the package or execute it through SQL Server Agent.

I tried to set the Property DelayValidation to True on the Connections, but this did not help.

Dose anyone have any ide how get around this problem?

Best regards,

Tina

Hi Tina,

You need to set DelayValidation=TRUE on the tasks that use those connection managers, not on the connection managers themselves.

Regards

Jamie

|||

Hello Jamie!

Thank you very much, it works now, bouth SQL Server Agent and in SSIS. (I also need to set Delay Validation = TRUE for att least the ODBC connection.).

Best regards,

Tina

Problem Warming Cache (Fails to Run MDX Statement)

Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.

This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:

{SQL Server Analysis Services 9.0 build 3042 (SP2)}

Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

SELECT NON EMPTY

{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],

[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],

[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],

[Measures].[Volume - Actuals] } ON COLUMNS,

NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *

[Location].[Location Name].[Location Name].ALLMEMBERS *

[Profile].[Profile].[Profile].ALLMEMBERS *

[Location].[Location ID].[Location ID].ALLMEMBERS ) } ON ROWS

FROM [Closure Flash Current]

I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!

I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):

When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.

After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.

I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.

Problem Warming Cache (Fails to Run MDX Statement)

Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.

This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:

{SQL Server Analysis Services 9.0 build 3042 (SP2)}

Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

SELECTNONEMPTY

{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],

[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],

[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],

[Measures].[Volume - Actuals] }ONCOLUMNS,

NONEMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *

[Location].[Location Name].[Location Name].ALLMEMBERS *

[Profile].[Profile].[Profile].ALLMEMBERS *

[Location].[Location ID].[Location ID].ALLMEMBERS ) }ONROWS

FROM [Closure Flash Current]

I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!

I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):

When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.

After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.

I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.

Problem w/ Data Flow Task of SSIS Package

I have a Data Flow Task in an SSIS package that transfers data from a local table to a table on a remote server. The remote database is SQL Server 2000 and the local one is SQL Server 2005. The package in the designer runs great. But, when I setup the job for the server agent it fails with the connection. I get the following error:

The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.

This is on validation from what the error logs say. It is probably just a simple configuration problem or something I have overlooked.

=============================================================

I also ran a seperate import task on the remote server (the same one as above) database table (Tasks > Import Data) and saved it as an SSIS package as the last option it gives you in the wizard. I am copying from a local table to a remote table. The import was successful as expected. But, when I run that same SSIS package in a SQL Server Agent Job, that package fails. I have not made any changes to the package what so ever. I never even opened the package to look it in the editor. I just ran it as is.

I am thinking it is a security problem of some sort. Like the Agent does not have right privilidges. I am running the packages under my local system account.

Thanks in advance!

This does sound like a security problem. If you're executing from SQL SAerver Agent then the packages will run as the startup acocunt for SQL Server Agent. Does this account have permissions on the source database?

There is a way that you can see what connection string is being used at runtime as I have explained here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

This will cause the connection string to be output to your log provider (assuming you are using one and if you aren't - you should be).

-Jamie

|||

Thanks for your response.

I am running the local database from a Windows XP development machine. This is where the SSIS package resides because the hosting provider I am using for the remote database does not support SSIS. So I have to run the Server Agent locally and then copy the data over as the last step of the package.

So to answer your question, NO, my local account that is running Server Agent is not on the remote server. I am using the same username/password on the remote machine as I am for the ASP.NET pages on the application on that remote machine. The remote account works. I am using windows security locally on my development for the database server login.

As I mentioned earlier, I can run the SSIS package in the designer without a problem but it does not work in the Server Agent.

I do have a log provider setup to output to a table on the local database. Below is what the log output is (I replaced the actual server name for security reasons to "server.name.here"):

__

988	OnInformation	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	1074016266	<Binary data>	Validation phase is beginning.
989	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	0	<Binary data>	Validating
990	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	50	<Binary data>	Validating
991	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1071611876	<Binary data>	The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.
992	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073450985	<Binary data>	component "Destination - Listings" (187) failed validation and returned error code 0xC020801C.
993	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	100	<Binary data>	Validating
994	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073450996	<Binary data>	One or more component failed validation.
995	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073594105	<Binary data>	There were errors during task validation.
996	OnPostValidate	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	0	<Binary data>

|||

After my last post I changed the timeout on the remote database connection to 60 seconds as you suggested in your blog. I would have never thought that it would have been that simple but it fixed the problem. I thought that the timeouts set at 0 seconds meant there WAS NO TIMEOUT. BAH!!

Thanks for your help! I was about ready to pull my hair out.

Problem variable initialization

Guys I noticed that the value I set on one of the package variables is not carried over during the execution. I have a package variable of type String which I use to hold the string for a parameter in a stored procedure. My stored procedure has one output parameter which is the id of the record fetched using the string parameter. These are my parameters
1) User::vendorId Int32 Initial value = -1
2) User::vendorName String Initial value = Vendor A

I make the assignment in the Variable pane. Are there any other ways of doing this?

Watching the two variables reveals that only the value for the first variable is carried over during execution. The vendorname is always blank --> {}. I'm using a SQL Task with OLEDBConnection.

What's going on here?

Carried over during execution? What do you mean?

Is this a real problem or an issue with the diagnosis method. When watching variables, you must be on a breakpoint, and you must examine them in the watch window. Looking at the variables pane or looking when not on a breakpoint may not give you the correct value, as only the watch window at a breakpoint is updated during execution. Is a bit fussy, as you can see the value in several other windows, but they just do not get updated.

|||When you create a variable and assign it a default, that value never changes. During execution it can change, but when the package is done all variables revert back to the defaults you entered when you created the package.|||"revert back to the defaults" - think of it this way, when you execute a package the execution host loads it, then executes it and then does not save it. The execution host and the designer are two different things, albeit with some communication for the debug features. This is a change to the DTS behaviour if that is in your background.|||Guys thanks so much for th replies. I'm still in the development phase obviously and I just want the flexibility of setting values to the package variables. For this, I use the Variable pane of Visual Studio where the last column - I think - is for the default value to be used on every run. I tried to use the Script task but I couldnt find my package variables. My vendorId which is initialized to -1 retains its value during the execution; at least that's what the Watch window is showing me. The other variable User::vendorName is always blank even if I set it to a certain string value prior to F5. My control flow doesnt have any event handler. Should you need more clarification guys, I'll do everything to make this clear.
|||Just to clarify, if you change the Value property for the variable in the Variables window, save, then close and reopen the package, does it have the value you set it to? If it doesn't, make sure that you don't have a configuration set that is overwriting the variable. If the value is correct, run the package and check the value in the watch window. If it is not correct there, either an expression or a script is changing the value at runtime.|||Thanks so much. Now I remember I've tinkered about package configuration. It didn't occur to me that explains it. It's nice to see you can toggle it on/off.

I created another configuration but I can't find it in the solution explorer or in the bin table? Is the configuration available only after deployment?
|||An XML configuration will be created at whatever path you specify when you create the configuration. If you open the package configurations dialog, you should see the path to the file.

Problem using DTSRUN in stored procedure / query analyzer

Hi. I am having a problem using DTSRun in a stored procedure or in query analyzer.
My DTS package truncates a table and then imports data from an Excel spreadsheet into the table.
The DTS package runs fine if I run it manually on Enterprise Manager or if I use DTSRun in a command prompt.
However when I try to run the package in a stored procedure or in query analyzer as follows:
ie Exec master..xp_cmdshell 'DTSRUN /SSQL SERVER /NPackageName /Uuserid /Ppassword'

The package does not return an error but it does not actually import any data from the Excel spreadsheet. It is able to truncate the table without any problem.
I've tried all kinds of combination of using Windows user id that I know has access to the excel file instead of a sql login.
I've run Filemon on the excel file and it seems like DTS is not accessing the file when I use the stored proc or query analyzer method.

If anyone could shed any light on this I would be most grateful. Thanks.The DTS package runs fine if I run it manually on Enterprise Manager or if I use DTSRun in a command prompt.
However when I try to run the package in a stored procedure or in query analyzer as follows:
ie Exec master..xp_cmdshell 'DTSRUN /SSQL SERVER /NPackageName /Uuserid /Ppassword'


When you run the package manually, are you running it from your machine (do you have EM running on your machine and connected to the SQL Server)or are you logged on to the server directly?

DTS looks for the file in the path of the machine it is running on ... so if the spreadsheet exists on your machine but not the server it will execute with success when run from your machine, but not by the SQL Agent on the server.|||Setup DTS logging and find out what is exaclty going on..|||When you run the package manually, are you running it from your machine (do you have EM running on your machine and connected to the SQL Server)or are you logged on to the server directly?

DTS looks for the file in the path of the machine it is running on ... so if the spreadsheet exists on your machine but not the server it will execute with success when run from your machine, but not by the SQL Agent on the server.

Hi. I have successfully run the package on Enterprise Manager on my machine and using DTSRun on a command prompt on my machine. The DTS package is pointing to the excel file with a full UNC pathname.

I've enabled DTS package logging and it does not return an error. The error code for the DataPump task is 0 even though it did not import any rows.|||Next place to check ... does the SQL Agent service login have permissions to the file share where the spreadsheet resides?

Problem uploading data into SQL Server with SSIS

Hi

I designed a package in SSIS that loads data from an Excel source into a oledb SQL database table.

The problem I have is that two of the columns that are supposed to be nvarchar type columns sometimes contain numerical strings. These values causes an error when 'Allow Null' is not set and loads as NULL if 'Allow Null' is set.

How can I force these values into the table?

I have a script transformation object in the package with this code:

If IsNumeric(Row.OCCode) Then

Row.ItemCode = CStr(Row.OCCode)

Else

Row.ItemCode = Row.OCCode

End If

ItemCode is of type unicode string and the column in the SQL DB Table it's mapped to is of type nvarchar

What is the source data type set to for those columns in the source connection?|||

I don't know how to check that, but the input columns into the transformation from the source are unicode strings.

I Also tried Forcing the Excel columns to Text.

|||In the source, the columns are set to Unicode string [DT_WSTR]|||Yeah, I don't understand what NULLs have to do with numeric data appearing in a string field. Can you shed more light on this?|||

It seems impossible to upload Text and numeric values into one Text column in the SQL database. The excel source picks up the source data as unicode string if its alphanumeric values and Numeric type when it's numeric value. if I make all the source columns in the source Unicode String it stops the running package as soon as it comes accross a numeric value with this Error Message:

There was an error with input column "ItemDescription" (889) on input "OLE DB Destination Input" (248). The column status returned was: "The value violated the integrity constraints for the column

If I make the column accept NULL values in the database, the package completes the data load, but make all the numeric values NULL.

I can enter data manually into this database to take numeric values in the nvarchar field, but I don't know how to set the SSIS package to upload the numeric values as Text.

|||Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1277733&SiteID=1|||

I managed to upload the data, thanks. The link you added mentioned that numerical strings in the excel file need to be re-entered after the column format is changed to Text. Seeing that I had 54000 rows, I had to perform a text-function operation on all the relative cells. Something like =trim(A2) and then copy these cells back to the original columns as values.

Thanks

|||The important thing in that link was the setting of the IMEX parameter.|||

Yeah I saw the code, but don't know where to add it or how to change the IMEX property. I had to get the data in.

It will help a lot if you can explain where to change this property. I can change the registry entries.