Is it possible to import a CSV and break the contents into multiple
tables?
On Apr 9, 3:59 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Is it possible to import a CSV and break the contents into multiple
> tables?
Use a staging table for that
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks, I'll look into it.
Do you recommend any book that cover DTS well?
|||On Apr 9, 5:05 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Thanks, I'll look into it.
> Do you recommend any book that cover DTS well?
Here is one I like
Professional SQL Server 2000 DTS (Data Transformation Services
by Mark Chaffin , Brian Knight , Todd Robinson
[url]http://www.amazon.com/gp/product/0764543687/102-5735017-0910517?ie=UTF8&tag=sql08-20&linkCode=xm2&camp=1789&creativeASIN=0764543687[ /url]
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks again
Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts
Friday, March 23, 2012
Noob Diving into DTS Packages
Is it possible to import a CSV and break the contents into multiple
tables?On Apr 9, 3:59 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Is it possible to import a CSV and break the contents into multiple
> tables?
Use a staging table for that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I'll look into it.
Do you recommend any book that cover DTS well?|||On Apr 9, 5:05 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Thanks, I'll look into it.
> Do you recommend any book that cover DTS well?
Here is one I like
Professional SQL Server 2000 DTS (Data Transformation Services
by Mark Chaffin , Brian Knight , Todd Robinson
http://www.amazon.com/gp/product/07...ASIN=0764543687
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks again
tables?On Apr 9, 3:59 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Is it possible to import a CSV and break the contents into multiple
> tables?
Use a staging table for that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I'll look into it.
Do you recommend any book that cover DTS well?|||On Apr 9, 5:05 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Thanks, I'll look into it.
> Do you recommend any book that cover DTS well?
Here is one I like
Professional SQL Server 2000 DTS (Data Transformation Services
by Mark Chaffin , Brian Knight , Todd Robinson
http://www.amazon.com/gp/product/07...ASIN=0764543687
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks again
Noob Diving into DTS Packages
Is it possible to import a CSV and break the contents into multiple
tables?On Apr 9, 3:59 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Is it possible to import a CSV and break the contents into multiple
> tables?
Use a staging table for that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I'll look into it.
Do you recommend any book that cover DTS well?|||On Apr 9, 5:05 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Thanks, I'll look into it.
> Do you recommend any book that cover DTS well?
Here is one I like
Professional SQL Server 2000 DTS (Data Transformation Services
by Mark Chaffin , Brian Knight , Todd Robinson
http://www.amazon.com/gp/product/0764543687/102-5735017-0910517?ie=UTF8&tag=sql08-20&linkCode=xm2&camp=1789&creativeASIN=0764543687
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks again
tables?On Apr 9, 3:59 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Is it possible to import a CSV and break the contents into multiple
> tables?
Use a staging table for that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I'll look into it.
Do you recommend any book that cover DTS well?|||On Apr 9, 5:05 pm, "JGiotta" <jdgio...@.gmail.com> wrote:
> Thanks, I'll look into it.
> Do you recommend any book that cover DTS well?
Here is one I like
Professional SQL Server 2000 DTS (Data Transformation Services
by Mark Chaffin , Brian Knight , Todd Robinson
http://www.amazon.com/gp/product/0764543687/102-5735017-0910517?ie=UTF8&tag=sql08-20&linkCode=xm2&camp=1789&creativeASIN=0764543687
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks again
Wednesday, March 21, 2012
NonLogged DTS?
SQL 7.0
Is it possible to have the DTS copy be a nonlogged
operation?
Thanks,
DonHi
At best you can have it as bulk-logged. There are no non-logged operations
in SQL 7.0 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:53c201c4c8ee$c8345120$a401280a@.phx.gbl...
> SQL 7.0
> Is it possible to have the DTS copy be a nonlogged
> operation?
> Thanks,
> Don
>sql
Is it possible to have the DTS copy be a nonlogged
operation?
Thanks,
DonHi
At best you can have it as bulk-logged. There are no non-logged operations
in SQL 7.0 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:53c201c4c8ee$c8345120$a401280a@.phx.gbl...
> SQL 7.0
> Is it possible to have the DTS copy be a nonlogged
> operation?
> Thanks,
> Don
>sql
NonLogged DTS?
SQL 7.0
Is it possible to have the DTS copy be a nonlogged
operation?
Thanks,
DonHi
At best you can have it as bulk-logged. There are no non-logged operations
in SQL 7.0 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:53c201c4c8ee$c8345120$a401280a@.phx.gbl...
> SQL 7.0
> Is it possible to have the DTS copy be a nonlogged
> operation?
> Thanks,
> Don
>
Is it possible to have the DTS copy be a nonlogged
operation?
Thanks,
DonHi
At best you can have it as bulk-logged. There are no non-logged operations
in SQL 7.0 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:53c201c4c8ee$c8345120$a401280a@.phx.gbl...
> SQL 7.0
> Is it possible to have the DTS copy be a nonlogged
> operation?
> Thanks,
> Don
>
NonLogged DTS?
SQL 7.0
Is it possible to have the DTS copy be a nonlogged
operation?
Thanks,
Don
Hi
At best you can have it as bulk-logged. There are no non-logged operations
in SQL 7.0 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:53c201c4c8ee$c8345120$a401280a@.phx.gbl...
> SQL 7.0
> Is it possible to have the DTS copy be a nonlogged
> operation?
> Thanks,
> Don
>
Is it possible to have the DTS copy be a nonlogged
operation?
Thanks,
Don
Hi
At best you can have it as bulk-logged. There are no non-logged operations
in SQL 7.0 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:53c201c4c8ee$c8345120$a401280a@.phx.gbl...
> SQL 7.0
> Is it possible to have the DTS copy be a nonlogged
> operation?
> Thanks,
> Don
>
Monday, February 20, 2012
No Steps have been defined for the transformation Package
I am going nuts trying to get this to work. Maybe someone can help me. I am running sql server 2000 and am using a dts package. The package runs fine on sql server. When I access it using asp.net I get the following error:
------
The execution of the following DTS Package succeeded:
Package Name: MapsImport
Package Description: Import Excel to Maps table
Package ID: {C56FF415-CD35-461E-98E4-BB2430163413}
Package Version: {30415D05-B121-4C4B-991C-43496EA47090}
Package Execution Lineage: {EAF14EB3-F6C1-4D9B-A4B9-46E31AF4B608}
Executed On: NS23
Executed By: ASPNET
Execution Started: 6/7/2006 3:33:12 PM
Execution Completed: 6/7/2006 3:33:17 PM
Total Execution Time: 5.11 seconds
Package Steps execution information:
Step 'Copy Data from Sheet1$' to [dbname].[dbo].[Maps] Step' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Failure creating file.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003436
Step Execution Started: 6/7/2006 3:33:12 PM
Step Execution Completed: 6/7/2006 3:33:17 PM
Total Step Execution Time: 5.015 seconds
Progress count in Step: 0
----
I searched through several forums and found that this seems to be a permissions problem. I set the IIS process to Low, I also added the <identity impersonate="true" /> tag to my web.config file. After adding the tag I get a new error message:
----
No Steps have been defined for the transformation Package.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: No Steps have been defined for the transformation Package.
------
I am at a stand-still trying to get any further. Can anyone teel me what else I might be able to try to resolve this problem?
Here is my code for executing the package:
------
Sub Page_Load(Src As Object, E As EventArgs)
Dim cnnstring as String="Data Source=NS32;Initial Catalog=dbname;Pooling=False;Min Pool Size=100;Max Pool Size=200;User ID=userid;Password=password"
Dim cnn as SqlConnection
Dim cmd as SqlCommand
Dim rs as SqlDataReader
Dim sql as String="Truncate Table Maps"
'Empty Equipment Contract Pricing table
cnn=New SqlConnection(cnnstring)
cnn.Open()
cmd=New SqlCommand(sql, cnn)
sql="DELETE FROM Maps WHERE Name IS NULL"
cmd=New SqlCommand(sql, cnn)
rs=cmd.ExecuteReader()
'check to see if table is empty
If rs.HasRows Then
Response.Write("<p><b>Failed to empty table.</b></p>")
Else
Response.Write("<p><b>Table successfully emptied.</b><br><br>Importing Data...<br>Please wait...</p>")
End If
'declare variables for DTS
Dim objDTSPackage, objDTSStep, strResult, blnSuccess
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
'Use stored procedure on sql server to import data
objDTSPackage = Server.CreateObject("DTS.Package")
blnSuccess = True
'Load package from sql server
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
'Explanation: LoadFromSQLServer ("ServerName", "Username", "Password", "Flags", "PackagePassword", "PackageGUID", "PackageVersionGUID", "Package Name", "PersistsHost")
objDTSPackage.Execute
'walk through steps and check for errors
For Each objDTSStep in objDTSPackage.Steps
If objDTSStep.ExecutionResult = DTSStepExecResult_Failure Then
strResult = strResult & "Package " & objDTSStep.Name & " failed.<br><br>"
blnSuccess = False
Else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br><br>"
End If
Next
'display success or failure message
If blnSuccess Then
Response.Write ("<p><b>Package Succeeded.</b></p>")
Else
Response.Write ("<p><b>Package Failed.</b></p>")
End If
Response.Write ("<p>"& strResult &"</p>")
rs.Close
cnn.Close
'Response.Redirect("list.aspx")
End Sub
---------
The code fails on the line where it says:
objDTSPackage.Execute
Thanks in advance!
-Mikeis the user name and password defined here...
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
..the owner of the dts package?|||Yes, the user and pass are defined properly. I changed them throughout the script for the post. I also added the package guid and the package version guid and I still recieve the same error message.
My scripts are running off a different server than where sql server is located. Could this have something to do with it? I am pretty sure this is some sort of permissions problem, but I dont know what it is. Any ideas?
Thanks.
------
The execution of the following DTS Package succeeded:
Package Name: MapsImport
Package Description: Import Excel to Maps table
Package ID: {C56FF415-CD35-461E-98E4-BB2430163413}
Package Version: {30415D05-B121-4C4B-991C-43496EA47090}
Package Execution Lineage: {EAF14EB3-F6C1-4D9B-A4B9-46E31AF4B608}
Executed On: NS23
Executed By: ASPNET
Execution Started: 6/7/2006 3:33:12 PM
Execution Completed: 6/7/2006 3:33:17 PM
Total Execution Time: 5.11 seconds
Package Steps execution information:
Step 'Copy Data from Sheet1$' to [dbname].[dbo].[Maps] Step' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Failure creating file.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003436
Step Execution Started: 6/7/2006 3:33:12 PM
Step Execution Completed: 6/7/2006 3:33:17 PM
Total Step Execution Time: 5.015 seconds
Progress count in Step: 0
----
I searched through several forums and found that this seems to be a permissions problem. I set the IIS process to Low, I also added the <identity impersonate="true" /> tag to my web.config file. After adding the tag I get a new error message:
----
No Steps have been defined for the transformation Package.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: No Steps have been defined for the transformation Package.
------
I am at a stand-still trying to get any further. Can anyone teel me what else I might be able to try to resolve this problem?
Here is my code for executing the package:
------
Sub Page_Load(Src As Object, E As EventArgs)
Dim cnnstring as String="Data Source=NS32;Initial Catalog=dbname;Pooling=False;Min Pool Size=100;Max Pool Size=200;User ID=userid;Password=password"
Dim cnn as SqlConnection
Dim cmd as SqlCommand
Dim rs as SqlDataReader
Dim sql as String="Truncate Table Maps"
'Empty Equipment Contract Pricing table
cnn=New SqlConnection(cnnstring)
cnn.Open()
cmd=New SqlCommand(sql, cnn)
sql="DELETE FROM Maps WHERE Name IS NULL"
cmd=New SqlCommand(sql, cnn)
rs=cmd.ExecuteReader()
'check to see if table is empty
If rs.HasRows Then
Response.Write("<p><b>Failed to empty table.</b></p>")
Else
Response.Write("<p><b>Table successfully emptied.</b><br><br>Importing Data...<br>Please wait...</p>")
End If
'declare variables for DTS
Dim objDTSPackage, objDTSStep, strResult, blnSuccess
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
'Use stored procedure on sql server to import data
objDTSPackage = Server.CreateObject("DTS.Package")
blnSuccess = True
'Load package from sql server
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
'Explanation: LoadFromSQLServer ("ServerName", "Username", "Password", "Flags", "PackagePassword", "PackageGUID", "PackageVersionGUID", "Package Name", "PersistsHost")
objDTSPackage.Execute
'walk through steps and check for errors
For Each objDTSStep in objDTSPackage.Steps
If objDTSStep.ExecutionResult = DTSStepExecResult_Failure Then
strResult = strResult & "Package " & objDTSStep.Name & " failed.<br><br>"
blnSuccess = False
Else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br><br>"
End If
Next
'display success or failure message
If blnSuccess Then
Response.Write ("<p><b>Package Succeeded.</b></p>")
Else
Response.Write ("<p><b>Package Failed.</b></p>")
End If
Response.Write ("<p>"& strResult &"</p>")
rs.Close
cnn.Close
'Response.Redirect("list.aspx")
End Sub
---------
The code fails on the line where it says:
objDTSPackage.Execute
Thanks in advance!
-Mikeis the user name and password defined here...
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
..the owner of the dts package?|||Yes, the user and pass are defined properly. I changed them throughout the script for the post. I also added the package guid and the package version guid and I still recieve the same error message.
My scripts are running off a different server than where sql server is located. Could this have something to do with it? I am pretty sure this is some sort of permissions problem, but I dont know what it is. Any ideas?
Thanks.
Subscribe to:
Posts (Atom)