|
Hi,
I built an SSIS package locally and now I need to deploy it to our development instance of SQLServer...can anyone tell me how to go about doing that?
"I need build Skynet. Plz send code"
|
|
|
|
|
ok...I've got the thing deployed, but I'm trying to run it from a job. If my job is running in a database called "dev" and a package named "foo" is running in Integration Services on "dev", how would I go about referencing it in my job step command?
(just a stab)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobID, @step_name=N'Execute SSIS package',
@step_id=1,
@command=N'/FILE "\\dev\MSDB\Maintenance Plans\foo.dtsx" /CHECKPOINTING OFF /SET \Package.Variables[User::Correlator].Properties[Value];' + @Correlator + ' /REPORTING E'
Looking at my @command assignment, is anything missing?
I have the thing deployed to MSDB\Maintenance Plans, but I really don't know if the reference is as simple as this.
..Is it? I'm getting an "XML Parsing error"(<-pseudo) that seems to be a permissions error when trying to open the package. Either the package doesn't exist at the location specified or my account doesn't have access to the file...I'm pretty confident that I have access to the file, so my other logical option is that the context that I have provided in my snippet above is incorrect.
Does anyone know how I should go about referencing it?
"I need build Skynet. Plz send code"
|
|
|
|
|
|
Thank you very much for the documentation, but I had already referenced the materials at those links. My specific question was surrounding the command-line reference that I was making to my package after it had been deployed.
...at any rate, I found out what my problem was. I was attempting to reference the package with the FILE switch, but I had not deployed the package to the file system of the database server which I was attempting to reference (I had deployed it directly to the SQL Server instance)
...I still am unsure of how I would go about referencing the package from SQL Server directly, but I repeated my deployment, this time to the file system, and adjusted my reference to the location on the file system, and the package was able to be loaded at run-time.
just fyi...anyone referencing a package with the "FILE" switch, make sure your package is deployed to the file system lol ...it'll save you a night's sleep.
"I need build Skynet. Plz send code"
|
|
|
|
|
Hi
Im Ashutosh
Student In MCA
Respected Sir
I create a webApplication in ASP.Net i want to store Vedio, Audio, Images, file in my SQL Server2008 so plz give me a solution Imidiat
Yours
Ashutosh
91-8115736699
|
|
|
|
|
monulove2u wrote: I create a webApplication in ASP.Net
then continue to work on it and when you stuck in any specific problem, CP members are here to assist you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Go and Google about HTML 5, ASP.Net Audio examples, Video Example. Try it. And when you are stuck with doubts, come and ask here. 
// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫
|
|
|
|
|
|
Knock yourself out. No, seriously...
|
|
|
|
|
Here borrow my hammer, it is available immediately!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Steps
1. Learn basic .Net
2. Learn about ASP
3. Learn basic database including SQL. This has nothing to do with steps 1/2.
4. Learn how "blobs" are stored/retrieved in the database. Nothing to do with steps 1/2
5. Learn to use SQL in .Net.
6. Put the above together to create a program that does what you want.
|
|
|
|
|
use BLOB (binary large object) datatype in sql
and if using .NET then send the image or video as parameter to query.
try it .
|
|
|
|
|
So we've got a single Solicitations table that logs all outbound solicitations that get wrapped up into a file and dropped on our FTP site as well as holding columns for handling the inbound processing when the records make their roundtrip back to our system.
So, for instance:
Table has columns
GUID|AccountID|AccountNumber|PhoneNumber|FirstName|LastName|Street|City|State|Zip|Code|CodeDescription|Agent|Date
GUID, AccountID, AccountNumber, PhoneNumber, City, State, & Zip are stored in the Log table on outbound processing, with the inbound columns defaulted to NULLS.
When the information comes back into our system after the Marketing call has been made, it will return all of the data that we sent out plus the remaining fields all in a csv format.
So, a record might go out looking like this:
'1234-5678-ABC-BLAH',5,'666321234','800-123-4567','Chasey','Lane','976 Gloryhole Ave','Los Angeles','CA','66699'
and it will come back looking like this:
'1234-5678-ABC-BLAH',5,'666321234','800-123-4567','Chasey','Lane','976 Gloryhole Ave','Los Angeles','CA','66699',170,'Sealed the Deal','Ron Jeremy',07/06/2011
For the inbound processing, I want to create an SSIS package with a Data Flow that uses a Flat File Source to pick up the inbound file and have an OLE DB Destination execute a SQL Command that maps the inbound fields to the specific columns that are coming out of the csv file.
I'm basically to here:
UPDATE Solicitations SET Code = ? , CodeDescription = ? , Agent = ? , Date = ?
WHERE GUID = ?
AND AccountID = ?
AND AccountNumber = ?
How do I reference the Flat File Source in my command? How do I assure that the correct parameter values are being pushed into the command in the correct reference positions? Am I overthinking the problem?
"I need build Skynet. Plz send code"
|
|
|
|
|
I have backup file which is backed by using ms-sql2008.
So Restore destination is ms-sql 2005.
Is it possible?
if can, how?
hi
My english is a little.
anyway, nice to meet you~~
and give me your advice anytime~
|
|
|
|
|
No, you can not. Your only choice (that I know of) is to script out the 2008 database in 2005 compatibility mode, create it in a 2005 server, and transfer the data over.
Scott
|
|
|
|
|
It doesn't work even if you set your database compatibility to SQL Server 2005 before taking the backup. So the only way is as suggested by scottgp.
|
|
|
|
|
Hi all,
I have a stored procedure sometime I get deadlock condition in it. Actually as per requirment I have open cursor in it 3 time also updation some tables. Can any one let me know how can I detect it.
Regard's
Kaushik
|
|
|
|
|
|
For deadlock detection I am using SQL Server Profiler to detect locks. Also I run a script to detect the longest query time execution and see what is actually happening in my procedure.
Also check this article. I think it will be more useful than to run SQL Profiler.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
modified on Monday, July 11, 2011 4:50 AM
|
|
|
|
|
I would like to know which of the following options will produce the fastest transactions (and why). Unfortunately, I don't know enough about the low-level "nuts and bolts" of database operation to really have a good intuition on this.
My PHP script resides on one server, my MySQL database resides on another. I believe that by performing operations on a regular MyISAM table, I am incurring time costs related to both 1) communicating between the web server and the database server, and 2) performing disk operations because of MyISAM is disk-based storage engine. I do not, however, know how much of the time cost is associated with each of these factors or if both are really significant (in the sense of important).
I have some temporary data that I want to manipulate on a per-session basis, and the way I see it I have two options:
I can create a local memory database and create a table there.
$wm = sqlite_open(':memory:', 0666);
sqlite_query($wm,"CREATE TABLE patterns (pattern varchar(500))");
Or, I can create a table in memory on the existing MySQL database.
mysql_connect("not.localhost.com", "admin", "example");
mysql_select_db("example");
mysql_query("CREATE TEMPORARY TABLE patterns (pattern VARCHAR(500)) ENGINE=MEMORY");
Which one of these will be faster to perform queries on? Will there be a noticable difference? My intuition is that the second will be slower because it is a transaction on a non-local database, but I don't know this for sure. When the table is in-memory, which computer's memory is it actually in? How much volume would have to be going on for a difference to actually be noticable?
Any thoughts would be very much appreciated.
--Greg
|
|
|
|
|
GregStevens wrote: Any thoughts would be very much appreciated
I think you should take measurements, and note down the version of the database that you tested.
GregStevens wrote: Which one of these will be faster to perform queries on?
I'm hoping Sqlite, since it could be a fast implementation in native code without much overhead.
GregStevens wrote: My intuition is that the second will be slower because it is a transaction on a non-local database, but I don't know this for sure.
How about putting it one the same machine?
You also could consider Times Ten[^] from Oracle.
GregStevens wrote: When the table is in-memory, which computer's memory is it actually in?
That depends on the specific implementation; most of the time it will be in memory that's managed by Windows, putting it into the virtual memory area. Others might have optimizations.
GregStevens wrote: How much volume would have to be going on for a difference to actually be noticable?
You can review the amount of free memory using the Task Manager;
Your computers fysical memory - used memory = free memory
Once the system starts to page out memory, you'll notice delays. That can range from minimal delays (say, served from the 64Mb buffer of memory in your harddisk) to large delays (Windows reshuffling a lot on disk, paging in and out other applications that are also running, your computer nearly grinding to a halt)
Bastard Programmer from Hell 
|
|
|
|
|
Hi
I understand what index seek means - but not sure about distinction between Index Seek, RID Lookup (Heap based what the hell...) and Key Lookup (clustered index lookup)
From this reference: [^]
RID Lookup is a form of a bookmark index lookup on a table without a clustered index (a heap).
> So both clustered and non-clustered is on the disk and SQL server actually build another index in memory/heap?! And all RID's (Row ID) are stored in volatile memory!?
key lookup is a bookmark lookup on a table with a clustered index.
> So how is it different from Clustered Index Seek?
I'm a bit unclear on seek vs lookup, but seems like "non-clustered seek" (non-clustered ONLY)[^] fetches only the RID, database engine still need to perform a "lookup" by RID to fetch the actual values of selected columns.
For "clustered index seek", leaf is actual data so there should never be lookup for rows fetched via "clustered index seek" (thus no idea what a "Key Lookup" is about)
Similar question to http://www.web-shelf.com/microsoft.public.sqlserver.relationalserver.performance/index-seek-with-lookup-vs-index-seek-with-include-thread-ID1202.aspx[^]
Adding to confusion, from MSDN[^]- so RID is in heap (where? All rows RID in heap/memory!?) and Key lookup is done via clustered index on the disk? HEAP (aka "Base Table) is not Clustered Index which is stored on disk.
RID Lookup is a bookmark lookup on a heap using a supplied row identifier (RID). The Argument column contains the bookmark label used to look up the row in the table and the name of the table in which the row is looked up. RID Lookup is always accompanied by a NESTED LOOP JOIN.
Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the clustered index. For more information about read-ahead, see Reading Pages.
Thanks
dev
modified on Tuesday, July 5, 2011 11:46 PM
|
|
|
|
|
I think that you've been thrown off by the meaning of the word "heap". Although it usually means what you think (i.e. an in-memory data structure[^]), in the context of the discussions that you quoted it means a heap-based table[^].
Anyway, Key Lookup is logically identical to RID Lookup. Physically, there are significant differences, but nine times out of ten you can count them with the rest of unimportant implementation details.
|
|
|
|
|
Many thanks
I understand the word "Heap" as I am a programmer - but how is it possible for SQL server to put in heap/memory RID's of all these million rows tables!
dev
|
|
|
|
|
Hi guys. This post is building upon the solution from my previous post in here on Jun 29th.
Synopsis: Building a scheduling/routing application on top of a data-driven app that lives in SQLServer that identifies customers that have dropped our service for "Winback" call center campaigns (and the like)
The logic for each of the campaigns is encapsulated within a query run within a stored procedure that is located on our Reporting database (it runs there because Reporting has the $**T indexed out of it) The stored procedure is executed from our local production database across a server link. My application knows absolutely nothing about the underlying stored proc. It knows of a command that it is wishing to execute for a particular campaign, and I have a mapping table that stores a pointer to the remote stored proc. I have a brokerage entry point exposed as a stored proc to my application that accepts parameters, dips into the local Mapping table, pulls the name of the stored proc, executes it across the database link, receives the result set, generates a GUID for the batch, enters the results into a local table called "Solicitations" with the GUID as a correlator and returns the GUID to the calling client.
...I was proud that I was able to accomplish all of that. I am nowhere *NEAR* what one would call a "DBA" and all of my "education" is in Oracle9i. I'm currently "working" in SQL Server 2008R2.
But now: All of the previous work has been the "sourcing" side of our process. Being able to get the data out of Reporting and into our local table is just the data pump that allows our campaigns to run. After the calling client receives the GUID as its return, I need to pass the GUID into an SSIS package that can receive that GUID, identify the campaign that is being run, dip into the mapping table to pick up the filename associated with that campaign, pull the relevant rows out of the Solicitations table, map them to a canonical format, and FTP them to an FTP server that acts as an integration point between our Customer/Account system and the system that manages call routing for our call center.
I have created an SSIS package that has a Data Flow task that Flat File Exports the Solicitations table to csv as "testy.csv" on my local file system and then an FTP task that picks that resulting file up and :21's it to the integration point. I went to the server and picked the file up and it was the file I was expecting. All of the guts of the "happy path" flow are there.
My actual question:
What I am needing to do is:
1) configure the working Data Flow Task such that it can receive a GUID and select items from a view where the correlator in the table matches the GUID for the batch.
2) I'm also needing to create a task that receives that GUID and retrieves the OutputFilename from the Campaign Mapping table so that it knows which file to pick up and transport.
3) I'm needing to modify the FTP Task such that the filename is the variable set in (2) <-I'm thinking #3 is going to be the easy part.
Has anyone ever done this before? Can anyone provide an assist on how to accomplish 1-3 above?
Thank you very much!
"I need build Skynet. Plz send code"
modified message subject on Wednesday, July 6, 2011 1:24 AM
|
|
|
|
|
I found an answer to my question. I created a stored procedure that accepts the GUID as a parameter and within the proc, I (create, add, and kick off) a job that executes the SSIS package as its only step, passing the GUID into the command line args of the command property of sp_add_jobstep.
Right now, the only thing in my way is that the server was restarted last night and the Job Agent wasn't part of the restart....so I'm waiting for server support to get back from lunch so that I can test out my implementation.
If anyone has any questions about my approach, I'd be more than happy to share.
"I need build Skynet. Plz send code"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
