Looking for the equivalence (date issue)

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Copy Folders and Files to different location One way would be a For Each Loop container with Traverse subfolders enabled, with a File System task inside it to move the files once they've been processed. Tag: Looking for the equivalence (date issue)
  - 2
    - Permissions to view Stored Procedure Property To grant permissions on a stored procedure In Object Explorer, connect to an instance of SQL Server 2005 Database Engine and then expand that instance. Expand Databases , expand the database in which the stored procedure belongs, and then expand Programmability . Expand Stored Procedures , right-click the procedure to grant permissions on, and then click Properties . From Stored Procedure Properties , select the Permissions page. To grant permissions to a user, database role, or application role, click Add . In Select Users or Roles , click Object Types to add or clear the users and roles you want. In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions, see Permissions . Selecting Grant indicates the grantee will be given the specified permission. Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals. Tag: Looking for the equivalence (date issue)
  - 3
    - data file blanks to nulls You can use a Derived Column as well (NULL(DT_*)). If you aren't dealing with many columns, the Derived Column approach is fine. However, if you have a lot of columns to check, the script approach can be easier, as you can set it up to process all the columns generically, checking for and replacing blanks. Tag: Looking for the equivalence (date issue)
  - 4
    - Send an email on non-fatal errors from Data Flow Hi Sandy, you should try to use Event Handlers. Event Handlers will allow you to build additional control flows that get activated by events (OnError, OnWarning, etc) posted from executables (a data flow in your case) you are monitoring. HTH. Tag: Looking for the equivalence (date issue)
  - 5
    - Service Broker "TO service could not be found" Message origin: "Transport" Just a guess, but probably your service is named 'tcp://mydomain.com/TARGET/MyService', not '[tcp://mydomain.com/TARGET/MyService]' Tag: Looking for the equivalence (date issue)
  - 6
    - Distinct Count with Junk Dimension Attribute Are you excluding the ALL member Take a look at the code sample below It calculates a count 3 ways. In the first, I count the members of the dimension's attribute hierarchy. It includes the members from all levels including the ALL member because I didn't specify a level. In the next one, I specify the leaf level of the hierarchy which does not include the ALL member. The third example uses DISTINCTCOUNT. Code Snippet with member measures.a as COUNT ([Geography].[Country]. Members ) member measures.b as COUNT ([Geography].[Country].[Country]. Members ) member measures.c as DISTINCTCOUNT ([Geography].[Country].Country. Members ) select {[a],[b],[c]} on 0 from [Adventure Works] ; Good luck, Bryan Tag: Looking for the equivalence (date issue)
  - 7
    - Trying to set output variable to row count result of SQL Execute Task Try naming your output (Select count(*) as recordCount...) BobP Tag: Looking for the equivalence (date issue)
  - 8
    - How can I set SO_TIMEOUT on the sockets used by the JDBC 2005 driver? Hi Steve, From the problem description above it sounds like you are looking for a way to set the timeout on a query execution. This can be done using the "setQueryTimeout" API on the SQLServerStatement object. For additional information please refer to the following MSDN topic: http://msdn2.microsoft.com/en-us/library/ms378081.aspx If you are requiring a connection timeout setting, you can specify "loginTimeout" on the connection URL or on the SQLServerDataSource object. For additional information on the connection URL properties please refer to http://msdn2.microsoft.com/en-us/library/ms378988.aspx . If you are using the SQLServerDataSource object, please refer to http://msdn2.microsoft.com/en-us/library/ms379035.aspx for additional info. The Microsoft SQL Server 2005 JDBC driver has built-in tracing using the java.util.logging API. For additional information about the different levels and categories, please refer to the following topic: http://msdn2.microsoft.com/en-us/library/ms378517.aspx HTH, Jimmy Tag: Looking for the equivalence (date issue)
  - 9
    - xquery - order by problem Try Code Snippet select @x . query ( 'for $x in /LogicalFolder/Items/FileNode order by $x/FullPath[1] return $x ' ) Tag: Looking for the equivalence (date issue)
  - 10
    - Very hard question on how to query data and return by months - Which version of SQL Server are you using - Can you post the query Search for PIVOT in this group. AMB Tag: Looking for the equivalence (date issue)
  - 11
    - Database attach at setup You can always use CREATE DATABASE mydb ON ( FILENAME = 'mydb.mdf') to attach a database after installing sql server. refer to http://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx for more details. or you can do attach database via management studio Look into this link: http://msdn2.microsoft.com/en-us/library/ms190209.aspx Tag: Looking for the equivalence (date issue)
  - 12
    - File System Watcher can not read excel from SharePoint Plase Tag: Looking for the equivalence (date issue)
  - 13
    - how to set date field in sql server 2005 table Sekar: Look up DATE AND TIME and related articles in books online to begin to get an understanding of the DATETIME datatype. Understand that there is no such datatype in SQL Server for DATE -- the datatype is DATETIME. As such, you cannot store date only in this datatype. The next thing that you need to give a look to is SET DATEFORMAT in books online. This will show you how to set your format to the DMY format you indicated. Code Block SET DATEFORMAT DMY Normal practice is to store a time of 12AM or 00:00:00 when the intention is to store date only in a datetime column. This is in fact what the database does by default. If you store a datetime of 18/2/7 in the database it will store this date and assume a time of 12AM. The way that this gets handled is by properly formatting the datetime columns into varchar strings when displaying the datetime. This is accomplished by using the CONVERT function. To select only the date portion from a column named "yourDate" in a table named "yourTable" the select might look something like: Code Block select convert(varchar(10), yourDate, 103) from yourTable Take note of the "103" in the convert function. That is the format specification to format datetime columns into a date format of DD/MM/YY. Give a look at the following articles in books online: DATE AND TIME SET DATEFORMAT CONVERT Tag: Looking for the equivalence (date issue)
  - 14
    - SQLSTATE 42000- Error 15404 with ADS Which service account is the SQL Server running with Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: Looking for the equivalence (date issue)
  - 15
    - HOW TO: Print a report directly to a printer After posting this yesterday. I found some problems with the code. This works great if your report is 8.5x11, but it still lacks features for printing landscape. I am working on a better solution. This all started from the need to send a reportname, a parameter, a printer name and number of copies to a program that would render and print a SSRS report. (Trying to replace a cludgy Crystal solution). Part of this goal is to avoid any UI and run this as a service on a report server, not to be confused with a sql reports server. So if anyone has better ideas , please let me know. Daryl Tag: Looking for the equivalence (date issue)
  - 16
    - LAG MDX statement Lag() takes a numeric argument, so if the attribute: [Dim Time].[DayNumberofYear] has an integer member value defined, you could use: .Lag( [Dim Time].[DayNumberofYear].MemberValue ). For example, the Adventure Works DayOfYear attribute has an integer value: >> With Member [Measures].[BegOfYear] as [Date].[Calendar]. Lag ( [Date].[Day of Year]. MemberValue - 1). MemberValue select {[Measures].[BegOfYear]} on 0 from [Adventure Works] where [Date].[Calendar].[February 1, 2002] ----------------------------------------------------------------------------- BegOfYear 1/1/2002 >> Tag: Looking for the equivalence (date issue)
