MDF Files currupted: source

Showing posts with label source. Show all posts

Friday, March 30, 2012

Named Query and Global Variables

Hello,
I want to filter records right in the Data Source View.
I did a "Replace Table with new Named Query" but how can I get it to filter
records by User!UserID?
Thanks,
-Will"I want to filter records right in the Data Source View."
What do you mean by this?
you want to filter your DATA? or do you want to set different
permissions for different users?|||I want to filter my data.
So "select * from table where field1=user!userid"
"sorcerdon@.gmail.com" wrote:
> "I want to filter records right in the Data Source View."
> What do you mean by this?
> you want to filter your DATA? or do you want to set different
> permissions for different users?
>|||Query parameters do not have to map to report parameters. RS automatically
creates a report parameter for every query parameter but you can delete them
and use an expression.
So, do this:
select * from mytable where field1 = @.User
RS will create automatically a report parameter called User. Instead of
using this mapping, you want to change it.
Click on the ... in the dataset designer, go to the parameters tab. On the
right side change from using a report parameter to using an expression. This
will bring you to the expression builder where you can set it to =User!UserID
Now, in the layout tab, report parameters menu, delete the report parameter
which was created for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>I want to filter my data.
> So "select * from table where field1=user!userid"
>
> "sorcerdon@.gmail.com" wrote:
>> "I want to filter records right in the Data Source View."
>> What do you mean by this?
>> you want to filter your DATA? or do you want to set different
>> permissions for different users?
>>|||I'm trying to use report models though.
So f I follow the example from msdn here:
http://msdn2.microsoft.com/en-us/library/ms156284.aspx
So after I create "Data Source View" I click on a Product table and click
replace with a named query, this let's me edit the sql or use the query
builder to filter records. In the "Filer" column for a field called user_id I
put =User!UserID, hoping that only products that user_id = User!UserId will
be returned. It does not like that filter, in fact I see nothing about global
variables or variables at all.
**The above is the way I'd like to do my filtering, but if not then:
I tried filtering in the "Report Model":
Looked at the fields, click "Add Group" and All must contain
dragged the user_id field into that "All must contain" box and set it to
user_id must contain GetUserID()
Not sure about the exact area's I clicked because I'm not by my development
pc. But I could not get the Report Model to Filter either.
I like the DataSource View filter because it's a universal way for anyone
who creates reports, in reportbuilder or dev. studio, to only have access to
records that have their userid.
-Will
.
"Bruce L-C [MVP]" wrote:
> Query parameters do not have to map to report parameters. RS automatically
> creates a report parameter for every query parameter but you can delete them
> and use an expression.
> So, do this:
> select * from mytable where field1 = @.User
> RS will create automatically a report parameter called User. Instead of
> using this mapping, you want to change it.
> Click on the ... in the dataset designer, go to the parameters tab. On the
> right side change from using a report parameter to using an expression. This
> will bring you to the expression builder where you can set it to => User!UserID
> Now, in the layout tab, report parameters menu, delete the report parameter
> which was created for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "WillG" <WillG@.discussions.microsoft.com> wrote in message
> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
> >I want to filter my data.
> > So "select * from table where field1=user!userid"
> >
> >
> > "sorcerdon@.gmail.com" wrote:
> >
> >> "I want to filter records right in the Data Source View."
> >>
> >> What do you mean by this?
> >>
> >> you want to filter your DATA? or do you want to set different
> >> permissions for different users?
> >>
> >>
>
>|||Sorry, I don't have any experience with report models. Can't help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:E1041A66-B463-4011-9EF6-0C0D11BF2E12@.microsoft.com...
> I'm trying to use report models though.
> So f I follow the example from msdn here:
> http://msdn2.microsoft.com/en-us/library/ms156284.aspx
> So after I create "Data Source View" I click on a Product table and click
> replace with a named query, this let's me edit the sql or use the query
> builder to filter records. In the "Filer" column for a field called
> user_id I
> put =User!UserID, hoping that only products that user_id = User!UserId
> will
> be returned. It does not like that filter, in fact I see nothing about
> global
> variables or variables at all.
> **The above is the way I'd like to do my filtering, but if not then:
> I tried filtering in the "Report Model":
> Looked at the fields, click "Add Group" and All must contain
> dragged the user_id field into that "All must contain" box and set it to
> user_id must contain GetUserID()
> Not sure about the exact area's I clicked because I'm not by my
> development
> pc. But I could not get the Report Model to Filter either.
> I like the DataSource View filter because it's a universal way for anyone
> who creates reports, in reportbuilder or dev. studio, to only have access
> to
> records that have their userid.
> -Will
>
> .
> "Bruce L-C [MVP]" wrote:
>> Query parameters do not have to map to report parameters. RS
>> automatically
>> creates a report parameter for every query parameter but you can delete
>> them
>> and use an expression.
>> So, do this:
>> select * from mytable where field1 = @.User
>> RS will create automatically a report parameter called User. Instead of
>> using this mapping, you want to change it.
>> Click on the ... in the dataset designer, go to the parameters tab. On
>> the
>> right side change from using a report parameter to using an expression.
>> This
>> will bring you to the expression builder where you can set it to =>> User!UserID
>> Now, in the layout tab, report parameters menu, delete the report
>> parameter
>> which was created for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "WillG" <WillG@.discussions.microsoft.com> wrote in message
>> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>> >I want to filter my data.
>> > So "select * from table where field1=user!userid"
>> >
>> >
>> > "sorcerdon@.gmail.com" wrote:
>> >
>> >> "I want to filter records right in the Data Source View."
>> >>
>> >> What do you mean by this?
>> >>
>> >> you want to filter your DATA? or do you want to set different
>> >> permissions for different users?
>> >>
>> >>
>>sql

