DTS Export format, please help

I have a table (tblstudent) with four fields, Fname, Lname, City, Zip.
I want to run a DTS export nightly that export the records out to a file.
The kick is that I would like the text file to list the fields on their own
line like:
Fname
Lname
City
Zip
Instead of across like: Fname, Lname, City, Zip.
How can I accomplish this?
Thanks for your time!
TonyHopefully this will solve your issue. write the code in Axtivex script in DT
S.
========================================
=========
'***************************************
*********************
' Visual Basic ActiveX Script
'***************************************
*********************
Function Main()
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "sqloledb"
Conn.Properties("Data Source").Value = "(local)"
Conn.Properties("Initial Catalog").Value = "Testing"
Conn.Properties("Integrated Security").Value = "SSPI"
Conn.CommandTimeout = 0
Conn.Open
'--get students record set
SqlQuery ="select * from tblStudent"
Set RsStudent = Conn.Execute(SQLQuery)
'--ourt vars
ourputString = ""
Path = "C:\test\"
'-- create output folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FolderExists(path) Then
objFSO.CreateFolder (path)
End If
'--Create Traget file
Set TargetFile = objFSO.CreateTextFile(Path &"Report.txt", True)
IF not RsStudent.eof Then
While not RsStudent.eof
ourputString=""
ourputString="Fisrt Name: " & RsStudent("FName") & vbcrlf & _
"Last Name:" & RsStudent("Lname") & vbcrlf & _
"City :" & RsStudent("City") & vbcrlf & _
"Zip :" & RsStudent("Zip") & vbcrlf & _
"----
--" & vbcrlf
TargetFile.WriteLine(ourputString)
RsStudent.moveNext
Wend
End If
Set TargetFile=Nothing
Set objFSO=Nothing
Set RsList =Nothing
Conn.close
set conn = nothing
Main = DTSTaskExecResult_Success
End Function
========================================
=========
"tony tuso" wrote:

> I have a table (tblstudent) with four fields, Fname, Lname, City, Zip.
> I want to run a DTS export nightly that export the records out to a file.
> The kick is that I would like the text file to list the fields on their ow
n
> line like:
> Fname
> Lname
> City
> Zip
> Instead of across like: Fname, Lname, City, Zip.
> How can I accomplish this?
> Thanks for your time!
> Tony
>
>

DTS Error

I need to export some table data to Excel which I normally do with Query Analyzer as a .CSV file. However, one of the fields has a ton of comma's which screws up the formatting so I'm using the DTS Import/Export Wizard. I choose the .xls file and paste my query. When it runs I get the following error: "Failed to copy 1 table(s). Double click the error..." When I click on the error it gives me an "Invalid Pointer" message. I'm not sure where to go from here so any help would be greatly appreciated.
Thanks,
Jim
I figured it out...I had "USE (Name of Database)" in my query. Once I took that line out everything worked quite well.
Jim
|||Please address future DTS (SSIS in SQL Server 2005) to the SSIS forum: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80

DTS DB2 Dates 0001-01-01

Hi,

I am trying to transfer a table from DB2 to SQLServer 2000 through a DTS package. The DB2 table contains fields with default dates of "0001-01-01". The DTS package errors out whenever it reads this date as "invalid data value". In SQLServer 2000, the date fields are of type ShortDateTime. I have searched the Internet but did not find a workable solution. Please, can anyone help me find a solution?

Older applications sometimes used dates like '9999-12-31' or '0001-01-01' to signify that the date was either NULL, invalid or not-entered. If DB2 is using that date as a valid point in time then the following would not work. However if it is a sentinal value used to represent NULL then you could transform such dates into NULL on-the-fly as you extract them from DB2; in pseudocode:

SELECT

NullIf(theDate, '0001-01-01') as theDate, <other fields>

FROM theTable

(You would need to find out the equivalent function for NullIf in DB2 parlance) This would have the effect of leaving all dates alone except for '0001-01-01' which would be translated to NULL, which would keep SSIS happy.

If you translated it to NULL then you would have to be careful that you did not break other business rules.

|||Thanks but I don't have any control over the creation of DB2 tables hence I was looking for DTS to do the trick. Can DTS handle this?|||In the source component of your DTS package, use the SELECT statement and conversion functions as detailed in my previous reply.|||

Thanks.

I am rather new to DTS and am trying to get an example of creating a package using vbscript. I have looked at Books on line but the examples are a little confusing for me right now. Do you by chance have any sample code that shows how to do something similar like this ?

Appreciate your help.

DTS Date Problem

I have a DTS package that imports from a text file into SQL 2k SP3. I am trying to insert a field (along with the imported fields) during the DTS execution that will be the current date. Does anyone know of a way to do this? I really didn't think I should have to schedule a seperate job to UPDATE each record with the current date...