- Next
  - 1
    - How to make this DATE format This: SELECT convert( varchar(16), getdate(), 120 ) Returns: ---------------- 2007-05-07 13:04 Refer to Books Online, Topic: 'Cast and Convert' for the 'style' (120, above) values. Tag: Looking for the equivalence (date issue)
  - 2
    - Can't figure out this error! To troubleshoot it, try creating a version of the flat file with only three rows - one opening, one closing, and one with the six columns. Add a MsgBox(Row.Line) as the first line of code. See which one (if any) breaks it. Or you could put a Try..Catch around the whole thing, with the MsgBox call in the Catch section. Tag: Looking for the equivalence (date issue)
  - 3
    - How to avoid non-published columns deletion on subscriptions' reset Can you explain the identity problems you are having You should be able to run dbcc checkident('tablename',reseed,somevalue) on a published or subscribed table. Note that with immediate updating rows inserted on the subcriber are inserted on the publisher first and pick up their identity value from there, before being inserted on the subscriber. So this might be problematic for you. Tag: Looking for the equivalence (date issue)
  - 4
    - Instead of INSERT trigger!! Just remove every "ELSE" and you should be fine. (except for the last one) Adamus Tag: Looking for the equivalence (date issue)
  - 5
    - Urgt Pls:Cell merge prob What are the controls you are using in your report.Check their alignment If it is textboxes,see to it if their edges are aligned with respect to one another.. if your edges are like this,it will merge the cells | | the alignements must be : | | Tag: Looking for the equivalence (date issue)
  - 6
    - Referencing an alias in a where clause You cannot use a column alias in the where clause, you just have to duplicate the calculation. I cannot see anything wrong with what you are trying to do. My only guess is that perhaps there is a datatype problem. Are both @project and actv.projID the same datatype Also, what version of SQL server is this Tag: Looking for the equivalence (date issue)
  - 7
    - Execute SQL Task: variable mapping failed with complex SQL script. Please help! I have had similar problems, and I suppose Paramters work best on simple SQL like : select * from myTable where myField= and myField2= In such cases, I would try two things: 1. Pass three args to my SP, and let the SP do the logical condition 2. Use a SQL Statement from Variable, with a variable that has "Evaluate as Expression"=True. Its expression would result in the right SQL. e.g. User: utputFile="NotUsed" "Dummy SQl that does no work", "Exec SPU_CreateOutput" + " " + User::InputFile + "," +User: esiredOutPut HTH Kar Tag: Looking for the equivalence (date issue)
  - 8
    - MSSQL_REPL55012: An unspecified error had occurred in the native SQL Server connection component. Hello Jason, You should configure Distributor, Publisher and Subscriber frame first. I think you are trying to publish a publication before configuring these components Can you inform me if you configured Distributor and Publisher already Ekrem Onsoy MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE Tag: Looking for the equivalence (date issue)
  - 9
    - Log Shipping To make your secondary be available for read and write, you simply need to recover the secondary database RESTORE LOG your-db-name WITH RECOVERY Tag: Looking for the equivalence (date issue)
  - 10
    - Move data to DB2 on AS400 with SSIS BJ, The ODBC connection is easier to setup. Nonetheless, what problem are you having Please be specific. Tag: Looking for the equivalence (date issue)
  - 11
    - sql server perfprmance Have you tested the performance of your query outside of RS Do you have the appropriate indexes on the table With between style queries it's good to have the clustered index on your date column so the data is physically stored in the table in date order. This makes range lookups very fast. Also make sure you are not retrieving 500,000 rows back from your query to RS. Nake sure you do a GROUP BY in your query to minimise the amount of data left to process by your report. Tag: Looking for the equivalence (date issue)
  - 12
    - Passing Variables from URL and DDL combined In order to filter DDL2 byt the result of DDL1, use the parameter 1 value in the where clause of the dataset populating ddl2. For example: Parameter1 Dataset: Select Value, Label from table where GUID = @Guid Parameter2 Dataset: Select Value, Lable from Table2 Where FilterField in ( @Parameter1) That should do the trick... BobP Tag: Looking for the equivalence (date issue)
  - 13
    - Instance Name of SQL Server 2005 Standard Unless you specifically gave it a name during setup then there is no instance name as its a default instance, hence you connect with (local) Tag: Looking for the equivalence (date issue)
  - 14
    - Problems installing Reporting Services You obtain "Reporting Services", you have to download and install SQL Server Express Advanced Services. See: SQL Server 2005 Express Edition (Advanced/SSMS) http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx http://msdn2.microsoft.com/en-us/library/ms365247.aspx http://tinyurl.com/yelwr9 Tag: Looking for the equivalence (date issue)
  - 15
    - Adding Time In DateTime Field set @d1='01/02/2007 01:00:00PM' datepart(hh,@d1) return : 1 DATEADD ( hour , datepart(hh,@d1) , YourD ate ) add 1 hour to your data Tag: Looking for the equivalence (date issue)
  - 16
    - Large table search performance 70 columns in a table is probably too many. It is hard to say. But there are few options to consider: 1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table 2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also. Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations. The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload. Tag: Looking for the equivalence (date issue)

enric vives

Hi all of you,

1)Ok, this works properly:

ISNULL([Column 13]) NULL(DT_WSTR,1) : RIGHT([Column 13],2) + "/" + SUBSTRING([Column 13],5,2) + "/" + SUBSTRING([Column 13],1,4)

for (old dts2000 column transformation)

Fecha=right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsNull(Fecha) then
DTSDestination("FechaAp") = Fecha
Else
DTSDestination("FechaAp")=null
End If

2). But how must I do such thing for IsDate when you have not available that function

Fecha=right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsDate(Fecha) then
DTSDestination("FechaAp") = Fecha
Else
DTSDestination("FechaAp")=null
End If

Thanks a lot,

Top

Re: Looking for the equivalence (date issue)

jwelch

I think you'll need to use a script component to do this check. You can use the DateTime.TryParse method.

Top

Re: Looking for the equivalence (date issue)

enric vives

Thanks for that. It'll be tested.

Top