Wednesday, March 21, 2012

Name and Description property of OLEDB Source components

I am adding two OLE DB Source components to my pipeline and giving them different names

(via the Sourcename variable) but like they are assuming default name and description i.e. OLE DB Source which is causing the following error message on opening the package that was generated.

The package contains two objects with the duplicate name of "component "OLE DB Source" (37)" and "component "OLE DB Source" (1)"

Here is how I am doing it, in the debugger the names in both instances seem to have set correctly based on the variable but when saved they are lost. What am I doing wrong here?

IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();

source.Name = Sourcename;

source.ComponentClassID = "DTSAdapter.OleDbSource.1";

source.Description = Sourcename;

Thanks

Just figured it out. So if anyone else is running into this you need to be aware of the fact that the name you provide to the IDTSComponentMetaData90 is reinitialized after you get the design time instance of the component.

So the Name and other properties have to be assigned after the design time instance is created.

Monday, March 19, 2012

Mysterious Stored Procedures

SQL Server 2005 sp1

Maybe you can answer this.

Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?

Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?

Thanks!

Frank

These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:

dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Good morning, Jens.

Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?

Thanks a lot.

Fernando M.

|||Yes, if you need created database diagrams you should leave the dbpoperties.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Mysterious Stored Procedures

SQL Server 2005 sp1

Maybe you can answer this.

Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?

Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?

Thanks!

Frank

These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Good morning, Jens.

Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?

Thanks a lot.

Fernando M.

|||Yes, if you need created database diagrams you should leave the dbpoperties.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Mysterious login error message

I get the following error messages in the sql server error log

Source Logon

Message
Error: 18456, Severity: 14, State: 11.

and

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]

The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages.
BTW: the two servers are in the same domain.

Did I miss something in configuration?

Thanks in advance for your help..

Jeff

It looks like a delegation problem. Most likely the credentials from a 3rd machine (most likely the client) are used to connect to the source SQL Server are being used to connect to the target SQL Server. If this is the case, the following articles will hopefully help you to fix it:

Security Account Delegation http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

Specifying Credential and Connection Information http://msdn2.microsoft.com/en-us/library/ms160330.aspx

Let us know if this information helped.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I do not think we have a 3rd machine/application to connect to the source server / or the target server. Actually, we setup both source server and target server (in our log-shipping scenario) to use the same domain account as SQL Server service and sql server agent service startup account.

For the 1st reference link you mentioned, i.e. Security Account Delegation http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp our environment is Win 2K3 EE + SP1 (64 bit) and SQL Server 2K5 EE + SP1 (64bit), so it seems unapplicable here.

Personally I believe it is has something to do with the log-shipping, because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.

Do you have some other thoughts regarding this?

Thanks a lot for your help, which is greatly appreciated...

BTW, in the error message

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
Here [CLIENT: 185.23.11.33] is the ip address of our source server (node 1 in two clustered server environment)

|||

Unfortunately I cannot think of any other reason why you would get this particular error message; hopefully somebody else may have a better answer. Another option could be to post your question on the SQL Server Database Engine forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1), hopefully there we may find somebody with more knowledge on log-shipping who may be able to answer your question.

