OLAP Connection error

Below code at web.config file, when i change Data Source to other pc name....an error occur 'The data provider didn't supply any further error information'...
let said change the datasource 'localhost' to 'webserver' error will occur...so i cannot run the program at my pc and link the olap database at server...

<add key="OLAPConnectionString" value="Provider=MSOLAP.2;Data Source=localhost;Initial Catalog=Foodmart 2000;" />The other PC that you are trying to connect should have the same users and user rights as the first one.

In addition to that, the Analysis Server on your second PC should know about these users as well.

I found that security with Analysis Server is very tight.

Good Luck

OLAP / Analysis services version checking by client

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

Hello,

I'm going to move your question to one of the SQL Server forums were they should be able to help you.

Daniel Roth

|||

Erkki wrote:

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

I would like to know that too. Anyway, how is it possible to verify the version of OLAP ? How can it be done? Maybe to somehow connect to server where the olap is being used?

Ok, how do I do this ?

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Ok, Apply buttons not working in Report Manager

This is weird. I have 2 different installs of Reporting Services and both do
nothing when trying to upload a file (rdl, datasource, etc)
Basically, the buttons don't post. This also happens on the apply buttons
in the property pages. Cancel and other buttons work.
I am thinking I need something in my aspnet_client folder that is not there.
I have the 1_1_4322 folder.
Any ideas?Nevermind...This happens if you misconfigure stuff when moving virtual
directories to another site.
"Eric" wrote:
> This is weird. I have 2 different installs of Reporting Services and both do
> nothing when trying to upload a file (rdl, datasource, etc)
> Basically, the buttons don't post. This also happens on the apply buttons
> in the property pages. Cancel and other buttons work.
> I am thinking I need something in my aspnet_client folder that is not there.
> I have the 1_1_4322 folder.
> Any ideas?

OK button is not working on file upload page

Hi everybody,
I hope than somebody can help me to fix my reporting services server.
I can upload reports on my reportin services server i dont know why but when
i press the OK button nothings happend everything is working bit i need to to
ulpload to new reports but i can't...
maybe if smebody know some diagnostic tool than can help to fix the problem
will be great.
plase, help....
Thanks.This happened to me once. But with the Apply button in the parameters section
of the report manager. This indicates the installation of reporting services
is corrupted and I had to reinstall it on the server.
Maybe that's not the problem for you, but it solved my problem.
"Edman" wrote:
> Hi everybody,
> I hope than somebody can help me to fix my reporting services server.
> I can upload reports on my reportin services server i dont know why but when
> i press the OK button nothings happend everything is working bit i need to to
> ulpload to new reports but i can't...
> maybe if smebody know some diagnostic tool than can help to fix the problem
> will be great.
> plase, help....
> Thanks.

Office Interop Assemblies and SSIS

Hi guys,

I'm trying to write an SSIS script that will create an excel file and append data to it.

I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.

However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.

If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.

Am I missing anything obvious?

Thanks for your help in advance

Mike

Do you have the Visual Studio Tools for Office 2007 installed?

Just curious, why create the excel file in the script task?

|||

Mike_Gaziotis wrote:

Hi guys,

I'm trying to write an SSIS script that will create an excel file and append data to it.

I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.

However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.

If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.

Am I missing anything obvious?

Thanks for your help in advance

Mike

This should help.

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)

(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx)

-Jamie

|||

The VSA designer's "Add Reference" dialog does not look for assemblies in the GAC but only in the %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies and %windir%\Microsoft.NET\Framework\v2.0.50727

We recomend that you copy them to the SQL server location pointed above for the "Add Reference" to work.

|||Thanks very much Jamie and Silviu!
Putting the DLLs in the .NET Folder seems to have done the trick.

In response to jwelch's question: I would have used the standard Data Flow task but it didn't seem to support some of the functionality i needed (ie dynamically building the file path and file name based on content etc)

Thanks again guys

Mike
|||

Actually, that sounds like a great opportunity to use variables or expressions. If you describe your scenario a little more, perhaps there might be some alternative approaches.

Not trying to change your mind on direction, just want to make sure you know what options are available.

|||Hi Jwelch,

Always keen to find out if there are more approaches that i haven't considered. Even if they're not used in this scenario it's good to know of the possibilites

Here are my SSIS job requirements:
- Execute (several) SQL queries which retrieve report data
- Insert the data in worksheets of several excel files
- Save the files by:
a: Putting a timestapt in their name (ie reportA_04072007)
b: storing them in different locations on the server based on the report
(ie %Reports%/ReportA/reportA_04072007.xls)
- Send mails with excel files attached

This is to be a scheduled job managed by the SQL server agent.

It's not a job of enormous complexity i must admit, but i somehow i found myself (perhaps because of me being a developer rather than a DBA by nature) more keen to write code than having to use loops, data flow tasks and all the rest. In fact when i first had the problem with the assemblies i half considered writing a windows app that would do that job and then run that from the sql server agent. And really, is there such a big difference between writing an app or a script-only SSIS job?

Thanks and sorry for the babble
Mike

odd behaviour when I load a file into a table

Hi all of you,

I've got an issue with a plain file. This file owns (n) rows. Some of them have less information than others.

I mean:

1234enricenric1enric3

1235enric2

1236enric4

1238

1239

File is loaded into the table successfully but my DTSX only loads all those rows totally full, the rest ones no. ????

1234enricenric1enric3

I'm stuck with this and at the same time, suppose that it'll be a stupid thing.

Thanks indeed for your time,

Enric

Hi Enric,

Does the package fail? I mean does it fail to insert the other rows or it just doesn't insert them but succeeds?

|||

Package doesn't fail. How odd!

Just doesn't insert them.

|||

Please check that on the flat file source editor, in the Error Output tab, u have not selected "Ingore the failure" on the "On error" or "On truncation". Anyway,what is happening is rather odd. I guess the file is tab separated so can u check also that all tabs exist? I mean in the flat file source editor, in the preview, can u see all the columns and values?

|||

Yeah, Ignore failure doesn't appear either Truncation or Error column...

Yes, from the flat file source editor I can see all the columns without problems.

I've checked that file and no, it haven't tabs as separator among columns...

|||

On general tab I've got the following values:

Format: Ragged Right

Text Qualifier: <none>

Header row delimiter: {CR} {LF}

Header rows to skip: 0

Thanks again,

|||Hmmmm....Ragged right format uses width to define all columns, except for the last column, which is delimited by the row delimiter. You can do a small test.. convert the file in a tab separated values file and try to import it, to see if the problem continues to appear.|||

[...]convert the file in a tab separated file

How can I do such thing?

I'm seeing that file using TextPad and I don't see at all tabs.

|||

The fastest way is to import the text file to Excel and exports it as a tab separated values text. The other thing you can do is just delete the spaces between values and add a tab between columns.If the column does not have a value then press tab again etc so that all columns exist in a line. If there are too many lines then just do a test with the first 5 lines or so.

|||

alas_gr wrote:

The fastest way is to import the text file to Excel and exports it as a tab separated values text. The other thing you can do is just delete the spaces between values and add a tab between columns.If the column does not have a value then press tab again etc so that all columns exist in a line. If there are too many lines then just do a test with the first 5 lines or so.

Using the second method you still wont see tabs but there will be there. You can see that when you move the cursor across the line using the arrow keys. The cursor "jumps" when there is a tab.

|||I've analyzed again the file and definitely, it haven't tabs at all.|||

I've created a new file taking the first one with..only two rows. One fully supplied with data and the another one partially.

The same.

I'm totally stuck, this is the last DTS to export to SSIS...

|||What I am suggesting is to make the last file (the one with the 2 rows) a tab separated value text. So get rid of the spaces and make it tab delimited. That means that you will have to change the delimiter to tab in the flat file source editor. So convert the test file a tab separated values txt and give it a try.|||

Hi,

I've done as you said me. Converting the file to .XLS and then exporting to .TXT again, keeping tabs between columns and only a row has been loaded (it had 2). Issue remains.

|||

