How do I add an ODBC connection data source as a Data Flow Source

I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.

Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?

Thanks in advance

ADG

Use DataReader Source for ODBC connections.|||

How do you know that the slowness is in the OLE DB sauce component and not in the destination component? How are you setting up the OLE Source component? Providing a query would be faster than selecting a table from the dropdown list.

In general I would not expect ODBC to be faster than OLE DB. but you can test it; create 2 different dataflows 1 with datareader(ODBC) and other with OLEDB (provide a query!) and then connect it to a rowcount or union all transform. When you run the packages you will be able to tell how long it takes to 'read' the data.

How do I add a new column to an existing Data Source View in SSRS?

I've got a table in my Data Source View that is one of our central entity
tables in our warehouse, and there are LOTS of relationship lines (Roles)
linking to this table. We've just added 6 new columns to this table, and I
need to add them to the Data Source View so the new columns will be available
to the end users running Report Builder.
I am pulling my hair out trying to find the option to add the new columns!
Completely removing and re-adding the table is NOT an option, as we have 37
relationship lines coming into this central entity table.Hello here,
From your description, my understanding of this issue is that, you add some
new columns in the source table in database and you want to reflect in the
Data Source View. If I am offset, please feel free to let me know.
Based on my research, you could not add the new column in the data source
view directly. My suggestion is that you could regenerate the model. Since
the wizard will generate the relationship automatically, you will not
concern about creating many relationships.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thanks for your post. The wizard does not automatically re-establish all
the relationship lines. These 37 relationship lines I had to manually create
the very first time when I generated the model, even though most of them
already have a foreign key in the database expressing the relationship. I do
not want to have to manually re-create all these relationship lines. Also, I
have other computed expression columns that would be blown away if I
re-generate the entire Data Source View using the Wizard. I would have to
manually recreate those as well.
"Wei Lu [MSFT]" wrote:
> Hello here,
> From your description, my understanding of this issue is that, you add some
> new columns in the source table in database and you want to reflect in the
> Data Source View. If I am offset, please feel free to let me know.
> Based on my research, you could not add the new column in the data source
> view directly. My suggestion is that you could regenerate the model. Since
> the wizard will generate the relationship automatically, you will not
> concern about creating many relationships.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
I would like to suggest you use the Refresh button in the DSV designer,
then use the Generate option on the corresponding entity in the report
model.
Please let me know if this resolved your problem.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Perfect!! The Refresh button did the trick!! Thank you so much!
=Steve=
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to suggest you use the Refresh button in the DSV designer,
> then use the Generate option on the corresponding entity in the report
> model.
> Please let me know if this resolved your problem.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Glad to hear that you resolve this issue. If you have any question, please
feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

How do I add a data source view and report model to an existing project

I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?

Report Models are part of a separate project type. Create a new "Report Model" project and you will be able to create the DSV and report model there.

sql

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the ans

We are going to need a little more context to evaluate your exact situation, and how to do what you ask, but here's 1 method:

Dim sqlConn as new SqlConnection("connection string here")

Dim sqlCmd as new SqlCommand("SELECT password from users where username = @.username", SqlConn)

sqlCmd.Paramaters.AddwithValue("@.username", username.text)

Dim DA as new SqlDataAdapter(SqlCmd)

Dim DT as new DataTable

DA.Fill(DT)

Dim myString as string = DT.rows(0).item("password").value ' This line may not be totally correct, doing it from memory and will edit it later

If myString.tolower = stringToCompare.tolower then

'Password is valid

else

'Password could be valid - but something threw you into the else statment, maybe an empty string.

end if

|||

Connection string and select command are already defined in the SqlDataSource.

how can you use the SqlDataSource to extract the value of the password?

i tried to use a hidden GridView but i didn't know how to access a specific cell

by the way i am using C#

|||

The above example is a much cleaner method of getting information like this. Putting a hidden gridview on your page is a VERY heavy method of trying to extract a single value, but it can be done.

Please see this article on how to extract a Datatable from the SqlDataSource control. Once you've done that, you can access a specific row of your datatable as mentioned above.

http://msmvps.com/blogs/egoldin/archive/2006/12/27/how-to-get-datatable-out-of-sqldatasource.aspx

I really have to recomend against this method though. If you insist on using the SqlDataSouce control to get this done, consider putting two hidden textbox on the page, and binding the value that comes back from the database to the hidden textbox, then use a compare validator and only let the user continue if page.isvalid.

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?!

