SSIS Datetime Variable Perfoming &&>= Instead Of &&>
Mar 1, 2007
The source table has timestamp column (Modified Date), which gets upated with each modification via an update trigger.
I store the MAX(modifieddate) in a DeltaExtractionHistory Table in the staging database for each extraction or package execution. Then in the next run, I need to pick the coulmns that have a greater datetime, than the last extracted MAX(ModifiedDate) in the DeltaExtractionHistory table for delta processing.
The OLEDB source has the following SQL statement:
SELECT * FROM [dbo].[MyTable] WHERE [ModifiedDate] > ?
with the following parameter:
Parameter 0 : dtLastModifiedDate
dtLastModifiedDate is an SSIS variable of type Datetime. I read the value of MAX(ModifiedDate) in this variable in a SQLExecuteTask prior to running the data flow task that contains the OLEDB source task.
The problem is - instead of extracting greater datetime, it also extracts the MAX(Modifieddate), which was already extracted in the last run. In other words instead of functioning as [ModifiedDate] > ?, it is functioning as [ModifiedDate] >= ?
because of this, the primary key gets violated on the target table. I think this is happening because the date is stored as "2007-03-01 11:56:34.550" in the table, but SSIS variable shows it in the data viewer as "01/03/2007 11:56:34 PM". It truncates the milliseconds and picks the same date again, because it thinks milliseconds .550 is greater than 000. But I am not 100% sure, may be it is only showing this in the dataviewer, but it contains the ms part as well. I tried using
SELECT * FROM [dbo].[MyTable] WHERE [ModifiedDate] > ? AND DATEPART(ms, MODIFIEDDATE) > DATEPART(ms, ?)
But it fails with an error that the provider was not able to parse the SQL and try storing the statement in the SQL variable. I could not succeed in that - the statement never parses with the parameters.
Can someone please help in this.
Thanks
View 8 Replies
ADVERTISEMENT
Jun 11, 2015
I have created one variable name migration_start datetime which give me default format of 6/11/2015 1:26 AM...But I expecting to get in 2015-06-11 01:26:22.813 format.I have used below expression betting getting issue with that
(DT_STR, 4, 1252) DATEPART("yyyy" , @[User::migration_start]) + "-" + RIGHT("0" +
(DT_STR, 2, 1252) DATEPART("mm" , @[User::migration_start]), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , @[User::migration_start]), 2)
View 5 Replies
View Related
Oct 25, 2006
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
View 1 Replies
View Related
Mar 6, 2008
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
View 4 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Aug 29, 2001
How to pass datetime as a variable to an sp? Can any one give the syntax??thanks.
View 1 Replies
View Related
May 12, 2004
HI,
I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.
DECLARE @DATE NVARCHAR(100)
SET @DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @DATE)
WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.
BUT,
WHEN I RUN A VARIABLE CONTAINING DATETIME,
DECLARE @DATE DATETIME
SET @DATE = GETDATE()
EXEC ('SELECT ' + @DATE)
IT GIVES ME AN ERROR :-
"Line 1: Incorrect syntax near '12'. "
IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.
View 7 Replies
View Related
Jan 18, 2008
Hello,
I have a SP that recevies a date value for a users date of birth called "dob".
However when passing it into the class which contains the Stored procedure it gives an error.
Below is my code
please advice
Thanks
Ehi
1
2 command.Parameters.Add(new SqlParameter("@usernames", SqlDbType.Int, 0, "RegionID"));
3 command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20, "username"));
4 command.Parameters.Add(new SqlParameter("@First_Name", SqlDbType.VarChar, 50, "First_Name"));
5 command.Parameters.Add(new SqlParameter("@Last_Name", SqlDbType.VarChar, 50, "Last_Name"));
6 command.Parameters.Add(new SqlParameter("@dob", SqlDbType.Date, 50, "dob"));
7
8 command.Parameters[0].Value = 4;
9 command.Parameters[1].Value = "username";
10 command.Parameters[2].Value = "First_Name";
11 command.Parameters[3].Value = "Last_Name";
12 command.Parameters[4].Value = DateTime.Parse(dob.Text);
HERE IS THE ERROR MESSAGE
Compiler Error Message: CS0103: The name 'dob' does not exist in the current context
Source Error:
Line 40: command.Parameters[2].Value = "First_Name";
Line 41: command.Parameters[3].Value = "Last_Name";
Line 42: command.Parameters[4].Value = DateTime.Parse(dob.Text);
Line 43:
Line 44: int i = command.ExecuteNonQuery();
Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 42
View 7 Replies
View Related
Aug 1, 2007
Hello,
I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.
Any ideas?
Thank you for your help!
cdun2
View 4 Replies
View Related
Nov 16, 2007
I was not able to find the solution myself so here I am to ask you.
I'm using an Ole DB Source with the SQL command from variable.
My variable expression needs to look like this:
select * from dbo.fx_function (SomeDateVariable)
My sql server fx_function recieves as a parameter datetime, my variable SomeDateVariable is datetime type.
I tried all different types of cast on @User :: SomeDateVariable but I either can't evaluate the expression or I have errors trying to execute the package.
Any ideas how the expression should look like?
I'll keep trying...
View 8 Replies
View Related
Feb 13, 2007
How can I return a datetime type variable from a stored procedure in SQL Server to C# code?
View 4 Replies
View Related
Mar 30, 2004
CAST a variable into a datetime object
I need to do a CAST(@variable_name as datetime)
this won't work because @variable_name has the following format
'dd/mm/yy hh:mi:ss:mmmAM'
like how do i specify a style for it.
please help..
James : (
View 4 Replies
View Related
Jul 19, 2007
I am having the wrost trouble with this today for some dumb reason...
Please don't suggest any alternates; this is just a quick example, full code is more elaberate.
Today is 07/19/2007
Declare @StartDate DateTime
@StartDate = CONVERT(VARCHAR(10), Month(GetDate()) & "/22/" & Year(GetDate()), 101)
So I want @StartDate = '07/22/2007'
What I need is CDate
View 6 Replies
View Related
Sep 13, 2006
two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff
View 5 Replies
View Related
Feb 5, 2004
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.
View 5 Replies
View Related
May 7, 2007
HI
can i assign a string column to a date column
string column contain date data.
UPDATE TABLE ENT SET EXPIRE_DATE = PREVIOUS_TRN_VALUE
where EXPIRE_DATE is a datetime column
PREVIOUS_TRN_VALUE is a string column having the value '2007-05-07'
thanks
View 2 Replies
View Related
Sep 12, 2006
I have a package variable that is a datetime... how am I able to set the time of that variable? In visual studio on the variables screen if I click on value it brings up a calendar control - I can't seem to edit the time portion of the variable.
When I first click open the variables window it will say 9/1/2006 12:00:00 AM - but as soon as I click on the value box the 12:00:00 AM part will disappear and I can't edit it. I've tried on someone elses PC as well to make sure there isn't something wrong with my visual studio
Ideas?
View 4 Replies
View Related
Aug 29, 2007
Hello,
I have a package variable called LatestTableFileDate that is typed to DateTime. I have an Execute SQL Task that executes the following SQL statement against an MS Access 2000 database table through the OLEDB Jet provider;
SELECT MAX(FileDate) AS MaxFileDate
FROM CollectionData_Access
The statement parses fine. At the moment, the returned value is NULL. I have 'Result Set' set to 'Single Row'. The FileDate column in the Access table is 'Date/Time', and the Format is 'Short Date'.
In the result set properties, I attempt to set the result to the LatestTableFileDate user variable. When I run the task, the task fails, and the following appears on the 'Progress' tab;
[Execute SQL Task] Error: An error occurred while assigning a value to variable "LatestTableFileDate": "Unsupported data type on result set binding 0.".
I searched the forum for this problem, and didn't find anything. Do I need to convert the date in MS Access to a string and set the package variable to a string type, or is there some other way to handle this?
Thank you for your help!
cdun2
View 16 Replies
View Related
May 5, 2006
Since I've installed SP1 the minimum value for a package variable has changed.
The former version used 12/30/1899 as the minimum value that can be entered.
Now the min value has changed to something about 1960.
During package execution I'd like to distinguish between two states:
1. The Variable has not yet been written to.
2. Any of my executable has written to it.
At the moment I check the value of it to be the minimum value (see above) or not.
So if I develop packages under SP1 I get into trouble.
Any way out of it?
Thanks
Fridtjof
[EDIT] The mindate in C# differs from both date values mentioned above! Consistency? Hum![/EDIT]
View 4 Replies
View Related
Aug 12, 2006
I am getting wrong output when assigning a datepart function to a variable. I should get 2006 but instead I get an output 1905.
Below is the code and output. Any help will be greatly appreciated. Thanks
DECLARE @FiscalStartCurrYear datetime
SET @FiscalStartCurrentYear = DATEPART(year, GETDATE())
select @FiscalStartCurrYear
Output
-----------
1905-06-30 00:00:00.0000
View 5 Replies
View Related
Apr 28, 2007
In one step of an SSIS package, i create an outgoing XLS filename based on the current datetime setting, a la:
"myFileName_" + (DT_WSTR,4)YEAR( getdate()) + RIGHT("0" + (DT_WSTR,2)MONTH( getdate()),2)+
RIGHT("0" + (DT_WSTR,2)DAY( getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mi", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss", getdate()),2) +".xls"
which provides the format as myFileName_yyyymmddhhmmss.xls.
This value is then assigned to a variable, user::myFilenameDateTime.
This variable is referred to in various steps which need the full pathname or filename.
I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.
So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).
The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.
so it appears that these variables are calculated realtime whenever encountered.
1) Am I misunderstanding something or misusing the variable assignment?
2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.
3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.
thx/spirits,
seth j hersh
View 3 Replies
View Related
Oct 4, 2006
Please help!
I am designing an SSIS package and need to change the value of a DateTime global variable with the value returned from an Execute SQL task. The queary is as follows:
Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'
The error message is as follows:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at VersionDate_Set, Execute SQL Task: Executing the query "Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'" failed with the following error: "The type of the value being assigned to variable "User::VersionDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: VersionDate_Set
SSIS package "Package.dtsx" finished: Success.
The variable is set to data type DateTime, but the value will not change. Any ideas?
View 1 Replies
View Related
Apr 28, 2008
I'm looking at http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx and http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx trying to understand where we are at using datetime overrides in ssis from the command line. One of these articles suggests something has been fixed in ssis sp1, what part of this issue was fixed?
Can you override (from the command line) a user var in ssis whose ValueType property is datetime? Or do some of the older articles still apply, ie the user var must be string so that ssis wont create a separate user var with the same name etc etc? And if it is string, does some kind of conversion need to be done to compare it against datetime cols in t-sql inside the pkg?
I already know that the syntax is
dtexec /F Package.dtsx /SET Package.Variables[User::Var].Properties[Value];2007-01-02.
If this has been fixed, was it fixed in service pack 1, 2?
View 1 Replies
View Related
Jun 12, 2006
Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
View 3 Replies
View Related
Jun 12, 2015
I am using Execute sql task in my SSIS package, and I am trying to make the following query:
<o:p></o:p>
Select max(sqlid) from archive.dbo.Archivebbxfbhdr
where timein <= ?<o:p></o:p>
Where ? is my input parameter variable migration_start which is a datetime.<o:p></o:p>
My issue is that variable name migration_start which give me default format of 6/11/2015 1:26 AM
But I expecting to get in 2015-06-11 01:26:22.813 format.<o:p></o:p>
How I can I change the datetime format of my variable to be (yyyy/mm/dd)hh:mm:ss)?<o:p></o:p>
View 3 Replies
View Related
Nov 1, 2007
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
View 2 Replies
View Related
May 13, 2008
Being a newbie to SSIS I'm not sure of the most efficient method of converting a DateTime object to a String.
I'm from a C# background where this would be easy using DateTime.ToString("YYYYMMdd"). I want to use the date in a file name so don't require most of the parts.
I'm sure I could do this using a script task to produce a file name for each row of data in my table and add that filename to the dataset but it seem like overkill to do something that should be simple. Also as I'm supposed to be getting to grips with SSIS I shouldn't keep running back to what I know.
My current approach is to derive a column and build up an expression to convert the date into a string. The only problem being that it doesn't work.
The expression I'm working with is:
(DT_WSTR, 50)([OrgName] ) + "_" + (DT_WSTR, 50)( [PayrollName] ) + (DT_WSTR, 4)(YEAR( [ProcessedDate] )) + (DT_WSTR, 2)(MONTH( [ProcessedDate] )) + (DT_WSTR, 2)(DAY( [ProcessedDate] )) ".txt"
Can anyone see where I'm going wrong?
All comments greatly received.
Cheers
Ben
View 8 Replies
View Related
Dec 7, 2014
I have define two variable of Datetime type @Sdate and @Edate.
1. @Sdate = DATEADD ("DD", -5, GETDATE())
2. @Edate = GETDATE()
3. Using Forloopcontainer for pulling the data into batches
( @sdate = dateadd ( "HH" , 1, @sdate))
Now since i am using getdate() to define @Sdate. my variable gets data as ( 2014/12/08 11:43:00AM)
Converting GETDATE( 2014/12/08 11:43:00AM) to only date 2014/12/08 00:00:00AM.
I tired using DT_WSTR which works fine (converting datetime int String).
Problem occurs when i am going through For Loop container . Since i have used assignment of @Sdate is says its not allowed.
View 9 Replies
View Related
Aug 19, 2015
I'm having a hard time trying to transform a date that comes in for import in this format: 2015-04-08T18:00:48.000Z to DT_DBTIMESTAMP, I get potential loss of data error. (I've tried other SSIS data types.)
I had never seen this type of date before so I'm at a loss.
View 2 Replies
View Related
Jan 18, 2006
Newbie question. I'm new to using variables in SSIS. Are there any good examples around of how to return an @@identity value from a procedure and then use it globally within an SSIS package for multiple "execute SQL" tasks? I've gone though the documentation but don't find it to be very complete.
GC
View 1 Replies
View Related
Mar 10, 2008
hi,
I have one variable at package level in ssis.
I want to use this in sql statement of execute sql task.
but i m not able to find how to do this.
my sql statement is
select * from Var
where Var is variable at package level which is string.
please help me
Thanks,
Chetan S. Raut.
View 7 Replies
View Related
Mar 24, 2008
I need to be able to, at run time, set the location of a DataSource in an SSIS package.
I've searched and searched and beat my head against the wall till I'm bloody but I can't figure this out.
Can anyone please point me in the right direction?
View 4 Replies
View Related
Jan 12, 2012
I am using an oledb source. the query is coming from a variable. The database to which oledb source is connected is Oracle.Mt variable contains the following query:
"SELECT REQUEST_ID FROM COMPLIANCE_REQUEST
where LOAD_TMSTP between (select max(END_TMSTP) FROM BATCH_JOB_LOG) and
TO_DATE("'+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System:tartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System:tartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System:tartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System:tartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System:tartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System:tartTime] ), 2) +'",'DD-MM-YY HH24.MI.SS')"
I am getting error as :
Error at Data Flow Task [OLE DB Source 2 [2177]]: No column information was returned by the SQL command.
Error at Data Flow Task [OLE DB Source 2 [2177]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".
Error at Data Flow Task [OLE DB Source 2 [2177]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
View 6 Replies
View Related
