Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Expressions still get evaluated even though the task isn't executed

Whilst investigating the problem talked about here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=118141
I noticed something quite strange.

The symptom of whatever problem we are having (we don't know yet) is that some property expressions fail to get evaluated and cause the package to fail. ALL of these property expressions are in package-scoped eventhandlers.

Now, if I set DisableEventHandlers=TRUE on the package container the problems still occur. If I completely delete the eventhandlers the problem goes away.

So, it seems to me that even though DisableEventHandlers=TRUE, the property expressions within those eventhandlers are still evaluated.

Why is that? Its pointless to evaluate property expressions if they are not going to be used - it means the package is doing unnecassary work.

-JamieAny comments?|||Jamie! So sorry - I review every couple days and just found this. Let me see what I can find out for you.

hold tight.
Andrea|||cool. Thanks Andrea.

Exporting to multiple files from a single source table using DTS

Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.Can't you split up your export by using views with restrictions on your recordtype instead of your table?|||manthenasri, does DTS allow you to redirect output on the fly? From what I have seen the source and destination is fixed at design time.

Have you tried BCP with a query? You could use DTS to script your BCP commands to a CMD file. Then at the cmd prompt you could use DTSRUN to build the CMD file and then execute the CMD file. Or just set up a procedure to script the BCP commands and use xp_cmdshell.

Sort of bassackwards but it's the best I could come up with.|||Hello Paul

I do not know much about BCP. But here is the problem that i have to solve.

I have a table by name T1 which has the following data

Business Unit Name SSN RATE
1 Joe 923456789 40
1 Jenny 568234569 50
3 Mike 234198634 35
3 Meri 743579374 45

Now i need to create a two files by name 1.ben and 3.ben on the fly.
and them put in the data related to Joe and Jenny into 1.ben and the data for Mike and Meri into 3.ben. If there is more data related to other business units i need to create those files also.

Can you please tell me how i can accomplish using BCP.

Thanks,
Srinivas.|||I think the BCP command you want is...

bcp "select * from <dbname>.<owner>.<table> where <where clause>" queryout <file name> -c -T -S <server>

probably could wrap it all up like this:
create table manthenasri([Business Unit] int, [Name] varchar(15), SSN varchar(9), Rate int)
insert into manthenasri values(1,'Joe','923456789',40)
insert into manthenasri values(1,'Jenny','568234569',50)
insert into manthenasri values(3,'Mike','234198634',35)
insert into manthenasri values(3,'Meri','743579374',45)

declare @.bu int, @.TSQL varchar(255)
select @.bu = min([Business Unit]) from manthenasri
while @.bu is not null begin
set @.TSQL = 'bcp "select * from ' + db_name() + '..' + 'manthenasri where [Business Unit] = ' + cast(@.bu as varchar) + '" queryout ' + cast(@.bu as varchar) + '.ben -c -T -S ' + @.@.servername
print @.TSQL
-- master.dbo.xp_cmdshell(@.TSQL)
select @.bu = min([Business Unit]) from manthenasri where [Business Unit] > @.bu
end

Open Query Analyzer and run the above code. It should produce a few BCP commands. Make adjustments for your table and rerun the script. Next try cutting and pasting the results to a CMD prompt and see how it works. Once that's done you can un-comment the xp_cmdshell call and try running this on your server.

THIS IS UNTESTED CODE!

Look this over and post back with questions.|||Hi Srinivas ,

Use DTS designer add one data pump task for each condition and output file.

For example : Use select statement as the source of data pump task with where clause and desination to the the disired out put file.

cheers :)

Shaji

Originally posted by manthenasri
Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.|||Hai Paul


Thank you for the BCP solution that you have given. I really appreciate you patience in giving me a sample program also.

The sample worked just fine except for the brackets enclosing the variable @.Tsql. I think these brackets should not be there. I had to remove these brackets.

But now have a problem of reading from multiple files and posting that data into table. I have to do an isert and also an update on the table. How can i do that?

Thank you very much once again.

Srinivas.

Exporting SPROCS

Ok, im trying to the difficult task of taking a DB i have on my laptop (SQL 2005) and putting it on my server (SQL 2000). How I got to this situation is somewhat od, just got roped in to do some part time development work.

Anyway heres my problem, I have transferred all the data and tables across no problems, but when I try to xfer my Stored Procedures across i get the following error.

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.objects'.

I get to this point by going 'Generate Scripts' instead of Export Data, I then get it to generate the script making sure to click the generate as SQL 2000 property. Yet I still get this error. Any help you can offer is much appreciated. Thanks in advance

Dan

Hi,

I haven't tried to export sp's from 2005 -> 2000, but it seems that you have the wrong syntax in your sp's. Check out what syntax differences there are between 200 and 2005 regarding sysobjects etc.

What you are getting from "GENERATE AS SQL 2000" is that the wrapping t-sql is compliant with sql 2000, not what you have written in your sp's.

Regards