to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the answer

Hello Ahmad,

Here's some code that should help you out... I'd be happy to clarify anything if needed.

using System.Data;

using System.Data.SqlClient;

string userPassword;

string userName;

SqlConnection cnn = new SqlConnection("Data Source = (local); Initial Catalog = nameofdatabase; Integrated Security = True");

SqlCommand cmd = new SqlCommand("select Password From Users Where username = @.username", cnn);

SqlParameter UserName = new SqlParameter("@.username", userName);

cmd.Parameters.Add(UserName);

cnn.Open();

string password = (string)cmd.ExecuteScalar();

cnn.Close();

cnn.Dispose();

if (userPassword == password)

{

// success! Do something here

}

else

{

// Failure...

}

Regards.

sql

How can you use VSS to manage stored procedures

Can anyone explain how to use the VSS integration in SSMS to mange stored
procedures? We use VSS to manage source code in Visual Studio but I can't
figure out how this is done with stored procedures in SMSS
What I do is:
1) Create a Solution for stored procedures.
2) Add the solution to Source Control
3) Add a new query.
4) Select a stored procedure
5) Script the SP as CREATE to clipboard
6) Paste into the new query
7) Add extra code at the bottom to grant permissions
8) Rename NewQuery.sql to my_SP.sql in the solution explorer
9) Check in pending check ins
10) repeat steps 3 to 9 until you have all your SP's under Source Control
I always script an existing sp as create so that I can't accidentally hit F5
You can do things like add a separate project to the solution for each
database or a separate solution for each database or any other way that
suits your way of organising things.
Regards,
Nigel Ainscoe MBCS MCP
"D. Haber" <DHaber@.discussions.microsoft.com> wrote in message
news:7BFAB085-3D2F-4FB0-9822-BE9BDEC2DB52@.microsoft.com...
> Can anyone explain how to use the VSS integration in SSMS to mange stored
> procedures? We use VSS to manage source code in Visual Studio but I can't
> figure out how this is done with stored procedures in SMSS
>
>

How can pass variable or parameter in DATA READER SOURCE ising ADO:NET Connection manager

In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .

I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .

Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...

Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa

USe an expression to override SQLCommand porperty of the data reader:

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

How can i write polling and processing queries

i dont want to fully update the molap cache and i also dont want to fully read the source database that is why i am using polling query to poll the changes and processing query to get the changed records from database. for information about polling and processing query you can check the link

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

but i am unable to write these queries to get all changes i.e insert, update and delete records can any one tell me how can i use these queries OR SQL Server provide some other way to just read the changes in source database not the whole dabatase.

This is

about proactive caching, right? If the changes in the table happen to be

insert, update and delete then the processing will *most probably* need to be

done fully.

When you end up with full processing, if you need to use polling notification

mechanism then the processing query is not needed. In case of deletes or

updates the polling query should fetch something like timestamp of the last

change in the database. Why not to use automatic SQL notifications?

The link you provided discusses polling and processing queries for incremental

processing. It is possible when only new records appear in the table (only

inserts).

There is one case when incremental processing can be used with "inserts,

deletes and updates". Suppose at time t1 you have a state of records S1 of

your table. Suppose you manage to make proactive caching to be scheduled at

well known time t2. I suppose Client Initiated is the only reliable one for

deterministic start of the proactive caching. If between t1 an t2 the inserts,

updates and deletes in the table do not change the records from S1 but only

create and modify incremental records then it is possible to use incremental

processing with proactive caching.

How can I use several resultsets from stored procedure?

I have create stored procedure that returns 2 resultsets. When I configure OLE DB Source to use this procedure I can not add second output for he source. I get following error:

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Source [1]]: An output cannot be added to the outputs collection.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

How to add second output?

The stock OLEDB source component supports one non-error output.

To populate multiple outputs from a source component (perhaps to avoid a double extract), you can write a script source transform with as many outputs as necessary. Building multi output transforms (script and custom) is not much more difficult than a single output source component.

Furthermore, you can write a custom source tranform if you wish to re-use the code (aka toolbox resident) more easily than cut/paste re-use of script components.

If a little coding is not up your alley, then you're stuck with a single output.

Of course there are probably workarounds to simulate two outputs, such as interleaving/marking the columns of the two result sets such that they form one result set you can later conditionally split, but that is likely beside the point.