Ok...try this : right click on the connector between flat file source and OLE DB Destination (or SQL Server Destination, whatever you are using) and add grid a data viewer (so to be sure that rows are read correctly). If 2 rows appear then probably there is something wrong/miscofigured in the destination component. And if you give me more details about the table you are trying to insert into and any constraints that exist.

cheers

odd behaviour when I load a file into a table

Hi all of you,

I've got an issue with a plain file. This file owns (n) rows. Some of them have less information than others.

I mean:

1234enricenric1enric3

1235enric2

1236enric4

1238

1239

File is loaded into the table successfully but my DTSX only loads all those rows totally full, the rest ones no. ????

1234enricenric1enric3

I'm stuck with this and at the same time, suppose that it'll be a stupid thing.

Thanks indeed for your time,

Enric

Hi Enric,

Does the package fail? I mean does it fail to insert the other rows or it just doesn't insert them but succeeds?

|||

Package doesn't fail. How odd!

Just doesn't insert them.

|||

Please check that on the flat file source editor, in the Error Output tab, u have not selected "Ingore the failure" on the "On error" or "On truncation". Anyway,what is happening is rather odd. I guess the file is tab separated so can u check also that all tabs exist? I mean in the flat file source editor, in the preview, can u see all the columns and values?

|||

Yeah, Ignore failure doesn't appear either Truncation or Error column...

Yes, from the flat file source editor I can see all the columns without problems.

I've checked that file and no, it haven't tabs as separator among columns...

|||

On general tab I've got the following values:

Format: Ragged Right

Text Qualifier: <none>

Header row delimiter: {CR} {LF}

Header rows to skip: 0

Thanks again,

|||Hmmmm....Ragged right format uses width to define all columns, except for the last column, which is delimited by the row delimiter. You can do a small test.. convert the file in a tab separated values file and try to import it, to see if the problem continues to appear.|||

[...]convert the file in a tab separated file

How can I do such thing?

I'm seeing that file using TextPad and I don't see at all tabs.

|||

The fastest way is to import the text file to Excel and exports it as a tab separated values text. The other thing you can do is just delete the spaces between values and add a tab between columns.If the column does not have a value then press tab again etc so that all columns exist in a line. If there are too many lines then just do a test with the first 5 lines or so.

|||

alas_gr wrote:

The fastest way is to import the text file to Excel and exports it as a tab separated values text. The other thing you can do is just delete the spaces between values and add a tab between columns.If the column does not have a value then press tab again etc so that all columns exist in a line. If there are too many lines then just do a test with the first 5 lines or so.

Using the second method you still wont see tabs but there will be there. You can see that when you move the cursor across the line using the arrow keys. The cursor "jumps" when there is a tab.

|||I've analyzed again the file and definitely, it haven't tabs at all.|||

I've created a new file taking the first one with..only two rows. One fully supplied with data and the another one partially.

The same.

I'm totally stuck, this is the last DTS to export to SSIS...

|||What I am suggesting is to make the last file (the one with the 2 rows) a tab separated value text. So get rid of the spaces and make it tab delimited. That means that you will have to change the delimiter to tab in the flat file source editor. So convert the test file a tab separated values txt and give it a try.|||

Hi,

I've done as you said me. Converting the file to .XLS and then exporting to .TXT again, keeping tabs between columns and only a row has been loaded (it had 2). Issue remains.

|||

Ok...try this : right click on the connector between flat file source and OLE DB Destination (or SQL Server Destination, whatever you are using) and add grid a data viewer (so to be sure that rows are read correctly). If 2 rows appear then probably there is something wrong/miscofigured in the destination component. And if you give me more details about the table you are trying to insert into and any constraints that exist.

cheers

ODBC's

