Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts

Thursday, March 29, 2012

filter by year

I'm using a datetime column (createddate), and passing a smallint variable
(@.year). what's the best way to filter records that fall in that specific
year (including 01/01 and 12/31 records).
Thanks!Guy
CREATE TABLE #Test
(
col DATETIME NOT NULL PRIMARY KEY
)
INSERT INTO #Test VALUES ('20000101')
INSERT INTO #Test VALUES ('20010101')
INSERT INTO #Test VALUES ('20020101')
INSERT INTO #Test VALUES ('20020102')
INSERT INTO #Test VALUES ('20020103')
INSERT INTO #Test VALUES ('20030101')
DECLARE @.y AS SMALLINT
SET @.y=2002
SELECT * FROM #Test WHERE YEAR(col)=@.y
If it does not help you please post DDL+ sample data + expected result.
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23kqBn6bEFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm using a datetime column (createddate), and passing a smallint variable
> (@.year). what's the best way to filter records that fall in that specific
> year (including 01/01 and 12/31 records).
> Thanks!
>|||Here's one method:
SELECT *
FROM YourTable
WHERE createddate >= DATEADD(YEAR,@.year-2000,'20000101')
AND createddate < DATEADD(YEAR,@.year-1999,'20000101')
David Portas
SQL Server MVP
--|||In addition to the other replies, you might want to implement a calendar
table if you do these kinds of things on a regular basis.
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23kqBn6bEFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm using a datetime column (createddate), and passing a smallint variable
> (@.year). what's the best way to filter records that fall in that specific
> year (including 01/01 and 12/31 records).
> Thanks!
>

Friday, March 23, 2012

FileWatcher Task or System.IO?

I would need to make a SSIS package which must to watch all the day long if a file come and then save into a variable that path+file.

FileWatcher task do that for me or better totally focusing this requirement in a conventional .NET Script using IO classes and all that sort of stuff?

OK, what about this here: http://www.sqlis.com/default.aspx?23

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||anyone got any sample on how to use the File Watcher Task component?

Wednesday, March 21, 2012

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg as int

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg = (@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg, '@.jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:\Data\Pics\@.jpg.jpg', Single_Blob) as 'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg asint

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg =(@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg,'@.jpg.jpg',

bulkcolumn fromOpenrowset(Bulk'D:\Data\Pics\@.jpg.jpg', Single_Blob)as'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROMOPENROWSET('MSDASQL','Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;','SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

Wednesday, March 7, 2012

File System Task Problem

I'm trying to realize a file system task that rename files from a
foreach loop container. So that means the task have a variable in the
source connection. This variable got the value (as an expression) of
"c:\\.....\\"+@.[User::ForeachloopVar].
But an error message appears when i run it.The message is


File System Task: An error occurred with the following error message:
"The given path's format is not supported."


When i don′t use the variable in the source connection it works fine.
Anyone knows what might be the problem?
Thanks.

Escape the slashes to be "\\"

BTW - you may find this blog post from Kirk Haselden useful ... http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/12/7750.aspx

Donald

|||I′m sory but that′s not the problem.Because i′ve just wrote the example whitout the slashes but they′re there. And i′ve already looked at that blog. It was helpfull to create my package. But thats the thing in his case the value of the source variable from the foreach loop appears in the value of the expression, mine doesn′t.|||

I see that you are creating a path and appending the source variable in an expression. However, the ForEach loop includes the option to return the fully qualified name of the file. Is there a reason you're not using that?

Donald

|||I′m using the fully qualified name option that's not the problem.|||

It′s working now i did some changes like using only name and extension option on the foreach an voilá.

thanks.

|||

That link was quite a nice example, but I seem to be having a bit of a novice problem. I've followed the blog virtually to the letter, even changing to the variable names used, but on the File System Task I get an error saying that ' FileSourcePath is used as a source or destination and is empty '.

I created the ForEach loop container as was described:

On the collection I have set the path (browsed to the path, so it does exist). I've specified files as '*.txt' - there will be 2 files in the directory by the time the container executes. In the variable mappings I have specified the user::FileSourcePath variable - it has an index value of 0. The variables have a scope of the entire package. I feel I've missed something obvious here, but somehow my container does not update the values for that variable - any help?

|||

Look i was having that same problem before.I did just like the blog shows and it alway fails. I don't know why because it seems to work in the blog. But i don't know if my solution was the best one but i created another variable to contain the flat file source path and and in the foreach loop i've chose the option on the collection pane on the retrieve file name -Name and extension. That means i'm olnly getting the *.txt for the variable @.[user::FileSourcePath]. And then i've put the new variable whit the @.[user::FileSourcePath] in the Connection Manager connectionstring. So then i've just wrote the following on the evaluate as expression of the user::FileDestinationPath

@.[User::FileDestinationFolder] + "\\" +(DT_WSTR, 10)(DT_DBdate)GETDATE()+ @.[User::FileDestinationPath]

and it rename the file just like i wanted and put the date before the old name.

Look i'm new at this thing to so i don't know if i've explained well. But i hope that you can use something.

|||

If you receive an error when the package starts saying that a source or destination placeholder variable is empty you can typically fix it this way: simply ensure that the default (initial) value of the variable is set to a filename.

Setting DelayedValidation on the loop may also help in your case.

Donald

Sunday, February 19, 2012

File group as variable

Hi Champs!

How can i create a table on an filegroup that comes from a variable?

Using EXECUTE?

the following does not work:

declare @.var char

set var = 'filegroup'

Create table table]

...

...

ON [@.var]

/Many thanks

Dynamic SQL is the only solution (sp_executesql or EXECUTE).