Flat File Source -> DB table overwrite in T-SQL 2008R2

Question

Flat File Source -> DB table overwrite in T-SQL 2008R2

Viewing 13 posts - 16 through 27 (of 27 total)

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

Ryan1 Ten Centuries Points: 1019 More actions · Answer 1

Nice, we are making headway.

I am now receiving this error when debugging:

[Execute SQL Task] Error: Executing the query "IF EXISTS (SELECT * FROM sys.objects WHERE object_..." failed with the following error: "Incorrect syntax near 'Field1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

-I don't have the variable stated anywhere else

-Tried deleting the middle Execute SQL Task

-Checkpoints are disabled

-Attachment 5 is the Execute SQL Task Properties

-Attachment 6 is the Package properties

Thanks.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

You don't need to define or map the variable in the component, the reference to it in the expression handles all that. You need to find out what it is building as the output of the expression. go into the express designer and click the evaluate and see what it builds, make sure it is building a valid statement. I can't tell you much more. I have provided all the basics to get you there, now you have to make sure what it creates will actually run.

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 3

It definitely builds, I must have the wrong code somewhere though. Thank you for your help, updates will continue.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

In the message with all my screen prints look at the very last one, You need to see what is in Evaluated Value, THAT is what is going to be run.

CEWII

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

On the other hand, you could use xp_DirTree to find the file names and use BULK INSERT to load the file into a table using only 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)

Ryan1 Ten Centuries Points: 1019 More actions · Answer 6

Thank you for your comment Jeff, I will definitely be trying that next.

For anyone else who is attempting this, here is how I solved what I was doing wrong. I entered the following code into the first Execute SQL task (" " is included in the actual code):

"IF OBJECT_ID('" + @[User::DestinationTable] + "', 'U') IS NOT NULL

DROP TABLE "+ @[User::DestinationTable] +";

CREATE TABLE "+ @[User::DestinationTable] +"

(

Field1 INT NOT NULL,

Field2 INT NOT NULL );

GO

"

This got it to check if the variable I had set was a table, to drop it if it was, and recreate a new table with two columns: Field1, and Field2.

Does anyone know if there is a proper way to copy all of the column headers before dropping the table by means of SQL code? This way I could create a new table with all of the same fields automatically. I know I could right click in 'Microsoft SQL Management Studio', but I am trying to optimize this.

-Also note I am using a flat file source which does not have the correct column headers, the specific column headers exist only in the tables I am trying to overwrite.

Thank you.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

You could do a SELECT * INTO dbo.NEwTableName FROM dbo.OldTableName WHERE 1=0

BUT I don't recommend it. If that table gets modified for any reason you will now carry that modification forward. I MUCH prefer the package to know the table structure and create the table that matches the data flow inside that package. You KNOW the structure of the table at EVERY run and the structure that was tested with the package. If you are loading into a persistent table that is shared with other loads this does not apply but tables I use for a single load I prefer to be managed within the package..

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 8

You make a very good point. Definitely something to consider.

Ryan1 Ten Centuries Points: 1019 More actions · Answer 9

I'm not quite sure what happened, I have seemingly back slid since this was successfully working.

I am now getting a failure to lock variable. It doesn't seem like IsSourPathVariable would apply to this and checkpoints are disabled. The User::DestinationTable string does work when I use it as the variable which defines my OLEDB Destination, so this is likely my syntax for TRUNCAT TABLE with a variable? I have the syntax setup exactly as you had before.

-It doesn't seem to 'evaluate' properly in expression manager. It continuously returns as @[User::DestinationTable] instead of the table I want it to display.

I have attached screenshots that show various settings which may apply.

Thank you.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

The error tells me you have something set to expect a variable name BUT you are passing it a command.

I actually see several problems.

The Variable User::TruncateTable is incorrectly formed. Use the expressions of the variable to set it to that truncate statement, the value of the variable should be the actual statement that it calculates NOT the formula to get there.

The next problem is you are using BOTH variables and expressions on the execute task, take out the expression OR change the type to something else and let the expression feed it.

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 11

Ryan1

Ten Centuries

Points: 1019

August 2, 2013 at 10:49 am

#1638275

Great, thanks

Ryan1 Ten Centuries Points: 1019 More actions · Answer 12

Just wanted to reinstate how much help you were here. You got me off to a really good start Elliot. Thanks again.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

You are very welcome, glad I could help. SSIS has a fairly steep learning curve. Some of the other topics are even steeper.. Good luck..

CEWII