I want to add an ODBC to a SQL database to everyone's computer in my
company. Is there a simple way to create a file that can add it, or do I
have to run through ODBC administrator on every machine to add it?
Thanks,
ScottScott Cadreau wrote:
> I want to add an ODBC to a SQL database to everyone's computer in my
> company. Is there a simple way to create a file that can add it, or do I
> have to run through ODBC administrator on every machine to add it?|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use a DSN-less connection string in the application. E.g.:
ODBC;Driver=SQL Server;
Server=MyServer;Database=MyDatabase;Trus
ted_Connection=Yes
You can use an IP address in place of "MyServer." Trusted_Connection is
the Windows Authentication. If you're using SQL logins replace
Trusted_Connection to:
UID=UserLogin;PWD=UserPassword
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQkN13IechKqOuFEgEQJQ0QCfXHLMeabeh1B6
oSw35+VfhW/mCqgAoOVp
4cuoUPssOpC40aPYF6CSfmtu
=lR4H
--END PGP SIGNATURE--
Scott Cadreau wrote:
> I want to add an ODBC to a SQL database to everyone's computer in my
> company. Is there a simple way to create a file that can add it, or do I
> have to run through ODBC administrator on every machine to add it?
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

ODBC Text file to SQL conversion

I have a large comma delimited text file (130K records) that I want to convert to an SQL data file. I want to use this data file as a datasource in a Visual Basic 2005 application.

Can I use the txt file directly?

If not, how can I convert/import the data into an SQL data file?

Thanks, Bob

Steve Kass has good info:
http://www.users.drew.edu/skass/sql/TextDriver.htm

You could also use openrowset() to query the data.

exec master..xp_cmdshell 'echo "this is a test" >> c:\temp\test.txt & echo
"this is a test2" >> c:\temp\test.txt'
go
select *
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=c:\temp\;HDR=NO',
'select * from test.txt')

ODBC Text Driver Issue for Tab delimited File

I am trying to do a ODBC Connection to a Tab delimited file. However it is
not parsing the tabs. Any advice?
Thanks
Here is my connection string:
Private ConnectionString As String = "Driver={Microsoft Text Driver (*.txt;
*.csv)};DRIVERID=27;Fil=Text;Format=TABDELIMITED;" & _
"COLNAMEHEADER=TRUE;DefaultDir=" & _
Application.StartupPath
And my calls to query the file:
Dim Adapter As New OdbcDataAdapter("SELECT * FROM Test.csv", Con)
When you use the Text Driver, the format is determined by
the schema.ini file. The format statement goes in the
schema.ini file. That's how I remember it anyway.
The following link has more information on the drive and a
link to information on the schema.ini file - watch out for
line wrap on the link:
http://msdn.microsoft.com/library/de..._details. asp
-Sue
On Tue, 1 Feb 2005 06:55:04 -0800, "Neil"
<Neil@.discussions.microsoft.com> wrote:

>I am trying to do a ODBC Connection to a Tab delimited file. However it is
>not parsing the tabs. Any advice?
>Thanks
>Here is my connection string:
> Private ConnectionString As String = "Driver={Microsoft Text Driver (*.txt;
>*.csv)};DRIVERID=27;Fil=Text;Format=TABDELIMITED; " & _
> "COLNAMEHEADER=TRUE;DefaultDir=" & _
> Application.StartupPath
>And my calls to query the file:
> Dim Adapter As New OdbcDataAdapter("SELECT * FROM Test.csv", Con)
>
>
|||Thanks Sue. That was the solution.
Neil
"Sue Hoegemeier" wrote:

> When you use the Text Driver, the format is determined by
> the schema.ini file. The format statement goes in the
> schema.ini file. That's how I remember it anyway.
> The following link has more information on the drive and a
> link to information on the schema.ini file - watch out for
> line wrap on the link:
> http://msdn.microsoft.com/library/de..._details. asp
> -Sue
> On Tue, 1 Feb 2005 06:55:04 -0800, "Neil"
> <Neil@.discussions.microsoft.com> wrote:
>
>

ODBC Text Driver Issue for Tab delimited File

