Float return

When the table below is created -the data selected
is different for values > 10. Does some one know why
float behaves this way ? - I am stumped
create table tempdb.dbo.TestValue (ColId int, TheValue
Float)
Insert into TestValue Values (1, 10.25)
Insert into TestValue Values (2, 10.99)
Insert into TestValue Values (3, 9.9)
Insert into TestValue Values (4, 6.59)
select * from TestValue
Results:
========
1 10.25
2 10.99
3 9.9000000000000004
4 6.5899999999999999PBrent
Read up "float and real" chapter in the BOL as well as visit on Aaron's web
site www.aspfaq.com to get more info and examples whu this datatype behaves
this way.
"PBrent" <PBrent@.discussions.microsoft.com> wrote in message
news:23d001c53f65$e8aff6a0$a401280a@.phx.gbl...
> When the table below is created -the data selected
> is different for values > 10. Does some one know why
> float behaves this way ? - I am stumped
> create table tempdb.dbo.TestValue (ColId int, TheValue
> Float)
> Insert into TestValue Values (1, 10.25)
> Insert into TestValue Values (2, 10.99)
> Insert into TestValue Values (3, 9.9)
> Insert into TestValue Values (4, 6.59)
> select * from TestValue
> Results:
> ========
> 1 10.25
> 2 10.99
> 3 9.9000000000000004
> 4 6.5899999999999999|||It's nothing special about 10. If you insert the values 16.9 into a float,
the actual floating-point value stored is just under 16.9, and you get this
Insert into TestValue Values (5, 16.9)
...
16.899999999999999
Of the numbers you inserted into the table, only 10.25 can be
stored exactly as a float. The others are stored as the nearest
representable floating-point value. When these approximations
are converted back to decimals for display, sometimes you see
the difference from the original number you tried to enter, and
sometimes you are lucky and they are rounded back to the
number you started with.
Steve Kass
Drew University
PBrent wrote:

>When the table below is created -the data selected
>is different for values > 10. Does some one know why
>float behaves this way ? - I am stumped
>create table tempdb.dbo.TestValue (ColId int, TheValue
>Float)
>Insert into TestValue Values (1, 10.25)
>Insert into TestValue Values (2, 10.99)
>Insert into TestValue Values (3, 9.9)
>Insert into TestValue Values (4, 6.59)
>select * from TestValue
>Results:
>========
>1 10.25
>2 10.99
>3 9.9000000000000004
>4 6.5899999999999999
>

flatfilesource(s) in a loop

I am trying to design a package to import the data of several .tx files into a table in sql server.

1) I created an execute task that truncates the sql server table i.e. truncate table tblContacts

2)
Placed a forrloop container with enumerator: foreach file enumerator
Folder points to the folder that holds the txt files
file: *.*
filename: fully qualified
variablemapping: User::FileName with Index 0

3)
placed a data flow task inside the forloop
this dataflow task has the following dataflow:
FlatFile Source: connection manager is pointing to one of the txt files
OLE DB Destination to place the txt data into tblContact in the database.

The question:
when the package is run, the tblContact gets populated only from the first txt file, i.e. the one which I placed in the flatfilesource connection manager.
How can I allow several files in the flatfilesource, instead of the one I have now...

Thanks

Firstly, in your For Each Loop, you could change filter from *.* to *.tx (not essential)

You need to make the connectionstring variable (ConnectionString is a property of your FlatFile connection Manager )

Click on your FlatFile Connection Manager (this is at the bottom of the BIDS screen).

Go to the properties of the CM, I mean the properties window that appears on the right of your screen.

Expand the Expressions property collection, and drill through to get the Property Expressions editor.

Choose property ConnectionString, and drill through the expression to get the Expression Builder.

Drag your variable (FileName) and drop it into the Expression box. I assume the FileName variable contains the entire file path and name.

Now it should work fine.

Flat file to table - rows out of order

Hi,

I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.

In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.

All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.

Is this normal?

Thanks

Is it normal? Well...its not not normal!

