June 10, 2011 at 7:19 am
Not only this query... even if I try to use...
SELECT 1
And if I wanted to pass the value 1 to variable it does not work. So I am very confident that the issue is with SSIS configuration and not SQL Server. Please let me know if you need other screenshots...
Cheers - DV
June 10, 2011 at 7:22 am
Deepak_Vad (6/10/2011)
Not only this query... even if I try to use...SELECT 1
And if I wanted to pass the value 1 to variable it does not work. So I am very confident that the issue is with SSIS configuration and not SQL Server. Please let me know if you need other screenshots...
Cheers - DV
I'm not sure I understand this. Are you responding to my last post about double-checking your variable scope? Or is this an addendum post to your DDL response?
June 10, 2011 at 7:25 am
Brandie Tarvin (6/10/2011)
Eh-hem. I think you need to go back and look at your variable scope. The picture you posted does not jive with the comment "I have a package level scope" that you made right after the post.
As long as the package is called AdjustImportOAG, I think he's OK.
DV, please will you try changing the Result Name from "0" to "MyDate"? I've a feeling that will fix it.
John
June 10, 2011 at 7:25 am
I am sorry for this confusion. I am adding to my previous post...
I had checked the variable scope and it shows that its package level scope. I had attached the screenshot for your reference.
Thanks again for trying to show me the way!!
June 10, 2011 at 7:30 am
John - Thanks again. I had tried that yesterday and this morning.. however, I had tried the same after your recommendation. The package seem to execute without any problem but it does not change the date in the variable. It still shows 30/12/1899 as value for my variable. I had also tried to create a new package with simple SELECT statement and it does not work as well. I had tried using return Integer value to variable for testing purpose and it does not return. I am hitting the brick wall!!! Please help me...
Thanks,
DV
June 10, 2011 at 7:57 am
Also I wanted to check if I am doing this right... to test the variable, I am executing the specific task (Execute SQL Task) in my case and then after successful completion then I am looking at the variable overiew window. I am assuming this is the right way to do... or do I need to execute the whole package to see the change in variable?
Sorry for being silly, but I am complete newbie with all this...
Cheers - DV
June 10, 2011 at 8:02 am
DV
I think if you look in the Variables window, you'll see the value for the variable the last time the package was loaded. If you know a bit of VB.Net then you could create a script task to display a message box with the variable value in it. Otherwise, create an Execute SQL task and write the value into a table.
John
June 10, 2011 at 8:12 am
Is the variable being manually set by any sort of config file?
June 10, 2011 at 8:19 am
John Mitchell-245523 (6/10/2011)
I think if you look in the Variables window, you'll see the value for the variable the last time the package was loaded.
That is an incorrect assumption. The Variable window will show either what is set up in a config setting or what was input as the default when the variable was created. Either way, the test is to blank out the value of the package variable, save the package, close it and re-open it. If the value is still there, then there is a configuration setting that is loading it. If not, then try running the package with the default as blank to see if it gets set.
EDIT: Rephrasing my above comment. It will only keep the "last run" value there as long as the package is open. Once the package has been closed, the value blanks back to its default.
John Mitchell-245523 (6/10/2011)
DVIf you know a bit of VB.Net then you could create a script task to display a message box with the variable value in it. Otherwise, create an Execute SQL task and write the value into a table.
This is a useful troubleshooting tip I have used many times.
1) Add a script task right after the execute task.
2) Put the variable as the ReadOnly variable in the middle tab.
3) Click open the script and add the following code:
MsgBox(Dts.Variables("vOAGDate").Value.ToString() & " is the variable value of vOAGDate.")
Save the task, debug the package. A message box will pop up with the value (if any) of your package variable.
June 10, 2011 at 8:34 am
Brandie Tarvin (6/10/2011)
John Mitchell-245523 (6/10/2011)
I think if you look in the Variables window, you'll see the value for the variable the last time the package was loaded.That is an incorrect assumption.
Not quite, although I admit I didn't explain it very well. My point was that if the value of the variable changes during execution, that won't show in the Variables window. What you'll see instead is the value the last time the package was loaded, whether that is inherited from last time the package was saved, or set using an expression or package configuration. The one exception to that is if you manually change the variable in the Variables window - then you'll see the value you change it to. But (I think) even then, the new value won't be used during runtime unless (1) the package is reloaded and (2) the value is not overwritten by an expression or package configuration.
John
June 10, 2011 at 8:37 am
Okay. I see what you mean now. You are correct.
June 10, 2011 at 8:50 am
Brandie Tarvin & John Mitchell - First thing first... I am very sorry for wasting your time. It works with script task and it shows the date. I am kicking myself for my ignorance and not letting you know this at the beginning. So the variable works with the Execute SQL Task but I am looking at the variable window which is initial load value as mentioned by both of you.
Thanks a ton for your time & patience with me... I almost gave up but you both of you are really helpful. I have a quick question.. what is the best way to trim the time from the DateTime of my variable. I assume that I need to use the expression to get this working... (I don't want to assume any more!!!! 🙂 )
Also please can you suggest a good book on SSIS? or best method to learn SSIS?
Thanks again for your help.
I had attached the screenshot for your reference.
Cheers - DV
June 10, 2011 at 8:55 am
DV
I haven't tried this myself, but you could try casting as the various date data types and see if one of those gives the date portion only. Failing that, you could cast as a char data type, and just use the 10 first characters to give you dd/mm/yyyy.
As for learning, I taught myself by trial and error, and checking forums, blogs and so on when I got stuck.
John
June 10, 2011 at 8:55 am
Deepak_Vad (6/10/2011)
Thanks a ton for your time & patience with me... I almost gave up but you both of you are really helpful. I have a quick question.. what is the best way to trim the time from the DateTime of my variable. I assume that I need to use the expression to get this working... (I don't want to assume any more!!!! 🙂 )
Glad you figured out what your issue was.
The quickest way is to do it via the Execute SQL Task.
SELECT Convert(char(10),Min(EffDate),101) from MyTable ...
--Pardon me that I don't remember your column & table name
Look up CONVERT in Books Online. The 101 means something and depending on how you want to format your result, you'll want to change that.
Also, if you convert back to datetime outside the character convert, it will add a time of midnight (lots of zeros) back to the value. So if you don't want the time at all, you need to keep it in char form and change all your package variables, etc. accordingly.
June 10, 2011 at 8:58 am
Many thanks to both of you... Any suggestion on the book and best possible approach to learn SSIS please?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply