How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to retrive a value using a query string?

Hello,

I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.

This is what I have:

<script runat="server"> Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @.Password) AND (users.username = @.Username)" conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance") cmd = New SqlCommand(cmdString, conn) cmd.Parameters.Add("@.Username", SqlDbType.VarChar, 50) cmd.Parameters("@.Username").Value = Me.Login1.UserName cmd.Parameters.Add("@.Password", SqlDbType.VarChar, 50) cmd.Parameters("@.Password").Value = Me.Login1.Password conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) If myReader.Read() Then FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False) Else 'Response.Write("Invalid credentials") End If myReader.Close() End Sub</script>

I would like to know how can I get now the "user.FirstName" and pass it to a session variable?
how should I code it?

thanks,

Inside your read have

Session["FirstName"]=myReader.getString(2)

Is there any reason that you're getting 7(I think) fields from the database and throwing them away?

Also, it's poor form to store unencrypted passwords in the database. At least use xor or something.

How to retrieve Global Variables in an ActiveX Script Task using VBScript in SSIS

I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:

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

Function Main()

Response.Expires=-1

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False

objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing

End Function
=========================================

I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).

My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.

Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...

Michael

No answer on all your questions but stick with using the XML source as this is what SSIS is designed for.

While the XML source does not cater for a variable for the XSD path, it does allow you to set it via expressions. Essentially the same thing.

When the data flow is highlighted, see properties > Expressions. There is a [XMLSource.DataSchemaDefinition] property which you can set if a variable or a value built up from expressions.

Also, stay away from the Active X Script task...