There is no concept of order in a database table. You should never assume that rows will get returned to you in the order that (you assume) they were inserted.

-Jamie

|||

That is not my understanding. For example, if you create a table, then insert a bunch of rows, one at a time, they will most definitely be returned in the order they were inserted. I have *never* seen an exception to this.

Perhaps the SSIS package is not inserting the rows in the order they are in the file?

Anyhow, I could be wrong, but this goes against my experience completely.

|||Not to sound mean or anything, but Jamie is absolutely right. There is no such thing as ordering in database land. Just because your experience "proves" otherwise, doesn't make it fact. The only way to guarantee order is to use an ORDER BY clause on your SQL statement which only controls the PRESENTATION of the data, not the way it's stored.

Do you have a situation that the records are out of order when ordering by an identity column, or are you merely using a "select * from table" statement without an ORDER BY clause?

This is perfectly normal behavior. You might want to add a sort transformation right before the destination. But still, there are no guarantees that the data will be stored "in order."|||By its definition, a database table is an unordered set of rows. While "most" of the time, a select without an ORDER BY clause will return the rows in the order they were entered, it is never guaranteed. The only way to guarantee retrieving rows in the order you want is with an ORDER BY clause on the query.|||

There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes.

Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things.

These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected.

There is no concept of order in a database table. Period.

-Jamie

|||

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

|||

sadie519590 wrote:

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

No worries. All the training courses in the world wouldn't have taught you this. The only way you learn a product is by using it. I've been using this damn thing for seven years now and I only know a fraction of it

-Jamie

Flat file produced but need line returns

My Integration Services creates a flat file using OLE DB Source and then a Flat File Destination. The flat file is created from data from my data source with is just a table with many rows.

Each row in my flat file .txt is appended onto each other, there are no line returns after each record. How can I put in a return after each row in my flat file that is outputted from the Flat File Destination component in conjunction with the properties in my Flat File Connection Manager. What am I missing here in order to ensure each row from my table creates a carriage return in my .txt flat file?

The column separator for your last column need to be {CR}{LF}

HTH,
Ovidiu

|||

In my File Connection Manager, I have {CR}{LF} for the header row delimeter but it's not putting a return at the end of each row in my flat file still.

I do not have a header row since this is coming straight from the table so I don't understand why the requirement to specify header information in the File Connection Manager at this point but it is required.

So I'm not sure what else I'm missing, my flat file still doesn't return each row, it appends each row right after another still.

|||I see what you're saying, put

{CR}{LF}

