SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 5314
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
Attachments
Package-1477191-364-1.zip (10 views, 4.00 KB)
Ryan1
Ryan1
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 130
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:BigGrinestinationTable] +" ') AND type in (N'U'))
DROP TABLE "+ @[User:BigGrinestinationTable] +" (
[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.
Attachments
Parameter Mapping.jpg (3 views, 70.00 KB)
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 5314
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
Ryan1
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 130
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
Attachments
1.jpg (4 views, 110.00 KB)
2.jpg (6 views, 186.00 KB)
3.jpg (5 views, 135.00 KB)
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 5314
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
Ryan1
Ryan1
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 130
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.
Attachments
5.jpg (5 views, 120.00 KB)
6.jpg (3 views, 248.00 KB)
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 5314
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
Ryan1
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 130
It definitely builds, I must have the wrong code somewhere though. Thank you for your help, updates will continue.
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 5314
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
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89147 Visits: 41143
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search