I am trying to do a ODBC Connection to a Tab delimited file. However it is
not parsing the tabs. Any advice?
Thanks
Here is my connection string:
Private ConnectionString As String = "Driver={Microsoft Text Driver (*.
txt;
*. csv)};DRIVERID=27;Fil=Text;Format=TABDEL
IMITED;" & _
"COLNAMEHEADER=TRUE;DefaultDir=" & _
Application.StartupPath
And my calls to query the file:
Dim Adapter As New OdbcDataAdapter("SELECT * FROM Test.csv", Con)When you use the Text Driver, the format is determined by
the schema.ini file. The format statement goes in the
schema.ini file. That's how I remember it anyway.
The following link has more information on the drive and a
link to information on the schema.ini file - watch out for
line wrap on the link:
http://msdn.microsoft.com/library/d...ail
s.asp
-Sue
On Tue, 1 Feb 2005 06:55:04 -0800, "Neil"
<Neil@.discussions.microsoft.com> wrote:

>I am trying to do a ODBC Connection to a Tab delimited file. However it is
>not parsing the tabs. Any advice?
>Thanks
>Here is my connection string:
> Private ConnectionString As String = "Driver={Microsoft Text Driver (
*.txt;
>*. csv)};DRIVERID=27;Fil=Text;Format=TABDEL
IMITED;" & _
> "COLNAMEHEADER=TRUE;DefaultDir=" & _
> Application.StartupPath
>And my calls to query the file:
> Dim Adapter As New OdbcDataAdapter("SELECT * FROM Test.csv", Con)
>
>|||Thanks Sue. That was the solution.
Neil
"Sue Hoegemeier" wrote:

> When you use the Text Driver, the format is determined by
> the schema.ini file. The format statement goes in the
> schema.ini file. That's how I remember it anyway.
> The following link has more information on the drive and a
> link to information on the schema.ini file - watch out for
> line wrap on the link:
> http://msdn.microsoft.com/library/d...a
ils.asp
> -Sue
> On Tue, 1 Feb 2005 06:55:04 -0800, "Neil"
> <Neil@.discussions.microsoft.com> wrote:
>
>

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC SQLBindColumn to a T-SQL variable not working

Hello,

I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.

Any ideas what I am doing wrong?

Thanks!
CB

DECLARE @.i AS INTEGER
SELECT @.i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

This code generates an error for me, but that is because you are not actually assigning the value of the count to the variable. Try this:

Code Snippet

DECLARE @.i AS INTEGER

SELECT @.i = COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

|||Sorry that was a typo. I do have the equals sign. When it does run, the C variable that I bind to the SQL variable i with SQLBindColumn() doesn't contain the value after SQLExecute().

ODBC SQLBindColumn to a T-SQL variable not working

Hello,

I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.

Any ideas what I am doing wrong?

Thanks!
CB

DECLARE @.i AS INTEGER
SELECT @.i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

This code generates an error for me, but that is because you are not actually assigning the value of the count to the variable. Try this:

Code Snippet

DECLARE @.i AS INTEGER

SELECT @.i = COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

|||Sorry that was a typo. I do have the equals sign. When it does run, the C variable that I bind to the SQL variable i with SQLBindColumn() doesn't contain the value after SQLExecute().

ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

When I run this SSIS package on my developer PC (from inside BIDS or using the Management Studio Execute Package Utility) it works. However when I run the package from within a SQL Server Agent Job it fails.

I have enabled logging and the error message given is:

System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

I now have it working. The problem was that I did not have administrator access to the server, and so was using a network share to store the configuration file. I now how admin access to the windows server, and have moved the config file to the server's C:\, which has fixed the problem.

Matt

|||

Thanks for posting the solution, I am just ran into that problem today - ODBC works fine on local machine but when I move to production I get the password length error, even though I set the password in my package. I will try the package configuration file tomorrow.

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.

ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

When I run this SSIS package on my developer PC (from inside BIDS or using the Management Studio Execute Package Utility) it works. However when I run the package from within a SQL Server Agent Job it fails.

I have enabled logging and the error message given is:

System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

I now have it working. The problem was that I did not have administrator access to the server, and so was using a network share to store the configuration file. I now how admin access to the windows server, and have moved the config file to the server's C:\, which has fixed the problem.

Matt

|||

Thanks for posting the solution, I am just ran into that problem today - ODBC works fine on local machine but when I move to production I get the password length error, even though I set the password in my package. I will try the package configuration file tomorrow.

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.