in the column delimeter for the last column's properties, is this correct? If so, I can't that property is greyed out (because it doesn't need it when specifying a flat file) when I click on the last column in the Advanced Tab of the File Connection Manager.

I just don't see where to specify the row delimeters after looking at all the tabs.

|||

Here's my flat file, still doesn't have carriage returns:

http://photopizzaz.biz/junk/flat_file.txt

|||What format are you using for your flat file? You should use RaggedRight if you want carriage returns at the end of your row.|||

The file needs to be a fixed format of 100 bytes. It needs to be formatted like this:

http://www.photopizzaz.biz/junk/final_flat_file.txt

It has to have line carriages just like this one.

|||Ranjeeta , you just made my day, thank you! it was so obvious but I had no clue!|||I had trouble figuring that out too and found the ragged-right solution on my own. But I have a different problem now. I specified a multi-line header in the flat-file destination component, but the text file produced only uses the line-endings of the header when viewed in WordPad or some other advanced text editor.

Notepad only shows little boxes and this make the header useless.

How do I get a real line-ending into the file at the end of each of my header lines so that notepad will display it properly?

Thanks!|||Don't use notepad. Try VIM, or wordpad, or TextPad or something like that. Notepad is a "dumb" editor and can't handle too many special characters.|||That particular solution is already in place. Some of my users are stubborn. You know how that goes. Notepad is their friend... In any case, there are a large number of less savvy software packages out there that will have the same trouble as notepad, and it would be nice for everyone using SSIS to be able to produce flat files that they can consume.

TextPad is what I generally use. Wordpad works too.

Thanks though.|||Right, but Notepad only processes CR-LF line terminations. If a line terminates with LF, it won't display correctly in Notepad. You can't do anything about that, unless you issue a CR-LF.

Flat file produced but need line returns

My Integration Services creates a flat file using OLE DB Source and then a Flat File Destination. The flat file is created from data from my data source with is just a table with many rows.

Each row in my flat file .txt is appended onto each other, there are no line returns after each record. How can I put in a return after each row in my flat file that is outputted from the Flat File Destination component in conjunction with the properties in my Flat File Connection Manager. What am I missing here in order to ensure each row from my table creates a carriage return in my .txt flat file?

The column separator for your last column need to be {CR}{LF}

HTH,
Ovidiu

|||

In my File Connection Manager, I have {CR}{LF} for the header row delimeter but it's not putting a return at the end of each row in my flat file still.

I do not have a header row since this is coming straight from the table so I don't understand why the requirement to specify header information in the File Connection Manager at this point but it is required.

So I'm not sure what else I'm missing, my flat file still doesn't return each row, it appends each row right after another still.

|||I see what you're saying, put

{CR}{LF}

in the column delimeter for the last column's properties, is this correct? If so, I can't that property is greyed out (because it doesn't need it when specifying a flat file) when I click on the last column in the Advanced Tab of the File Connection Manager.

I just don't see where to specify the row delimeters after looking at all the tabs.

|||

Here's my flat file, still doesn't have carriage returns:

http://photopizzaz.biz/junk/flat_file.txt

|||What format are you using for your flat file? You should use RaggedRight if you want carriage returns at the end of your row.|||

The file needs to be a fixed format of 100 bytes. It needs to be formatted like this:

http://www.photopizzaz.biz/junk/final_flat_file.txt

It has to have line carriages just like this one.

|||Ranjeeta , you just made my day, thank you! it was so obvious but I had no clue!|||I had trouble figuring that out too and found the ragged-right solution on my own. But I have a different problem now. I specified a multi-line header in the flat-file destination component, but the text file produced only uses the line-endings of the header when viewed in WordPad or some other advanced text editor.

Notepad only shows little boxes and this make the header useless.

How do I get a real line-ending into the file at the end of each of my header lines so that notepad will display it properly?

Thanks!|||Don't use notepad. Try VIM, or wordpad, or TextPad or something like that. Notepad is a "dumb" editor and can't handle too many special characters.|||That particular solution is already in place. Some of my users are stubborn. You know how that goes. Notepad is their friend... In any case, there are a large number of less savvy software packages out there that will have the same trouble as notepad, and it would be nice for everyone using SSIS to be able to produce flat files that they can consume.

TextPad is what I generally use. Wordpad works too.

Thanks though.|||Right, but Notepad only processes CR-LF line terminations. If a line terminates with LF, it won't display correctly in Notepad. You can't do anything about that, unless you issue a CR-LF.

Flat file processing dilemma

Hello,

How do I import data from a text file into columns (DB_table) that I have created in SQL server 2005?

Data in text file does not have any commas, semicolons, or spaces in between them.

Each instance in the text file consists of 109 characters (real numbers).

I also know that first 4 numbers go in particular field, next 4 go in another field, and next 6 are particular field, and so on.

I also have created columns in a table respectively.

Ravi,

have you tried using Fixed Width (or Ragged Right if you have row delimiters) format to process your files?

|||Use a fixed width file format and your problem is solved.

Flat file destination could not be loaded

Hi,

I have created a package and when i was trying to configure a flat file destination, i am getting the following error:

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Please advise.

Thanks & Regards,

Deepak

Was it working before? If yes, what has changed in the meantime?

Thanks,

Bob

Flat file destination could not be loaded

Hi,

I have created a package and when i was trying to configure a flat file destination, i am getting the following error:

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Please advise.

Thanks & Regards,

Deepak

Was it working before? If yes, what has changed in the meantime?

Thanks,

Bob

Flat File created but with too many spaces

My Flat File connection manager is creating a flat file that has way too many spaces between my fields. I am exporting columns from a table which in the end should only produce a fixed length flat file of 100 bytes but there are a ton of spaces between the columns for some reason.

http://www.photopizzaz.biz/junk/4.jpg

http://www.photopizzaz.biz/junk/7.jpg

You're using fixed width and likely the default width, which is 50 characters. You might want to switch to delimited or use a smaller fixed width.|||thanks much, found it.

FK. Can I do this?

Hello,

I have 3 tables:
[A] > Aid (PK)
[B] > BId (PK)
[C] > CId (PK), TargetId (FK)

TargetId should be related to both Aid and Bid.
Records created in C can be related to records in A or in B and TargetId can be either a Aid or Bid.

Can and/or should I do this?

Thanks,
Miguel

Hey,

You can relate TargetID to both A and B, but that means that that value has to reside in both tables, or you will get a constraint exception. That means that TargetID cannot be "either a Aid or Bid", but has to be both, if you set it up as a FK.

If that is the case, then sure, it's better to add constraints that are valid than to not have them for integrity sake.

|||

Although you cannot have an FK that say either A or B, you could build a trigger that checks the existance of id in either A or B prior to inserting the value in the referring table.

Or you could perhaps build a CHECK constraint. Dunno if it is possible to do a SELECT (from A and B) in a CHECK constraint though.

|||

Hi,

This seems really strange. I will try to explain it by using the real project I am working on:

I have 3 tables: Posts, Events and Files.
Each post, event and file can be a associated to one or many tags.

My idea was to create only one Tags table.
Note that each tag can have various associations.
It can be associate to various posts, events and files simultaneous.

My idea was to create a Tags table as follows:
[Tags] > TagId (PK), PostId (FK), EventId (FK), FileId (FK).

- Will I have problems with my Transact SQL queries?
- Will I have problems with .NET 3.5 LINQ?

The other 2 options I see are:

1. Having only one FK in table Tags, i.e. TargetId, which could be
associated with PostId, EventId or FileId ...
This does seem right to me. I feel I will have problems later on.

2. Have 3 Tags tables: for posts, for Events and for Files.
I would like to avoid having 3 tables but ...

I need to extend my decision to categories, ratings, etc.
So having 3 Tags tables, 3 Categories tables, 3 Ratings tables does not seem a good idea.

Could, someone, please advice me on this?

Thanks,
Miguel

|||

Hey,

I would have one tags table, but not have any foreign key. Because the tag could be in any one of those three tables, but not all of them, I wouldn't do that personally. That requires some extra care when you are dealing with the data, in ensuring that if you remove anything, you ensure that the tag isn't being used anywhere else.

|||

I am going for this:

Posts (PostId PK)
Files (FileId PK)

PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)

Tags (TagId PK, TagName)

I think it is the best option. I hope. :-)

