I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load for any specific date, package should be set for yesterday's date dynamically. How to achieve this as I am new to SSIS.
View 2 RepliesI have a ssis package which runs daily. This ssis package has couple of execute sql tasks which load data for yesterday's transaction. Ex.
INSERT INTO Shipped (Div_Code, shipment_value, ship_l_id, shipped_qty, shipped_date, whse_code,
ord_id, ship_id, ship_l_ord_l_id, Created_date) select ord.DIV_CODE as div_code, ship.SHIPMENT_VALUE as shipment_value, ship_l.SHIP_L_ID as ship_l_id, ship_l.SHIPPED_QTY as shipped_qty, ship.SHIPPED_DATE as shipped_date, ship.WHSE_CODE as whse_code, ord.ORD_ID as ord_id, ship.SHIP_ID as ship_id, ship_l.ord_l_id as ship_l_ord_l_id, Getdate() as Created_date from SHIP ship, ORD ord, SHIP_L ship_l where ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))) and ship.WHSE_CODE='WPP' and ord.ORD_ID=ship.ORD_ID and ship.SHIP_ID=ship_l.SHIP_ID
All execute sql task has query like above query. and in some query we have date filter which loads data for yesterday. Ex. one query has ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). some other query has ord.trans_date=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). this package runs daily through sql server job, so It loads data for yesterday. Now If i want to run for any particular date, How could we achieve from ssis?
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.
Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.
However, no matter what I did I always got 99 records when I joined.
I have a table in Server A and it has 5 columns. One is Address & ID, CreateDatetime,..
I need to transfer data from this table from Server A to server B for a report pupose. The Address column in this table has some places two address in the table. I am giving ex below
Address
Houston, Dallas
Redmond
Sacramento
New Jersey, New York
I want to avoid the rows where address are two Houston, Dallas & New Jersey, NewYork to the destination table in server B and need to do incremental loads.
how to proceed with this?
The version we are using is Sql Server standard edition 2008r2
What is the syntax to verify that the partition data is loaded into the correct partition.
View 0 Replies View RelatedWhat is the syntax to verify Partition data load.
View 1 Replies View RelatedI'm struggling with trying to display a single vertical stripline at a date value along my x-axis.
I'm using the following non-default settings within the stripline editor:
bordercolor = tomato
borderstyle = solid
borderwidth = 1pt
intervaloffset = Fields!uidate.Value.ToOADate()
where uidate is a calculated field in my dataset that always has the value of 05/29/2012 (using formatdatetime("05/29/2012",dateformat.ShortDate))
Everything else is set to Auto, with stripwidth=0
I'm trying to display a single vertical line at the x-axis value of 05/29/2012
I have previously tried setting the interval offset to "05/29/2012" but that didn't work. I've also tried setting the value of a hidden text box on the report to "05/29/2012" and setting the interval offset to the value of that text box, but that gave me a "text box not declared" error.
How I can simply display a vertical stripline on my line graph at the x-axis value of 05/29/2012 ?
I am trying to get the yesterday's date without the time, eg "1/25/2006 " but I will get "2006-01-25 17:10:21.403" instead. Can somebody tell me the sql for it? currently I am using :
CONVERT(DATETIME, DATEADD(day, -1, GETDATE()), 101))
My BIDS installation is able to create new projects and open saved ones, but if i try to add an existing package from a remote server, it throws the error pasted below. I can see the package list, but once i choose the package, hit ok, then hit ok to load it, I get the error. I can open the same package on a different computer, so it's not user rights related. Below the error is version info. Thanks in advance for any help you might be able to give!
===================================
One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
(Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.LoadFromSqlServer(String packagePath, String serverName, String serverUserName, String serverPassword, IDTSEvents events)
at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.<>c__DisplayClassa.<LoadPackageFromSql>b__9(String password, IDTSEvents events)
at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageWithPassword(PackageLoader loader, IWin32Window dialogParent, String& packagePassword)
at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageFromSql(String packagePath, String server, String sqlUserName, String sqlServerPassword, IWin32Window dialogParent, String& packagePassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.LoadPackage(String& packagePassword)
at Microsoft.DataTransformationServices.Project.Controls.AddExistingPackageForm.AddExistingPackage()
at Microsoft.DataTransformationServices.Project.Controls.AddExistingPackageForm.AddExistingPackageForm_FormClosing(Object sender, FormClosingEventArgs e)
===================================
One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadFromSQLServer(String bstrPackagePath, String bstrServerName, String bstrServerUserName, String bstrServerPassword, Boolean bLoadNeutral, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.LoadFromSqlServer(String packagePath, String serverName, String serverUserName, String serverPassword, IDTSEvents events)
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: IDE Standard
Microsoft Visual Studio 2005 Tools for Applications
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
Hello there,Im not quiet sure this is the right forum, but what the...I got a database with eg. dates in..(Day-Month-Year)01-02-200802-02-2008 <-- Today03-02-2008
Then i want to find the date for yesterday, in this example 01-02-2008, and delete the record.. - How is this done?Hope you understand and can help me,Regards Jeppe Richardt
I need to create a variable expression that will be passing yesterday's date to a sql command but when I create the variable there is only the datetime datatype, how can I trim the time portion from this expression to get the date portion :
DATEADD( "day", - 1 , GETDATE() )
OK, the thread's name is not very self explanatory so here I go:
The fact
I'm working on a simple APP where I store the date of a transaction. I use in my Stored Procedure the getdate() as the default (and untouchable) value for the transaction date
The problem:
These transactions are made all day long and there are transctions made on the morning and afternoon. Now, when I make a report of the transactions for one specific range of days, all the transactions made in the morning of the start date are ignored completely.
Say I have these transactions
TCode TType TDate
45 REQ 03/09/2007 08:20:16 a.m.
46 REQ 03/09/2007 11:59:43 a.m.
47 REQ 03/09/2007 12:01:30 p.m.
48 REQ 03/09/2007 06:01:49 p.m.
and I make this query
SELECT * FROM <Table Name> WHERE TDate BETWEEN <Start Date> AND <End Date>
(where <Start Date> && <End Date> are 03/09/2007 selected from a DateTimePicker control)
I only get these results 47 REQ 03/09/2007 12:01:30 p.m.
48 REQ 03/09/2007 06:01:49 p.m.
if I make a cast as integer I get these results:
TCode TDate TCast
46 2007-09-03 11:59:43.000 39326
47 2007-09-03 12:01:30.000 39327
so my question is
Why are the Morning Transactions from the start date ignored (or interpreted as part of previous day transactions) ?
SQL 2008 R2
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
I am developing the SSIS and stuck on copying specific files.
1. We receive CSV file to our drive on a daily basis.
2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.
3. There are a lot of files in this directory.
[URL]
What we would like to do:
Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.
I have 3 tables as per following:
orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5
ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015
product
ProductdescMinQty
Item1This is 110
Item2This is 220
I want to pull only the 1 line for minqty for an item as follows
OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015
I have some source tables like Customer, Order, ship, item, invoice. Among these source tables, I have to create 5 dimension tables and 1 Fact called orderFact using sql server queries just to test data. So i have created 5 dimensions and pulled dimension keys from each dimension and loaded into fact using join. For measures I have joined those 5 sources created a Rawfact table which have all measures.
Now loading into fact I have joined Rawfact with all dimensions and get keys and for measures i directly pulled from rawfact. Is this process right or we can do it by some other method?
And I want to avoid any Cartesian product for below queries. What I can do to avoid this?
Dimension:
DimCustomer, DimOrder, DimShip,DimItem, DimInvoice and Fact is FactOrder:
Loading Rawfact:
select o.ord_id, o.full_order_value,o.open_order_value,o.div_code, o.order_type_code,o.order_status,o.order_date,
s.num_of_pallets,s.num_of_cartons,s.shipment_value,s.ppd_coll,s.ship_status,
i.invoice_amt,
it.net_weight, it.gross_weight,it.warranty_days,it.item_type,it.item_num,
c.terr_code, c.largest_bal,c.last_amt_pay,c.last_inv_amt,c.num_invoice_paid,c.cust_num,
from order o
[Code] .....
I need to load a customer database onto our SQL 2008 server. I always use restore database option in the management studio and create a new database from device (customer database backup file), it used to work just fine. But when I do the same now, I get this below error:
CREATE DATABASE permission denied in database 'master'.
RESTORE HEADERONLY is terminating abnormally.(Microsoft SQL Server,Error: 262)
I also tried to create new database option in the management studio, but get the same error. I did run management studio as 'Run as admin'.
Server - Windows 2008 R2
SQL Server 2008 R2, SP1
How do i get only the data for the last date in the query. I just want the most current date. Which is yesterdays information. But on monday, its fridays information. So is there a code for getting the most Current Data. If not whats the code for getting the last business day such as friday for today. But for tommorow it will be monday. My time_log field has the date converted into mm-dd-yyyy. Any help?
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Visits_6_Daily]
(@Region_Key int=null)
AS
BEGIN
SELECT dbo.Qry_Visits.Status,
dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],
B.VisitsTotal
FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status
FROM dbo.Qry_Visits
WHERE Qry_Visits.Status=2
GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status
HAVING SUM(Visits) < 6)B
ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND
CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate
WHERE REGION_KEY=@Region_Key and Qry_Visits.Status=2 and CONVERT(varchar, dbo.Qry_Visits.time_log, 110)=GETDATE()
ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)
END
SET NOCOUNT OFF
I am running SQL 2008 Enterprize Edition with SP1 on Windows 2008. I am trying to set up replication. I have completed the following:
1. Created distribution Database
2. Created publisher
3. Granted SQL Agent access to the ...MSSQL100Com folder to execute the agent_exe files
4. Granted SQL Agent access to ...MSSQLinn where the subsystem_dll files are located
5. Granted SQL Agent write permissions to ...MSSQL
epldata in order the write the bcp files
Each time I try to initialize the snapshot, I get the following errors in the SQL Agent Log
1. Log Step.......cannot be run because the LogReader subsystem failed to load. The job has been suspended.
2. Log Step.......cannot be run because the Snapshot subsystem failed to load. The job has been suspended.
I found posts where the records in the msdb.dbo.syssubsystems pointed to different folders than where the dll and exe files are located. So, I checked that, but they are correct.
The SQL Agent has sysadmin on the SQL Server and is using a windows service account.
I believe it is a security issue because I can run the executables from the command prompt to generate the snapshot for the publication. Have I missed the forest for the trees?
I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
View 4 Replies View RelatedHere's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
We have a database in SQL Server 2008 R2 with mirroring and want that replication is done by dedicated network.We stop the endpoint and when we try to run the following command, syntax error occurred:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '192.168.1.14'.
What is the correct syntax of the command line below?
ALTER ENDPOINT Endpoint_Mirroring AS TCP (LISTENER_IP = '192.168.1.14')
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
View 0 Replies View RelatedI have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
Hi,
Can anyone tell me how to get data for yesterday, last month, and last year?
As a example, I wanna know how many loans disbursed for yesterday(not for last 24 hours).
It may be smthing like this. But when I run this one , it gave me a syntax error.
WHERE DATEDIFF(dd,Account.CommencedOnDate,GetDate()) = 1
can anyone help me out pls?
Howto write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i don't want. everyday i need to search the data from yesterday whole day.
View 13 Replies View RelatedI am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
Hi,
Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005.
Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008.
So, the same way I need to know the week number of a given (variable) date.
I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date.
regards,
Saibal
Note : Please feel free to write me if my question is not clear.
I have a list of students who have taken a test on different dates.
I need a function to calculate year ago date from the date of test.
eg Sam took the test on feb 23 2015
I need to get a feb 23 2014 to see what subjects he studied from feb 23 2014 to feb 23 2015.
How can I do that.