Make sense?Make a datetime column in the imported table default to getdate().|||Originally posted by joejcheng
Make a datetime column in the imported table default to getdate().

hehe, thanks. That's what I was trying to do, but for some stupid reason I wasn't putting the () on the end of getdate.

Thanks

DTS data transfer and Update

My DTS package transfers data mostly codes from temp to master table. The master table has 10 description fields for each of the codes. These description fields are then populated using update statements joined with 10 description tables. However, the update process is so inefficient since a single update statement takes several hours to finish.

The second option is to populate description fields as codes are transferred from temp to master table using 10 left outer joins with description tables. But the result was unexpected. It's giving me more than twice as many records as there are in the temp table.

What would be the best (efficient) approach for this situation? Would greately appreciate any help/thoughts.

thanks.I think I would create a view, unload the vie and bcp the data into the table in native format

Do the code tables contain keys?|||a single update statement takes several hours to finish

Then you must be doing something wrong...

The master table has 10 description fields

This sounds like awful design! Fancy posting the DDL of the table in question?sql

DTS and Excel - missing fields

Hello, I'm having a problem with importing an Excel file
with DTS. When doing this I see 2 entries for each sheet
in the Excel file but one has a $. The one with the $ has
all the fields from Excel when I preview it but the one
without the $ doesn't have the last field (going to a bit
field in SQL). When I look at the transformations for the
Source on the last field it says "ignore" and the field
isn't in the list to choose from Excel. Can anybody tell
me what's going on?
Thanks,
VanFound the problem. Instead of "1"s and "0"s in Excel, I
needed to use "True"s and "False"s for bit fields.
>--Original Message--
>Hello, I'm having a problem with importing an Excel file
>with DTS. When doing this I see 2 entries for each sheet
>in the Excel file but one has a $. The one with the $
has
>all the fields from Excel when I preview it but the one
>without the $ doesn't have the last field (going to a bit
>field in SQL). When I look at the transformations for
the
>Source on the last field it says "ignore" and the field
>isn't in the list to choose from Excel. Can anybody tell
>me what's going on?
>Thanks,
>Van
>.
>

dts and bit fields

sql2k sp2
If I dts data from a table to a .txt file all of the data
in the bit field gets turned into a "true" or "false"
value. Is there a way to turn this setting off?
TIA, ChrisDid not test, but
turn the column to allow null, then when dts, do not load the column.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris|||DTS a query or view rather than the base table.
In the query or view, use CONVERT or CAST to cast the bit column to integer
(or what you want).
James Hokes
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris

DTS + PK

Hello,
I have a table with a primary key.
I also have a CSV. The table has all the same fields as the CSV plus the
PKID.
I want to create a DTS that will import it. It seems to be putting in NULLS
for the PKID though.
I created the table and checked it. It is a PK and Identity is set to 1 and
increment by 1.
How to fix this?When you are in the DTS wizard, click the transform tab and see if all
columns are properly matched.
"Won Lee" <noemail> wrote in message
news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a primary key.
> I also have a CSV. The table has all the same fields as the CSV plus the
> PKID.
> I want to create a DTS that will import it. It seems to be putting in
NULLS
> for the PKID though.
> I created the table and checked it. It is a PK and Identity is set to 1
and
> increment by 1.
> How to fix this?
>|||All the columns are matched. I remember not having a problem with this at
all before. I have several other DTS packages that look similar. All the
cloumns are matched except for the pkID.
SO the table in SQL server has 7 fields. The CSV has 6 fields seperated by
commas.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> When you are in the DTS wizard, click the transform tab and see if all
> columns are properly matched.
>
> "Won Lee" <noemail> wrote in message
> news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I have a table with a primary key.
> > I also have a CSV. The table has all the same fields as the CSV plus
the
> > PKID.
> > I want to create a DTS that will import it. It seems to be putting in
> NULLS
> > for the PKID though.
> > I created the table and checked it. It is a PK and Identity is set to 1
> and
> > increment by 1.
> >
> > How to fix this?
> >
> >
>|||Very strange. However, when you uncheck Allow Identity Insert, it looks
working -- though I thought it should be the other way around.
"Won Lee" <noemail> wrote in message
news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> All the columns are matched. I remember not having a problem with this at
> all before. I have several other DTS packages that look similar. All the
> cloumns are matched except for the pkID.
> SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
by
> commas.
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > When you are in the DTS wizard, click the transform tab and see if all
> > columns are properly matched.
> >
> >
> > "Won Lee" <noemail> wrote in message
> > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I have a table with a primary key.
> > > I also have a CSV. The table has all the same fields as the CSV plus
> the
> > > PKID.
> > > I want to create a DTS that will import it. It seems to be putting in
> > NULLS
> > > for the PKID though.
> > > I created the table and checked it. It is a PK and Identity is set to
1
> > and
> > > increment by 1.
> > >
> > > How to fix this?
> > >
> > >
> >
> >
>|||Yes. That was the case. I delselctged enable indentity insert and it
worked fine.
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:eGaoByWUDHA.2316@.TK2MSFTNGP09.phx.gbl...
> Very strange. However, when you uncheck Allow Identity Insert, it looks
> working -- though I thought it should be the other way around.
>
> "Won Lee" <noemail> wrote in message
> news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> > All the columns are matched. I remember not having a problem with this
at
> > all before. I have several other DTS packages that look similar. All
the
> > cloumns are matched except for the pkID.
> >
> > SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
> by
> > commas.
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > > When you are in the DTS wizard, click the transform tab and see if all
> > > columns are properly matched.
> > >
> > >
> > > "Won Lee" <noemail> wrote in message
> > > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > I have a table with a primary key.
> > > > I also have a CSV. The table has all the same fields as the CSV
plus
> > the
> > > > PKID.
> > > > I want to create a DTS that will import it. It seems to be putting
in
> > > NULLS
> > > > for the PKID though.
> > > > I created the table and checked it. It is a PK and Identity is set
to
> 1
> > > and
> > > > increment by 1.
> > > >
> > > > How to fix this?
> > > >
> > > >
> > >
> > >
> >
> >
>

