Bulk Load problem - error connecting

I get the following error when trying to use the SQL Bulk Load object.

"Error connecting to the data source." The datasource is used right above this code to successfully read from the database. It is the last line that blows up.

Here is the problem code:

connStr = "provider=SQLNCLI;Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"

Dim objBL As New SQLXMLBULKLOADLib.SQLXMLBulkLoad

objBL.ConnectionString = connStr

objBL.BulkLoad = True

objBL.XMLFragment = True

objBL.KeepIdentity = False

objBL.ErrorLogFile = "C:\BulkLoadErrors.xml"

objBL.Execute(SchemaFile, datafile)

(SchemaFile and datafile are strings containing the full file name and path)

Use the connection string as follows :

ConnStr = "provider=sqloledb;data source=myserver;database=mydb;integrated security=SSPI;"

It should work.

Thanks.

Naras.

|||Thank you! That did it. Annoying part is the connection string I was using was taken straight from the MSDN examples.

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Building attributes based on calculated values

I have a measure: "# of clicks". I have a calculated measure, which is "# of clicks in last 30 days". What I'd like to do is segment my users using this "# of clicks in the last 30 days" calculated measure so that I have three different segments: "No usage in last 30 days", "Some usage in last 30 days" and "Tons of usage in last 30 days".

Here's my question...what's the best way to create an attribute in my user dimension for these usage segments? The only thing I've been able to come up with is to pre-calculate the "# of clicks in last 30 days" when I build the warehouse and then put in the "case WHEN [# of clicks]" segmentation in the dsv. I don't like this method because it will like slow down the warehouse build dramatically.

It doesn't seem like there's a way to build that logic into cube or dimension. What am I missing? I thought about trying to define "sets", but if I do it that way I can't use the segment in reports as attributes to show.

Anyone have any advice? Thanks for any help can provide,

P

You could create calculated members in the calculation script of your cube to do this sort of thing. Although it will not slow down your build times, depending on your data, the performance could be slow at runtime.

I have assumed that getting the "last 30 days" is as simple as grabbing the last 30 nonempty days, in practice it is often not this simple, other approaches would include using date functions to determine the current system date and then lagging back 30 members, or some people even build their time dimensions as they are needed, so the last member in the dimension is the last day.

CREATE MEMBER CurrentCube.User.[tons of clicks] as AGGREGATE(
FILTER(Users.UserName.members
, SUM(TAIL(NONEMPTY([Date].[Days].Members),30)
Measures.[# of clicks]) > 100
)
)

You could also create a similar expression using a custom rollup formula in your dimension table.