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

Question

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

Ryan1

Ten Centuries

Points: 1019
More actions
July 24, 2013 at 11:47 am

#296850

Hello SQLSC,
I am extremely new to SQL. As you can tell is my first post on the forum, I have a feeling we are going to get to know each other very well 😉
I have been tasked to use a flat file source -> overwrite a table on a 2008R2 SQL database with the data in the flat file source. I am able to utilize BIDS to create a new table in which my data can upload to no problem. I cannot however overwrite the table that it needs to go. It seems to run in bids no problem with no errors. Any detailed help would be much appreciated.
Thank you and have a nice day.

Viewing 15 posts - 1 through 15 (of 27 total)

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

Not entirely clear what the issue is..

In your process are you needing to drop the table then load it? Or do you just need to clear the table? Please go into additional detail.

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 2

The flat file data needs to replace the data in the columns of the database table.

The table in the database has column names that the flat file does not as well.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

Ok, so I read that as you need to empty the destination table.

On the Control Flow of the SSIS package add a Execute SQL Task, chose the correct connection, and put in a TRUNCATE TABLE command for the table you need to empty, connect the Exec SQL task to the Data-Flow task and you should be in good shape.

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 4

Ryan1

Ten Centuries

Points: 1019

July 24, 2013 at 12:23 pm

#1635465

Thank you so much Elliot!!

Ryan1 Ten Centuries Points: 1019 More actions · Answer 5

Is there any way to fully map each Available Input Column to each Available Output Column in the OLE DB Destination Editor without dragging each column name across, making a link?

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 6

You are very welcome, this is a VERY common design pattern, clean the receiver, fill the receiver, then run some process to do something with the data..

CEWII

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

If they have the same names they will usually automap, but if they don't then no, you have to handle the mapping.

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 8

Ryan1

Ten Centuries

Points: 1019

July 24, 2013 at 12:35 pm

#1635472

Awesome, thank you so much.

Ryan1 Ten Centuries Points: 1019 More actions · Answer 9

Are there ways for me to replace the flat source file name and database table name with variables?

-I started with the Execute SQL task in control flow, trying to use a variable for a table name in that area first.

-Trying to piece together information on how to accomplish this from different websites, but I have hit a stand still.

*listed attachments display print screens of my settings.

1.

The 'value' of my 'execute' variable is:

TRUNCATE TABLE [nevada].[dbo]@tableName;

----------

2.

My ResultSetType is set to: ResultSetType_None

-----------

3.

Resulting Error when debugging:

[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [nevada].[dbo]@tableName;" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

-Parameter Name is 0

-ResultSet is greyed out in 'edit' of Execute Sql Task

-Connection is established, package runs fine when my new variables are not in place.

Thank you.

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

You are most of the way there. I recommend though that the destination DB NOT be included in the command, set the destination database in your destination connection.

I have uploaded a package that illustrates how this can be done.

https://dl.dropboxusercontent.com/u/85082194/SSC/Package-1477191-364-1.dtsx

I have also attached it here in this post..

First I have setup these variables:

I am using expressions to build two commands, one to build the work table and the other to truncate it. The are fed from the variable DestinationTable.

They are then used in the Exec SQL Tasks as such:

We then go to the Data Flow Task, you will notice that I am using an OLEDB Source, this is just to make the demo easier.

The Source just gives 5 rows of data:

The Destination is a setup a little different:

You'll notice the Data Access Mode and the variable. Because the Destination table has an Identity for Field1 the Keep Identity is checked.

This gets us most of the way, you probably noticed the DestinationDatabase variable, that is used to set the catalog at the destination. There are several ways to do this, you can set the whole connection string at run time or a couple other ways. I'm keeping it really simple.

You'll notice the little formula symbol next to the Connection name, I have configured an expression to set the catalog (database) name.

I hope this sheds some light on the situation for you.

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 11

Thank you for your detailed response, you are a very big help as there is not a lot of useful/detailed information on this subject matter.

-I have the property expression set to "ForcedExecutionValue", Here is the code:

"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' "+ @[User::DestinationTable] +" ') AND type in (N'U'))

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

[Field1][int][ IDENTITY(1,1) NOT NULL,

[Field2][int] NOT NULL )

GO

"

Here is the error message text copied from the progress tab:

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

-My ResultSet is 'None' as yours is.

Could this possibly be my parameter mapping? Attached is what I am currently working with, I have also tried it with no parameter mapping at all.

Thank you.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 12

There is no mapping needed.

I'm having a little trouble following you. Where did you set the expression? What object? What property? What was your exact formula?

CEWII

Ryan1 Ten Centuries Points: 1019 More actions · Answer 13

I set each expression to it's appropriate Execute SQL Task, I assigned no other expressions.

Both of the properties are set to ForceExecutionValue. It didn't co-align with MSDNs definitions per

http://msdn.microsoft.com/en-us/library/ms137749.aspx

-but it was one of the few that actually allowed me to debug still.

Attachment 1 displays the way everything is setup

Attachment 2 displays the first Execution SQL Task(build table)

Attachment 3 displays the second Execution SQL Task (truncate)

I understand that Field1, and Field2 wont be written into my actual table, but I need to create some type of relation when creating a table so I figured I would just keep it.

-This is failing on the build table.

Thanks

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 14

I understand your mistake now. You can't use that property, it doesn't line up to the command to execute. SQLStatementSource is the property you need to set. With the type being direct in.

Try that.

CEWII