My project uses WIX to do this. Please don't ask me to give you a sample, that's more detail than I'm prepared to put into a forum post
-Jamie
|||You can also use VS Setup project. Add it to the same solution with SSIS project, add Outputs of SSIS project to Setup project. It will add all DTSX files and configurations.sqlHow can you tell what DTS package a Job step is running when it looks like this?
DTSRun /~Z0xBEA63421A37A7AA7175D23C17BA2CB8CEC94DCC69159320C62777706E248EFDD4BF43C93709C9FCEF2982201C4D26B7EAFF25816E9F0E7C51467E5C2446BE35FBA02E809F523C5D19F6AC91416E5ED3B79BE27B7991980910679EBF4C311A9A33AED799A9C53BB7BFDAEDE7EEBC6ECE9001D0E6218956A578C40FC
The GUID does not appear to be related to what DTS shows in the Package or Version GUIDS of the DTS packages. And yet the job runs from what I am told.
SQL Server 2000 msdb.dbo.sysjobsteps
The value "DTSRun /~Z0xBEA63421...." is in the command column. The table also has a step_name column which appears in the job. The default step_name is the name of the DTS package but it could have been renamed.
If so turn on profiler and find the package execution line with the package name
exec msdb..sp_get_dtspackage N'MyPackage'
|||If you can enable the dTS package log then you can see the status.|||The /~Z paramater is an encrypted command line version. Microsoft does this in case it needs to pass a password.
The job step and job name is by default the name of the package. If these have been changed that will make it harder.
If you have the command line and run it from a command prompt it will display in the command window which DTS package is running. I would recommend using this information to change the command line to use the /S /E /N commands so that it's easier to see which package is running.
OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
Right click on the package and select "Export..."
Chose File System as the destination and once done filling in the fields, you'll be able to open that package up in Business Intelligence Developer Studio.|||
Thanks, I tell ya, I should have walked away from this project when it was brought to me.
Coming from coding .NET apps for the pass 6 years, this SQL style work is a whole new world to me.
ok, thanks
I define a package variables "varOutTable" and "varFromTable".
and I insert a SQL Execute SQL Task into Control Flow
my sql command is
"Select * into @.[User::varOutTable] from @.[User::varFromTable]"
but the task failed,
it seems that sql task can't get the varOutTable and varFromTable
How can I use the Variables in SQL Execute SQL task?
thanks!!
Use an expression to build your SQL statement. This is:
Open SQL Task editor|||
thank for your reply.
you are right,
it runs great now.
|||Will this work for a select count(*)?|||agentf1 wrote:
Will this work for a select count(*)?
Why not? A select count(*) would work just like a select * does.... So yes, it would work.|||Just make sure you add an alias to the column (eg. Count(*) as MyCount) and the result set uses the same name (MyCount)....|||I am doing this, my SQL looks like Select count(*) as recordcount from table_name. My variable is set up in ResultSet on the task and is an object.|||Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.|||
Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.
That's because your variable is of OBJECT type. If you're returning one row, one value, you can use an integer data type (in this case).|||I am, thanks. I will try this.|||When changed to int32 I get the following error message.
Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select count(*) as recordcount from loan_auj" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.|||
That should work. Make sure ResultSet property is set to single row; the result set page you have a resultsetname RecordCount that points to a SSIS variable and that variable is Int32.
If all this is fine; then post all the details of you Execute SQL task to see other potential causes of error
|||That was it, I needed to change result set to single row. Thanks.
I define a package variables "varOutTable" and "varFromTable".
and I insert a SQL Execute SQL Task into Control Flow
my sql command is
"Select * into @.[User::varOutTable] from @.[User::varFromTable]"
but the task failed,
it seems that sql task can't get the varOutTable and varFromTable
How can I use the Variables in SQL Execute SQL task?
thanks!!
Use an expression to build your SQL statement. This is:
Open SQL Task editor|||
thank for your reply.
you are right,
it runs great now.
|||Will this work for a select count(*)?|||Will this work for a select count(*)?
Why not? A select count(*) would work just like a select * does.... So yes, it would work.|||Just make sure you add an alias to the column (eg. Count(*) as MyCount) and the result set uses the same name (MyCount)....|||I am doing this, my SQL looks like Select count(*) as recordcount from table_name. My variable is set up in ResultSet on the task and is an object.|||Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.|||
Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.
That's because your variable is of OBJECT type. If you're returning one row, one value, you can use an integer data type (in this case).|||I am, thanks. I will try this.|||When changed to int32 I get the following error message.
Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select count(*) as recordcount from loan_auj" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.|||
That should work. Make sure ResultSet property is set to single row; the result set page you have a resultsetname RecordCount that points to a SSIS variable and that variable is Int32.
If all this is fine; then post all the details of you Execute SQL task to see other potential causes of error
|||That was it, I needed to change result set to single row. Thanks.
Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement?
Also maybe this is a very bad way to do this- any suggestions?
You can use dtsrun commandline utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp
BTW, SQL provides a extended stored procedure xp_cmdshell to perform commandline tasks, so you can also call dtsrun utility in T-SQL:
exec('master..xp_cmdshell ''dtsrun -S iori\Athena -E -N test ''')
|||I saw that command line solution a while ago, but I was told that it isnt the best thing to do security wise.I ended up finding DTS.Packages (DTS.Package dtsp = new DTS.Package();) which seems to work pretty well. The only problem is that the web account does not have the correct permissions. By enabling a trusted connection, and tweaking IIS, it now prompts the user for a username/pswd when they get to the site. If they enter in an account with the correct permissions, then they are able to run the DTS job.
Now I'm trying to get rid of this annoyance. Is it possible to somehow force a login to a specific trusted account? If not then I was thinking of trying to run a job that will just run the DTS package, but I don't know how to do that either. Any suggestions?