Thanks,

Miguel

|||

Yeah, actually that would be better That does make some more sense than not having a FK.

Fixed Width Text Report

I'm trying to create a fixed width text report. I've created a function that
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary

first SQL conn

Hi forum, thought about time I updated from OleDB to SqlClient.

My remote host company supports MS SQL Server 2005, I have created a testsql.mdf, now I want to connect to that database,( im used to writing Access conn like below and have updated my OleDB code to Sql ).

<%@. Import Namespace="System.Data"%>
<%@. Import Namespace="System.Data.OleDb" %>

strAccessConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\\inetpub\\vhosts\\blablabla\\httpdoc\\App_Data\\testdb1.mdb";

How do I write the SQL connection string?

<%@. Import Namespace="System.Data"%>
<%@. Import Namespace="System.Data.SqlClient" %>

strSqlConn =?? this bit ???

This is the remote host path

LocalSqlServer

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

This my first SQL data application so need some help thanks Paul

Check outhttp://www.connectionstrings.com/

first analysis service project+deployment failure

I created small analysis service project as according to the MSDN.
and when i test the database connection it says its okay.
but when i try to deploy , it says

'The project could not be deployed to the '(localhost)\SQLEXPRESS' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.'.

what kind of security or other configurations i should do.
what went wrong?