DTS - Import CSV

Okay guys im having a problem... i've created a DTS package to import a CSV file, but all fields containing integers shoot me an error stating that it can't insert a STRING into an INTEGER column...

Does anybody know how to circumvent this? I've googled it to death and still can't come up with a solution. Maybe i'm asking for the wrong thing... who knows... PLEASE HELP!!!Would it be better if i just imported an xls or mdb or someething?|||Hi,

you might want to open the .csv file in Excel and check if the particular columns that are causing the problem are not of text type.

Cheers,
Ziyi|||every entry in this colum is integer... meaning no characters, just numbers...

Dts

Hi All,

I am trying to populate an Oracle table with the data from an Excel spreadsheet using DTS. The Excel spreadsheet has three date fields. When I run DTS one of these three fields is populated correctly and the other two just come over as blank. Any idea?

Any help is appreciated.Whenever importing data, especially from unreliable formats such as Excel, it is wise to load the records into a staging table first, and then use a stored procedure to verify, cleanse, and transfer the data to your production table.
Make the columns in your staging table NVARCHAR2, so you can see what is actually coming into them, and that will help you debug the situation.|||excel? DTS? SQL Server? oracle?

DTS

Hello,
I want to imports text file having Header and detail sections. Both Header
and Detail sections have different number of fields and different field
separators.
How can i imports such a file using DTS
Thanks
read the file twice, filter the header records in one process to a temporary
table, process it again for the details again to a temporary table. process
the temporary tables(s) again to scrub (validate the data) to where ever you
want.
Nik Marshall-Blank MCSD/MCDBA
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CA3AA9AF-5FEE-40D7-84B1-54BE55360E64@.microsoft.com...
> Hello,
> I want to imports text file having Header and detail sections. Both Header
> and Detail sections have different number of fields and different field
> separators.
> How can i imports such a file using DTS
> Thanks

DTS

Hello,
I want to imports text file having Header and detail sections. Both Header
and Detail sections have different number of fields and different field
separators.
How can i imports such a file using DTS
Thanksread the file twice, filter the header records in one process to a temporary
table, process it again for the details again to a temporary table. process
the temporary tables(s) again to scrub (validate the data) to where ever you
want.
--
Nik Marshall-Blank MCSD/MCDBA
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CA3AA9AF-5FEE-40D7-84B1-54BE55360E64@.microsoft.com...
> Hello,
> I want to imports text file having Header and detail sections. Both Header
> and Detail sections have different number of fields and different field
> separators.
> How can i imports such a file using DTS
> Thanks

DTS

Hello,
I want to imports text file having Header and detail sections. Both Header
and Detail sections have different number of fields and different field
separators.
How can i imports such a file using DTS
Thanksread the file twice, filter the header records in one process to a temporary
table, process it again for the details again to a temporary table. process
the temporary tables(s) again to scrub (validate the data) to where ever you
want.
Nik Marshall-Blank MCSD/MCDBA
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CA3AA9AF-5FEE-40D7-84B1-54BE55360E64@.microsoft.com...
> Hello,
> I want to imports text file having Header and detail sections. Both Header
> and Detail sections have different number of fields and different field
> separators.
> How can i imports such a file using DTS
> Thanks