SSIS Refuses to capture a single row result set as a variable.

Question

Post reply

SSIS Refuses to capture a single row result set as a variable.

fizzleme

SSCertifiable

Points: 6906
More actions
June 19, 2016 at 5:14 pm

#327187

I am running a simple query "select FileName from testtbl where substring(FileName,18,8) =(select max(substring(FileName,18,8)) from testtbl);"
Having a hell of a time capturing the filename in a result set variable. I created a variable called FinalResult and defined it as a result set value. In the SQl task editor I defined Result set as a single row. Tried to run this and it fails on the execute sql task every time. Stumped. Help please.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.

Viewing 9 posts - 1 through 8 (of 8 total)

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

celia.moreno SSC Enthusiast Points: 172 More actions · Answer 1

celia.moreno

SSC Enthusiast

Points: 172

June 20, 2016 at 1:17 am

#1885787

Make your variable string type.

JustMarie SSCertifiable Points: 7771 More actions · Answer 2

Check your query to make sure you're really only returning one row.

Check your parameter to confirm it's set to data type varchar or nvarchar.

Check your result set to see that it's going to the right variable.

Add a breakpoint at post execution and see what the result is when running the package.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Here's a walk through on how to set it up. Hope this helps....

http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Heh... why not cut out the middle man and just use T-SQL? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fizzleme SSCertifiable Points: 6906 More actions · Answer 5

We are receiving daily backup files. I need to select the correct one from a folder and automate the restore process. It makes sense to use SSIS to identify the correct backup based on when the file was created. I will consider alternatives. Suggestions?

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.

fizzleme SSCertifiable Points: 6906 More actions · Answer 6

Since I've decided to use the file creation date and time, I am not using the code I created for this ticket. Still happy to consider alternatives to SSIS.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

fizzleme (6/20/2016)
We are receiving daily backup files. I need to select the correct one from a folder and automate the restore process. It makes sense to use SSIS to identify the correct backup based on when the file was created. I will consider alternatives. Suggestions?

Use xp_Dirtree to find the latest file and use a simple restore script to do the restore.

The syntax for what you'll need for xp_DirTree is...

EXEC xp_Dirtree 'UNC or file path here', 1, 1;

The first "1" says to only check the current level of the file path. The second "1" says to return file names as well as directories. The UNC/Filepath must either be a string literal or can be contained in the appropriate variable. I just use VARCHAR(500) for that.

Step 1 would be to create a simple table to hold the output from xp_Dirtree and then do an INSERT/EXEC using xp_Dirtree to capture the file information. It's not extensive but should be good enough for your purposes. Then, you can read through that table to find the correct file name. Hopefully, you have ISO dates as a part of the file name to make life real simple.

Then, just do a restore using a script.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fizzleme SSCertifiable Points: 6906 More actions · Answer 8

Thanks. That is helpful.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.