A Sql statement used for variable purposes in an expression

Question

Post reply

A Sql statement used for variable purposes in an expression

darrylgemert

SSC Veteran

Points: 237
More actions
July 29, 2010 at 6:22 am

#238286

I'm new to sql server 2008. I always worked with version 2000 uptill now, so you can understand that it is a big twist.
I have been trying to make a standard SSIs template to work with.
In my company I get files to different lociations on a FTP server. Log files are been kept of the activities concerning 1 package. We get the files and unzip them to a staging folder, send the zippedfile to backup load the unzipped information to a table and delete the unzipped information after that. Its all different information, but this part of the SSis is standard.
variable are the Filename (2010*gga* /2010*jka* etc)
locations on the FTP ( \\teller5\backup_comv_25\rtbsdump\)
logfile name (ggalog / jkalog etc)
some other
I like to, put the varible information in a table and them work with expressions to make the whole thing Dynamic.
All I will need is to update the table for the variables once and the SSis will always keep on working with that info. Fact is that I do not know the power of SQL Server2008
Now I like to show you a SQl statement that i'd like to make into an expression.
"use data
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fileCheck]') AND type in (N'U'))
DROP TABLE [dbo].[fileCheck]
GO
create table fileCheck (names varchar(100),Paths varchar(100))
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Localcom]') AND type in (N'U'))
DROP TABLE [dbo].[Localcom]
GO
create table Localcomverse (names varchar(100),Paths varchar(100))
--Populate Filecheck
insert into filecheck (names)
exec xp_cmdshell 'dir \\telsur25\backup_comv_25\rtbsdump\" + @[User::wildcard] + " /on /b' "
This is only part of the SQl I like to use .
As you can see a varialble here is already @[User::wildcard]
but the error I get is :
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
The expression contains unrecognized token "on". If "on" is a variable, it should be expressed as "@on". The specified token is not valid. If the token is intended to be a variable name, it should be prefixed with the @ symbol.
Could you guys please help? I'm wasting time trying to adjust.
Is there a nother way I could go about this?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

darrylgemert SSC Veteran Points: 237 More actions · Answer 1

please help don't just vieuw the topic. Let see some input, pleaaaase

darrylgemert SSC Veteran Points: 237 More actions · Answer 2

ok so now I removed the /on part and the script looks like this

"use data

go

---als table met filenames van backup25 bestaat veeg het uit en maak het weer

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fileCheck]') AND type in (N'U'))

DROP TABLE [dbo].[fileCheck]

GO

create table fileCheck (names varchar(100),Paths varchar(100))

go

--als table met filenames van locale G: bestaat veeg het uit en maak het weer

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Localcomverse]') AND type in (N'U'))

DROP TABLE [dbo].[Localcomverse]

GO

create table Localcomverse (names varchar(100),Paths varchar(100))

--Populate Filecheck

insert into filecheck (names)

exec xp_cmdshell 'dir \\telsur25\backup_comv_25\rtbsdump\" + @[User::Zoekconditie] +"

after evaluated it looks like this:

use data

go

---als table met filenames van backup25 bestaat veeg het uit en maak het weer

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fileCheck]') AND type in (N'U'))

DROP TABLE [dbo].[fileCheck]

GO

create table fileCheck (names varchar(100),Paths varchar(100))

go

--als table met filenames van locale G: bestaat veeg het uit en maak het weer

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Localcomverse]') AND type in (N'U'))

DROP TABLE [dbo].[Localcomverse]

GO

create table Localcomverse (names varchar(100),Paths varchar(100))

--Populate Filecheck

insert into filecheck (names)

exec xp_cmdshell 'dir \telsur25ackup_comv_25

tbsdump" + @[User::Zoekconditie] +

@[User::Zoekconditie] is not chaging into the value

Please help me This is a cry for help....

stephane.siao Grasshopper Points: 24 More actions · Answer 3

Have you thought about using a stored procedure and passing the variable to it?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

stephane.siao (3/29/2011)
Have you thought about using a stored procedure and passing the variable to it?

9 month old topic.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA