Importing Table/Column Comments for a Data Dictionary

Hello,
I need to create a Data Dictionary with the following contents:
Table Name
Column Name
Data Type
Comments for each Table
Comment for each column for each table
The first three are easy by using information_schema.columns. However,
there are no comments in our database right now.
Is there an easier way to import comments to the database besides using
sp_addextendedproperty? I do have the Erwin tool at my disposal to use.
Any other third party tools might help here?
The issue here is that I have over 500 tables and over 2500 columns to
add comments for and I am looking for the easiest and fastest way to
do this.
Thanks in advance for any help.
LarryFor extended properties, you may want to take a look at AGS
SQL Server Extended Property Editor.
http://www.ag-software.com/ags_SSEPE_index.aspx
-Sue
On 26 Mar 2004 11:33:09 -0800, lschwei@.oaot.com (Larry)
wrote:

>Hello,
>I need to create a Data Dictionary with the following contents:
>Table Name
>Column Name
>Data Type
>Comments for each Table
>Comment for each column for each table
>The first three are easy by using information_schema.columns. However,
>there are no comments in our database right now.
>Is there an easier way to import comments to the database besides using
>sp_addextendedproperty? I do have the Erwin tool at my disposal to use.
>Any other third party tools might help here?
>The issue here is that I have over 500 tables and over 2500 columns to
>add comments for and I am looking for the easiest and fastest way to
>do this.
>Thanks in advance for any help.
>Larrysql

Importing structure of a table to a new table

Hi,
Can anybody help me? I want to create a new table and i want to import the structure of another table to that new table but not the data.I want to do it by executing a single sql statement.Would it be possible?
Thanks!!
JoydeepSELECT * INTO NewTable FROM OldTable WHERE 0=1

It will not bring over contraints or indexes.

To do that you need to script the table and copy them so they can be applied.

Don't know how to script? Look at the first sticky post in this forum and look for the FAQ section|||thanks a lot. :)

Importing old data into asp_tables...?

I have created an application that uses the login, create, etc login components in .net. How hard is it to convert all my old users, passwords, usertypes into the new tables. It almost looks like I have to do them by hand and created a new guid(userid), along with the same guid in the aspnet_usersinroles and aspnet_Membership. Is there a script to do this programatically?

Hi,

You can use t-sql cursor to go record by record thru your old table and inserting to asp.net tables. The difficult part is passwords. I think it would be too difficult to decode and encode password in t-sql.

So, probably first import data and then use asp.net to re-encode passwords. As far as I know there are no utilities to do it automatically.

Importing non-hidden rows from Excel?

I have a need to import only non-hidden rows from an excel spreadsheet. When I create the package in SSIS, it imports everything. Due to the use of the data on the spreadsheet, we cannot simply delete the data.

Is there a special setting in the Excel Source or Connection manager that can be set to "only import non-hidden rows"?

Also, how do I go about setting the sheet with an index instead of the actual Sheet name? The user changes the sheet name at random, but I know I only need the first two sheets on the file.

Thanks!

Matt Michuta

No, there is no setting like this. You at the mercy of the Excel OLE DB Provider here and it doesn't possess functionality like you are requesting. You will have to filter those rows out in the pipeline.

Not sure about the sheet index problem - I don't ever use the Excel provider. I suspect you can't do that either. Perhaps try the data access forum if no-one here knows: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

|||You should be able to do this with a little bit of creative vba coding in excel. You can write a macro that will copy all the data to a sheet that has a static name and link the action to an event or button. Since you can use sheet indexes and row properties in vba you can get exactly what you need, and since the sheet you're copying to has a static name you can use the excel source in SSIS to import it.

importing from access to SQL Server

can i import my Access queries into SQL Server, i've tried and all it does is create new tables with the data form the queriescan i import my Access queries into SQL Server, i've tried and all it does is create new tables with the data form the queries

I'm assuming you want the query created as a view in sql server.

Go the the SQL view and cut and paste the DML in to query analyzer...you may need to modify it some.

Post the code here so we can have a look.|||The reason you may need to modify it is because Access SQL syntax is not exactly the same as MSSQL syntax.

Importing from access

Hallo.

Is there a way to create a general dts to import tables from access mdb file?
so that i will not have to change the dts for every table i am adding to the access file?
something like foreachtable? or a way to read the tables list from the access file in the sql connection?

i am importing to sql server 2000.

thanksI'd just use a stored procedure. Have it take an argument for the linked server name for the access file (or a UNC pathname and have your procedure create the linked server too using sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp)), then use sp_table_ex (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_9q60.asp) to put the catalog into a temp table, use a cursor (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) to traverse the list of tables, and do a SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) from each of the foreign tables into a SQL Server table.

Then again, I'm lazy!

-PatP

Importing CSV file with URL using DTS?

I need to be able to create a DTS package that imports a CSV file which is loated at URL. I.E. HTTP://www.url.com/csv/thefile.xls I tried copying the URL an pasting it in the file location when in SQL wazird but I got an error message.

Please help point me to anywhere that I can find some help in doing this? If there is a better way please let me know

ThanksI would post this in the SQL Server Integration Services forum instead of this one. Also, to get a quicker response, please detail exact steps you took to hit the problem, as well as display the exact error message you got.

Importing big data with exception.

Hi, there;

I use ASP.NEP to create a SSIS package to import data from .dbf file. When I import data from a big file (216,173KB) my package throw exception:

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available.
".
The "input "OLE DB Destination Input" (71)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (71)" specifies failure on error. An error occurred on the specified object of the specified component.
The ProcessInput method on component "OLE DB Destination" (58) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "WorkThread0" has exited with error code 0xC0209029.
Thread "SourceThread0" has exited with error code 0xC0047038.

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available.
".
The "input "OLE DB Destination Input" (77)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (77)" specifies failure on error. An error occurred on the specified object of the specified component.
The ProcessInput method on component "OLE DB Destination" (64) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Thread "WorkThread0" has exited with error code 0xC0209029.
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "SourceThread0" has exited with error code 0xC0047038

Does anybody know what the problem is?

Thanks.

Sounds like the connection to your destination database timed out and was closed. Most likely the SQL Server instance severed the connection. Are you performing a large sort or an aggregate in the dataflow? You might first try going to a raw file instead of the database table and then create a second data flow that uses the raw file as the source straight into the database table.|||Thanks.

My structure is: my data source files (*.dbf file)are under another machine, some files are very big (216,173KB, 283,845KB, some are even bigger as time goes on). I want to import these data to my new SQL2005 server which is empty now. I create a table before importing data (Works now). In my source component I set " oSrcInstance.SetComponentProperty("SqlCommand", "SELECT * FROM [" + sourceTable+"]");", there is no other aggregate operation between source and destination, just simply pump all data from source to destination. I can do this with my SQL2000 DTS (same as a ASP.NEP project).
So simple as Source (*.dbf)--> Destination (SQL2005).

Thanks.
|||

Are you creating the new table through an Exec SQL task? Is it using the same connection manager as your data flow task? Trying to verify that the package is connecting successfully. Also, how long is the package running before you received this error? Is it occuring if you try a smaller file?

You might try setting the OLE DB Destination data access mode property to one of the fast load options, and specify Rows Per Batch as 10,000 or 20,000, to see if you get different results.

|||

No, I don't use ExecSQL task to create table, I just use SqlConnection to create table and then close+dispose the connection.

Actually I am runnning the package now. It looks like it is running slower and slower. I can see the row numbers increaing in the SQL2005 database.

I am using OLE DB Destination,

oDestInstace.SetComponentProperty("CommandTimeout", 0);

oDestInstace.SetComponentProperty("OpenRowset", "[dbo].[" + destinationTable+"]");

oDestInstace.SetComponentProperty("OpenRowsetVariable", null);

// oDestInstace.SetComponentProperty("SqlCommand", null);

oDestInstace.SetComponentProperty("DefaultCodePage", 1252);

oDestInstace.SetComponentProperty("AlwaysUseDefaultCodePage", false);

oDestInstace.SetComponentProperty("AccessMode", 0);

oDestInstace.SetComponentProperty("FastLoadKeepIdentity", false);

oDestInstace.SetComponentProperty("FastLoadKeepNulls", false);

// oDestInstace.SetComponentProperty("FastLoadOptions", null);

oDestInstace.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);

The exception happens after package runs for 30 minutes or more. It doesn't occur for small files.

Thanks

|||

Try setting FastLoadOptions to TABLOCK, CHECK_CONSTRAINTS, ROWS_PER_BATCH=1000

and FastLoadMaxInsertCommitSize to 1000.

|||

Yes, you are right. I did that and it works perfectly. Apart from FastLoadMaxInsertCommitSize = 0.

Thanks.

Importing Access Tables?

Hi,
I have a database in Access 2003. I plan to migrate to SQL Server. Right
now, I have install SQL Server 2005 Express Edition. I have create a new
Database under SQL Server 2005 but stuck in the place because I don't know
how to import existing Access tables to the newly create Database.
Does anyone know how to do this?
SF
Hello,
You could use SQL Server Integration services (SSIS) to copy the tables from
Access to SQL Server
Thanks
Hari
"SF" <ss@.yahoo.com> wrote in message
news:eFHNntKTHHA.4028@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a database in Access 2003. I plan to migrate to SQL Server. Right
> now, I have install SQL Server 2005 Express Edition. I have create a new
> Database under SQL Server 2005 but stuck in the place because I don't know
> how to import existing Access tables to the newly create Database.
> Does anyone know how to do this?
> SF
>
|||Another option is to use the SQL Server Migration Assistant for Access (for
migrating databases from
Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005):
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Importing Access Tables?

Hi,
I have a database in Access 2003. I plan to migrate to SQL Server. Right
now, I have install SQL Server 2005 Express Edition. I have create a new
Database under SQL Server 2005 but stuck in the place because I don't know
how to import existing Access tables to the newly create Database.
Does anyone know how to do this?
SFHello,
You could use SQL Server Integration services (SSIS) to copy the tables from
Access to SQL Server
Thanks
Hari
"SF" <ss@.yahoo.com> wrote in message
news:eFHNntKTHHA.4028@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a database in Access 2003. I plan to migrate to SQL Server. Right
> now, I have install SQL Server 2005 Express Edition. I have create a new
> Database under SQL Server 2005 but stuck in the place because I don't know
> how to import existing Access tables to the newly create Database.
> Does anyone know how to do this?
> SF
>|||Another option is to use the SQL Server Migration Assistant for Access (for
migrating databases from
Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005):
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Importing Access Tables?

Hi,
I have a database in Access 2003. I plan to migrate to SQL Server. Right
now, I have install SQL Server 2005 Express Edition. I have create a new
Database under SQL Server 2005 but stuck in the place because I don't know
how to import existing Access tables to the newly create Database.
Does anyone know how to do this?
SFHello,
You could use SQL Server Integration services (SSIS) to copy the tables from
Access to SQL Server
Thanks
Hari
"SF" <ss@.yahoo.com> wrote in message
news:eFHNntKTHHA.4028@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a database in Access 2003. I plan to migrate to SQL Server. Right
> now, I have install SQL Server 2005 Express Edition. I have create a new
> Database under SQL Server 2005 but stuck in the place because I don't know
> how to import existing Access tables to the newly create Database.
> Does anyone know how to do this?
> SF
>|||Another option is to use the SQL Server Migration Assistant for Access (for
migrating databases from
Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005):
http://www.microsoft.com/sql/soluti...ss/default.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder = fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:
> > I will like to know how to create a process that unzips a folder and
> > import all its files into SQL Server 2000/2005.
> >
> > Any suggestions will be greatly appreciated.
> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like the
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder => fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> > Hello all,
> >
> > I will like to know how to create a process that unzips a folder and
> > import all its files into SQL Server 2000/2005.
> >
> > Any suggestions will be greatly appreciated.
> >
> >
> > Thanks.
> >
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:
> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>

Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSour...b/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder =
fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here
.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:

> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like t
he
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSour...b/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder =
> fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>

Importing a SQL File from the Internet...

Hello...

I am trying to create a small SSIS Package that should do the following things

1. Fetch a file from a (Later Several) Webserver(s)

2. Parse the file so the Format is "more acceptable"

3. "Normalize" the Data from the file into 3 Tables for each row (Each row contais a "Unit" , a "Location" and a "Owner" - For each of these Items i have to check for "existance" and if they do exist i need to update IF the Values changed). Also if a row is NOT present i need to remove it from my DB...

So since I am stuck on step one... Is there a "premade" Task that will allow me to fetch this/these Files? I know that i could just write a Scriptblock to fetch thse files, but since there is a FTP Task i thought there would also be an HTTP one? Also some suggestions about the best way to "normalize" the Data would be welcome...

[edit]

I just found the HTTP Task in the wishlist ;)

Ash Sharma has described a method for doing this: http://sqljunkies.com/WebLog/ashvinis/archive/2005/05/25/15653.aspx

Parsing the data would best be done with a script task/component or a custom task/component. Donald Farmer has an example of importing a semi-structured file in his book (http://www.amazon.co.uk/exec/obidos/ASIN/1932577211/qid=1139167425/sr=8-1/ref=sr_8_xs_ap_i1_xgl/203-1655415-8887134) which you will find useful.

-Jamie