i know someone might have answered this earlier also.
pl help on this.I had kinda the same problem a while ago, when I started the following thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247494&SiteID=1

Hope this'll be helpfull :-)|||

The message "'The project could not be deployed to the '(localhost)\SQLEXPRESS'" means that you are trying to deploy an AS project to a SQLServer EXPRESS edition?

Because SQL Server EXPRESS edition doesn't support Analysis Service.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

First 5 Related Records?

I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do this
?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926Try:
select
o1.F_relNum
from
Orders o1
where
o1.RE_rel_num in
(
select top 5
o2.RE_rel_num
from
Orders o2
where
o2.F_relNum = o1.F_relNum
order by
o2.RE_rel_num desc
)
order by
o1.F_relNum
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Wendy" <Wendy@.discussions.microsoft.com> wrote in message
news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do
this?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926|||That worked great! Thank You
"Tom Moreau" wrote:

> Try:
> select
> o1.F_relNum
> from
> Orders o1
> where
> o1.RE_rel_num in
> (
> select top 5
> o2.RE_rel_num
> from
> Orders o2
> where
> o2.F_relNum = o1.F_relNum
> order by
> o2.RE_rel_num desc
> )
> order by
> o1.F_relNum
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Wendy" <Wendy@.discussions.microsoft.com> wrote in message
> news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
> I am trying to determine what the first 5 related records to another recor
d
> are. I have created a table of matches where I have returned the unique ke
y
> value for each record in a one to many relationship. Preferrably, I would
> like to update the table that contains the F_rel_num with the 5 (or less)
> values for RE_rel_num in fields such as RE_rel_num1,
> RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
> RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would n
ot
> work as I need the top 5 for each F_rel_num. Does anyone know how to do
> this?
> I could easily write this in ASP or VB, but I need to be able to run this
as
> a regular SQL job, so I imagine I need to do it completely with T-SQL.
> F_relNum RE_rel_num
> 3 1633955
> 3 1353526
> 3 1137500
> 3 905264
> 3 732204
> 3 639101
> 3 488182
> 3 377705
> 3 365446
> 3 365445
> 3 313125
> 3 256899
> 3 254183
> 3 133409
> 6 214174
> 6 139273
> 6 117524
> 6 117520
> 7 1053160
> 11 1126433
> 11 857312
> 11 464240
> 13 555629
> 13 316781
> 13 302905
> 13 231447
> 14 644116
> 14 164926
>

Firing already made DTS Package -- from vb.net

Someone please help me with this.

I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section.

HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION

I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated.

Thanks in advance everyone,

RB

Try this url you may find your solution. Hope this helps.

http://www.sqldts.com

|||

I have this in my stored proc already:

Here is what I am trying to run within my stored proc:

dtsrun /F'CSI_DTSPackages/GTProPaymentExtract.dts'/N'GTProPaymentExtract'/M''

PLEASE TELL ME WHAT I'M DOING WRONG!!!

Thanks in advance everyone.

RB

|||

I gave you Darren Green's site because if you can do it in DTS he may have done it. I have found another link, it may solve your problem. Hope this helps.

http://www.vbdotnetheaven.com/Code/Jun2003/2070.asp

|||

here a stored procedure I got from the DTS site. As this fires off a DTS package on the server your path will have to be a UNC path. In my case the path is source to a file I am reading data from.

CREATE PROCEDURE spExecuteDTSPKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ConnName varchar(25) = '',
@.XLSPath varchar(255) = '', -- Path to Source File
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON

DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000), @.Connection varchar(255)
Select @.Connection = 'Connections.Item(' + @.ConnName + ').DataSource'
Select @.ret = 0

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

EXEC @.hr = sp_OASetProperty @.oPKG, @.Connection, @.XLSPath
If @.hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Failed***'
EXEC sp_OAGetErrorInfo @.oPkg
RETURN
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

RETURN @.ret
GO