-Raul Garcia

SDE/T

SQL Server Engine

|||With the help from MVP oj, I finally figured it out. The error appeared because a linked server (on the source) was created by the Log-shipping wizard ( but I still do not understand why Log-shipping needs a linked server), and after configured the linked server by giving a remote user with password, the error disappears.

Wednesday, March 7, 2012

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.
Can you edit the posting and change it to DTS?
Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.
I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).
I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281
Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281
Odd that there is no SSIS category available...

There is... Sort of. It's DTS.
Can you edit the posting and change it to DTS?
Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.
Can you edit the posting and change it to DTS?
Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

Monday, February 20, 2012

My package does not take the values configured in the configuration file.

Hi all,

I created a simple file system task to copy a file from one location to another on my local system.

I used variables to specify the source and destination paths and now in my configuration file if I configure the values to something else also the package is taking the initial default values and not the values specified in the configuration file.

Can anyone throw some light on this.

Thanks in advance.

first of all check that u are using configutaion file in your package correclty

second check that variable is being mapped (present) in your configuration

these are all i can assume from ur explained scenario

write back to me if u find ne luck

regards,

Anas

|||Also, look at the execution log of the package; a warningis generated each time a configuration does not take place. If you are executing it via BIDS, go to the progress tab; otherwise enable package logging.

My guess is stored procedures

Greetings All!
I just started playing with MS SQL and was wondering how i could
possibly do the following for example.
I do a 'SELECT source FROM history' and that gives me a list of
sources. Now i want to run a SELECT for each source i received from
the previous SELECT.

For further clarity, in programming terms, it would be just like
looping through an array.

Thanks Guys,
PatricePlease define what you mean by "run a SELECT for each source". It might help
to post the DDL (CREATE TABLE statements) and some sample data (INSERT
statements) for your table(s). Also, show the end-result you would want to
get from that sample data.

In general in SQL you should try to minimise procedural code such as loops.
SQLServer is optimised for set-based queries. If you want to SELECT a set of
rows for each "source" then you can probably write that query as a JOIN to
one or more tables.

For example (from the Pubs database). To retrieve all the Titles for each
Author:

SELECT A.au_fname+' '+A.au_lname AS author, T.title
FROM Authors AS A
JOIN TitleAuthor AS B
ON A.au_id = B.au_id
JOIN Titles AS T
ON B.title_id = T.title_id
ORDER BY A.au_lname, A.au_fname

--
David Portas
SQL Server MVP
--|||Hi David,
Sorry for the lack of clarity.

My plan is to detect if any users login from more than 3 sources.

I have a history table
CREATE TABLE history(client_id int, when datetime, source varchar(20))

After some inserts a SELECT * FROM history looks like

-------------
|client_id | when | source |
-------------
|1010 | <date> | 202.22.21.33 |
-------------
|1818 | <date> | 192.10.22.31 |
-------------
|1542 | <date> | 222.452.1.36 |
-------------
|1010 | <date> | 192.22.21.33 |
-------------
|1010 | <date> | 242.22.21.33 |
-------------
|1010 | <date> | 256.22.21.33 |
-------------

so from this table i would to a
SELECT DISTINCT client_id from history (if thats the syntax) this would
return
----
|client_id |
----
|1010 |
----
|1818 |
----
|1542 |
----

Now from these values i want do a
SELECT count(DISTINCT source) from history where client_id = 1010

this would return 4. Since client_id 1010 logged in from 4 different
sources.
Then i want to run the same query with client_id = 1818 and so on. I.e
loop through the client ids.

How can i do this in a stored proc. My guess is cursors. Any help?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||No need for cursors or loops. This should give the result you want:

SELECT client_id, COUNT(DISTINCT source)
FROM History
GROUP BY client_id

--
David Portas
SQL Server MVP
--|||
Dave, you're a champ!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Friday, March 30, 2012

Named Query and Global Variables

Wednesday, March 21, 2012

Name and Description property of OLEDB Source components

Monday, March 19, 2012

Mysterious Stored Procedures

Mysterious Stored Procedures

Mysterious login error message

Wednesday, March 7, 2012

My SSIS problems text file, date data types

My SSIS problems text file, date data types

My SSIS problems

Monday, February 20, 2012

My package does not take the values configured in the configuration file.

My guess is stored procedures

MDF Files currupted

Blog Archive

About Me