I have an odd one. I have a SQL job that doesn't have a schedule and is being run each morning. It is a legacy system and I am trying to document the data flow process and I am having a hard time tracking down where/what is starting the job. I see which user executed the job:
SELECT message FROM sysjobhistory WHERE job_id = 'jobid' AND run_date > 'yesterday'
Which is useful, but I want to know what is starting the job.
Hi All,
I created a SSIS package to extract data from a flat file source and load them into a table in a data base. After I created the package i checked it in to source control(perforce).
But the problem is once a month new flat source file comes and data should be updated.
Once the new flat file comes, is there anyway that SSIS package can identify the path of the flat file and execute the package automatically? In Flat file source only the data will be changed. Not location or data type or anything.
Can i use parameters to do that?
Thanks
We run a weekly update on one of our databases from a download (FTP) from our mainframe. What I want to do is start the update job automatically after all of the files are downloaded.
I have a similar process in another database where the users set a flag in an application after they've made certain changes. The job fires daily at 18:00 and checks the value of the flag in the table. It then processes or goes back to sleep. This works great but there is no flag that the users can update in this application.
Thanks in advance.
Sidney Ives
Database Administrator
Sentara Healthcare
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies View RelatedI have the following sets of records:
ColA ColB
----- -----
21 A
22 A
23 A
24 B
25 B
26 D
What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:
ColA ColB ColC
----- ----- -----
21 A 1
22 A 1
23 A 1
24 B 2
25 B 2
26 D 3
I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.
I want to identify rows that go negative but only for 2 cents or more as well as identify rows that 2 or more.
I have this expression that does not work how I want it to work:
CASE
WHEN (SUM(FavUnfavCostChange) < (2/100) THEN 'Less'
WHEN SUM(FavUnfavCostChange) > (2/100) THEN 'More'
ELSE NULL
END AS 'Flag'
But I get:
0.00000815000000000000More -- this is not more than 2 cents, is just a positive number
-0.00094700000000000000Less -- this is not less than 2 cents, is just negative number
-0.00222000000000000000Less -- this is not less than 2 cents, is just negative number
-0.00012250000000000000Less -- this is not less than 2 cents, is just negative number
0.00000000000000000000NULL -- this is zero so null is fine
0.01188576000000000000More -- this is not more than 2 cents, is just a positive number
I am in process to develop TSql code to identify change in data.
I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.
But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?
select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');
Is there a way to identify who have changed the SQL port number other than the DBA's?
For example through c2 audit or any other triggers?
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
I have a job that runs a dtsx package (containing an infinite loop). What I want to do is set up a schedule to start the job at 8am and stop it at 5pm. The scheduling GUI doesn't give this kind of option (caveat: that I can see).
View 3 Replies View RelatedI'm running SQL 2012 on a Win 2012 VM and the SQL server shuts down automatically after starting the SQL Server & SQL Server Agent Jobs.
View 9 Replies View RelatedI am trying to use SQL Server 2012 for the first time. I am running Win 8.1. I create a local website and sample webpage using Visual Studio 2012 for Web. Running this page on my localhost, when it attempts to create a new SQL database this exception is thrown:
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."
When I start SQL Server Agent it's shutting down minute after starting. I tried to set Alerts to send email. After I configured Database Mail ,I was able to send test email , however after that SQL Server Agent started to misbehave.
View 3 Replies View RelatedIf Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
Hello,
I had created a windows service using C#. I set its Startup Type as Automatic but it is not getting started automatically when my System Starts.
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
How to identify whether the files are in read write or read only?
View 1 Replies View RelatedWhen our rep distribution services time-out (several times a day) some dba's just restart the service while others restart the rep job which starts the service. Are there any differences between these two methods? Does restarting the service inherit any changes made to the job's attributes?
Thanks!
We have SQL 2000 (SP2) , Our SQL server agent showing green light as started from EM , It is succussfully restarting from NT Services too . But When I open the properties of the jobs from EM its gives error message SQL DMO error " SQl Agent is starting , try later " . And in EM Next run Date & Time not available .
MDDB database is accessable & in Log file its recovered fully
Thankx in Advance
Sha
I am working on task where I need to load data into DWH from OLTP. Load needs to b incremental load. For this I am planning to use CDC, to track changes and only load data which has been new inserts/updates/deletes. But to load data to destination, there are couple of joins, which I need to do. Is there a way in CDC, where I can use query as source?
View 0 Replies View RelatedWe have a job running the SSIS Package. I am working with that SSIS Package. The SSIS package has a script task. In the script task they used the variable of source path. I want to change the value of that variable (Need to modify the source path).
I modified the variable value in that package and in the config file but getting error message
Exception has been thrown by the target of an invocation.
We need to replicate data from a live database to a reporting database nightly.
There is data which is only needed for the reporting function - namely the selection criteria for reports which are to be run nightly or monthly.
Is it possible to have extra tables in the target database, holding the selection criteria?
If not, we'll have the reports running heterogeneous queries between the target database and a separate database with the selection criteria in it.
I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.
I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.
Passing a date parameter in a stored procedure through OLEDB Source (Data flow task). We have a multipurpose stored proc in which one of the many expected parameter is a date. It keeps giving me the following error:Description: "Operand type clash: int is incompatible with date"- although there is no integer.
One way to fix it would be change the data type from date to datetime in proc but I can't do that since it is multi-purpose proc. I didn't find anything good on the net.
I am not able to see the Data Source in Visual studio 2012. I have tried several things like 'Alt + shift + D' which is meant to bring up the datasource tab but it does not. I have also looked in view->Other windows but its not displayed there.
View 5 Replies View RelatedI have a SSIS package that simply moves data from a SQL database A to another SQL database B. I have update (increased) the size of a nvarchar column, on both A and B.I am wondering if there is a way to "refresh" somehow the SSIS package so I don't have to rebuild and redeploy it.The error I get now is a truncation error: "Text was truncated or one or more characters had no match in the target code page".
View 2 Replies View RelatedI need to use the same SQL source code in more than one stored proc.
Is there a way to -include mysql.sql or something like that ?
Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
I am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
[Code]....
Was wondering if there was a best practice minimum permissions for creating a SQL login to use when setting up a new shared Data source for SSRS report manager?
Something along the lines of them being a data read for the DB and permissions to update tempdb?
Would have thought it not advisable to have the login be able to update the main db...
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
View 4 Replies View Related
Hi,
How would I know that a dll component I built is in .NET framework 1.0 or 1.1??
annej
How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit
l've written it in layman's terms .Is it wise to use substring or they is a better method......
select
case
when amount is (negative) then 'C'
when amount is (positive) then 'D'
else
end
from table_Tran
Greetings.
I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched.
However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )
Tipps, suggestions, help, all is very wellcome! :-)
DBCC SHOWCONTIG scanning 'TInsurance' table...
Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 1275
- Extents Scanned..............................: 225
- Extent Switches..............................: 224
- Avg. Pages per Extent........................: 5.7
- Scan Density [Best Count:Actual Count].......: 71.11% [160:225]
- Extent Scan Fragmentation ...................: 74.67%
- Avg. Bytes Free per Page.....................: 520.0
- Avg. Page Density (full).....................: 93.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.