Having problems with variables

I have a Execute Process Task that pretty much executes a batch file that downloads a file that has a dynamic file name (with datetime stamp). Now I would like to load this file to a Flat File Source task in the Data Flow Task section automatically. So, creating a file manager or something on the fly. Is something like this possible with SSIS? Or am I simply hitting the wall here?

Thank you.

Why can you not just drop a data-flow into your package and make sure it executes after the Execute process task?

-Jamie

Having a MAJOR brain fart here...

Guys I'm sorry to be asking such a routine question...

I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.

1public int Add()23{45string SQL;67SQL ="INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +89"LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +1011"FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +1213"GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +1415"AirExchange, InternetAccessID, InteriorDescription) " +1617"VALUES ( @.PropertyID, @.CeilingHeight, " +1819"@.LoadingDocks, @.PassengerElevators, @.FreightElevators, @.PassengerEscalators, " +2021"@.FireSprinklersID, @.SecurityCameras, @.SmokeDetection, @.FireAlarms, " +2223"@.GasDetection, @.SecureAccess, @.HeatTypeID, @.AirConditioningID, " +2425"@.AirExchange, @.InternetAccessID, @.InteriorDescription)";2627PropertyDB myConnection =new PropertyDB();2829SqlConnection conn = myConnection.GetOpenConnection();3031SqlCommand cmd =new SqlCommand(SQL, conn);3233cmd.Parameters.Add("@.PropertyID", SqlDbType.Int).Value = PropertyID;3435cmd.Parameters.Add("@.CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;3637cmd.Parameters.Add("@.LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;3839cmd.Parameters.Add("@.PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;4041cmd.Parameters.Add("@.FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;4243cmd.Parameters.Add("@.PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;4445cmd.Parameters.Add("@.FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;4647cmd.Parameters.Add("@.SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;4849cmd.Parameters.Add("@.SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;5051cmd.Parameters.Add("@.SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;5253cmd.Parameters.Add("@.FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;5455cmd.Parameters.Add("@.GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;5657cmd.Parameters.Add("@.SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;5859cmd.Parameters.Add("@.HeatTypeID", SqlDbType.Int).Value = HeatTypeID;6061cmd.Parameters.Add("@.AirConditioningID", SqlDbType.Int).Value = AirConditioningID;6263cmd.Parameters.Add("@.AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;6465cmd.Parameters.Add("@.InternetAccessID", SqlDbType.Int).Value = InternetAccessID;6667cmd.Parameters.Add("@.InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;6869cmd.ExecuteNonQuery();7071cmd.CommandText ="SELECT @.@.IDENTITY";7273this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());7475conn.Close();7677return this.BuildingInteriorID;7879}80

Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?

Any comments are greatly welcomed.

Hi eterry,

As far as I can see there is no better way to assign value by iterating through each column like this.

But I think there is one thing that you can improve in your code. You can put SELECT SCOPE_IDENTITY() (use SCOPE_IDENTITY() instead of @.@.IDENTITY in SQL Server)at the end of the INSERT statement. Seperate them with a ";", like

INSERT INTO ......; SELECT SCOPE_IDENTITY()

Then you can use ExecuteScalar to have the 2 statement executed at once. This will have 2 advantages.

1. Save a roundtrip to the server and gain better performance.
2. Prevent the concurrency issues. In your code, if 2 users do this together, there is possibility that they will get wrong identity if one's execution is interrupted by the other.

HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||

Thanks for the tip Kevin.

I was hoping that there would be a better way of doing, but it is what it is.